Create a billboard network

By:
On:
With: 12 Comments

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 Korolov
Latest posts by Maria Korolov (see all)