Create a billboard network

Say you and your friends want to set up a self-serve system to post announcements on each other’s grids or sims?

Here’s how to do it without having to create any databases, by using Google Spreadsheets.

First, you go to Google Docs and choose Create – Form.

Here’s a sample form, which you can use to submit event announcements for Hyperica billboards: http://www.hyperica.com/add-new-event/

I created the form in Google Docs, then embedded it into my website by asking Google for the embed code, and just pasting it into a blog post, the same way you would embed a YouTube video.

My form asks for things like the event name and date, an image URL, the hypergrid address, and a website address.

The data that’s collected goes into a Google spreadsheet. But it’s ordered according to when people added the events. So I created a new sheet — there’s a plus button at the bottom left of the Google spreadsheet page where you can add new sheets that are part of the same spreadsheet.

This new sheet filtered all the events, to show just the ones that I haven’t happened yet.

Here is my formula:

=filter(Events!B:I,Events!C:C>=TODAY())

Where Events is the name of the first sheet, and column C is where the date is. Now you have two sheets — one with all the events, and one with all the events that haven’t occurred yet. I called the second sheet Events2.

Then I created a third sheet, where I would sort all the events chronologically — so that the event that will happen next is listed first.

Here is my formula:

=sort(Events2!A:H,Events2!B:B,TRUE)

You’re going to get an extra row at the end with the row headings. I don’t know how to get rid of that! Also, there might be a way to combine these two formulas into one, but just putting one inside the other didn’t work for me. But maybe I was doing it wrong.

Anyway, now, on the third sheet, you have just what you want — upcoming events, sorted in chronological order. But you could sort them in any other way you want, too. For example, if people pay you for these announcements, you could sort them by how much they’ve paid. Or you can filter based on what grid the events are on, or whether the event is free or costs money.

Then I went in-world and created an object on which to display an event.

My event announcement board.

It’s actually a flattened cube — I have the size set to X=1.8, Y=2.2, and Z=0.01. Then I have Taper set to X=0.40. That gives me a little strip at the top, a little strip at the bottom, and a wide area in the middle for the event’s image. You can adjust the tapering settings if you don’t want either of the strips — or you can make your billboard be just the image itself — and just have it do a teleport if you click on it.

Here is the script: Hyperica Event Script

(I had to change the file type to a .TXT file to upload it.)

The script pulls the event name, location and time from the Google Spreadsheet and writes it at the top of the panel. Then it pulls the event image down from the web and displays it in the middle of the panel. There’s a built-in timer, so the board will automatically download a new image once an hour.

It pulls the bottom buttons from a texture that needs to be placed inside the object and named “Grid-Website buttons.” If you rename the texture, just edit the script to use the new name.

Then it waits for you to click. If you click on the Teleport button, it will pull up the map with the destination on it, so you can confirm the teleport. And if you click on the Website button, it will bring up the Website. I created the texture for the button panel — feel free to use it, or create your own:

The way my script works is that you edit the first row to tell the script which event to display — the first event, the second event, etc… The reason I did it this way was to set up a big announcement board showing the next six events:

But you could also use it to show a random announcement, instead, as in this ad below:

Here, if you’re the owner, and you click on the middle image, it pulls in a new random ad from the spreadsheet — where the submissions aren’t ordered or filtered at all. Clicking on the top red panel takes you to the website where you can submit your own, free, Hyperica ad. (Feel free to do that.)

Here, I added a second texture to the object, “Click here for your free ad” to put in the top panel. Then I adjusted the image orientation and stretching manually so it would fit right.

Here is the script itself: Hyperica Ad Script

It pulls in the data from the spreadsheet, counts up the number of rows, and picks a random row to use. If you wanted to, you could add columns to give a particular weight to particular ads, and adjust the randomness calculation to bring up the weighted ads more often.

You could also take out the top and bottom panels, and just have a teleport function if someone clicks on the ad.

And it’s not just for ad billboards — you could also use this to display announcements simultaneously in multiple locations around your grid — use a timer to refresh the display on a set interval to keep the displays up-to-date.

Remember to make your Google Form public access if you want outsiders to add their own listings, or keep it private if you’re just going to manage it yourself. You will also need to make the spreadsheet public — the particular sheet you’re using for your script — and get its URL and update the script accordingly.

You can get more detailed information about how to pull data from a Google Spreadsheet in my previous article.

maria@hypergridbusiness.com'

Maria Korolov

Maria Korolov is editor and publisher of Hypergrid Business. She has been a journalist for more than twenty years and has worked for the Chicago Tribune, Reuters, and Computerworld and has reported from over a dozen countries, including Russia and China.

  • Visitor

    The Hyperia Event Scipt return this error: http://pastebin.com/Jek8BAK7

    • Visitor —

      Sorry, it’s still working on my regions, and I have a hard time figuring out what error you’re getting, exactly. It doesn’t help that the error is in French…

      It seems to be saying you’ve got an invalid URI?

      Are you using my exact script, or did you modify it to work with your own spreadsheet?

      Is your Google spreadsheet up and working, and set up for public access, with all the fields aligned correctly?

      • Visitor

        Hello, I finally got to no longer have the error and display the url snashot correctly.

        However I can not see other information. Certainly the order of my colones is not correct.

        Can you indicate the correct order of colones so I mayest correct me.

        Thank you in advance.

        • I have:

          SimLocation = llList2String(DataList,row*ColumnCount+6);

          You will have to experiment with the number at the end there. For example, if you have deleted any questions in the associated Google Form, then you’ll still have those columns there in the spreadsheet.

          I needed a bit of trial and error on mine to get the columns counted right.

          • Visitor

            It seems that the order of your form of demonstration does not correspond to the order of the script …

            If I take as an example the field “Event Image URL”, he is in 6th position in glass form. But apparently to work with the script must be in 3rd position in the spreadsheet. This brings really confusing …

            Can you provide the correct order to use your script?

          • I’m not sure what you are asking, since the script works as is on my grid.

            Are you using my spreadsheet, or your own spreadsheet?

          • Visitor

            If I use your link in your script is “Hyperica Envent Script” it does not work

            My debug shows this:
            ID: adf89901-76e3-431E-b8de-1dff36eb3e7c
            STATUS: 200
            META:
            BODY: # N / A, -, -, -, -, -, -, –

            This really seems normal because if I see your picture I see it is empty …

            If I use my own connection with myarray this works 50% because I certainly did not put much colone in the correct order.

            So if you can indicate the correct order for colone firt useful and it would be greatly appreciated.

            Thank you in advance.

          • Visitor

            If I look at your form, I see 10 fields, which corresponds to 11 colones in the table (10 +1 for timestamp).

            If I look in your event script I see ColumnCount integer = 8 (line 16)

            There is therefore a mistake I think …

            If I look at line 43 and line 47, I see that you are requesting colone 6 twice for 2 different things.

            So there is an error there too I think …

          • Sorry, I was looking at the ad script, not the event script… the Event script also works in-world. I’m going to go through it and see where the issue is later on today.

          • It looks like…

            Event Name is +0,
            Event Date is +1,
            Event Time is +2,
            SimAddress is +3, (This is the hypergrid address.)
            Location should be +4 (This is upper, lower, or middle.)
            Snapshot URL is +5,
            Website is +6,
            Grid Name is +7,

            Looks like I hadn’t changed SimLocation in the script because I wasn’t using that data… so very sloppy on my part, should either remove the variable or fix it.

            The other problem could be that I wasn’t updating the spreadsheet. I’ve added a bunch of upcoming events, and it should be working correctly now.

          • Visitor

            Hehe I also have a little confused at the beginning …

            They all work correctly, it was just a little fix and possibly adapt to our own forms.

            It is now it will work for everyone, even for novices.

            I’ll put different examples on my website soon and you will definitely informed of this by twitter

            Thank you!

      • Visitor

        I am amazed in your script:

        Website llList2String = (DataList row ColumnCount * + 6);
        and
        SimLocation llList2String = (DataList row ColumnCount * + 6);

        both of 6 …