Pull in data from Google spreadsheet

A few weeks ago, I posted a script about how to add data to a Google spreadsheet from inside an OpenSim world (or from a Second Life region).

But what if you want to go the other way, and bring data from a spreadsheet in-world?

It’s not too hard.

First, pull up your Google spreadsheet, and go to File>Publish to Web.

Then choose which sheet you want to publish, set to republish automatically if the sheet changes, and hit “Publish now.”

Select the CSV format.

Now look at the second half of that window. Select the format as CSV — comma separated values — and choose which sheet you want to bring in. You can also just bring in a portion of a sheet by replacing “All cells” with a range, such as A1:C3.

Google will generate a URL for you — copy that URL.

Now paste the URL into the first line of the attached script, replacing the URL I have in there now. You don’t have to do much else — the script will pull in the data and say it in-world. Of course, that’s not very useful.

The inner workings

First, the script downloads all the data from the Web into a single string — the following two statements are key here.

httpkey=llHTTPRequest(URL, [] ,””);

http_response(key id, integer status, list meta, string body

You don’t want to mess with these parts.

Then, the string gets chopped into pieces, with each piece going into a separate list item. I have it being broken up by commas and line breaks, which works perfectly with my spreadsheet — but I don’t know how it will work if you have commas inside your cells themselves.

DataList = llParseString2List(body, [“,”,”\n”], [] );

The total number of cells is just the total number of entries in the list.

CellCount = llGetListLength(DataList);

I also count up the total number of rows in the spreadsheet by counting how many line breaks there were, and adding 1.

RowCount = llGetListLength(llParseStringKeepNulls(body, [“\n”], []))-1;

It works by converting everything into yet another list, one just broken up by line breaks. If you know how many columns your spreadsheet has — and that it’s not going to change — then you can speed up the script by skipping this step. So, say, if you have eight columns in your spreadsheet, which is what I have, I would calculate the total number of rows like this:

RowCount = CellCount /8;

In the end, you’ll have a list with as many line items as there are cells in the spreadsheet, or what they call in LSL, a strided list.

For example, if you needed to, you could sort by the first item in every row, or resort in random order.

To pull out an individual element from the list, use the following command, which returns a string, or text data:

 llList2String (DataList, row*ColumnCount + column)

Counting of the rows starts at zero. So if you wanted to get the third item in the second row of your spreadsheet, you would actually use 2 for column, and 1 for row.

The script is attached as a text file, since I’ve been having trouble embedding scripts in this blog.

Script to pull in data from Google spreadsheet

One application of this is to show the last ten visitors to a region. Sure, you can just pull in the Google spreadsheet itself as media-on-a-prim, but what’s the fun of that?

The tower control room at Hyperica Upper. (Click to see larger image)

The script below pulls in the data from my Google spreadsheet visitor counter script and shows the last 25 visitors on the surface of an in-world prim. This script only works in OpenSim, since it uses the OpenSim text commands to display the data on a surface.

In order to pull in the data, the spreadsheet has to be public, so don’t use this for sensitive corporate data. Google does have a secure API, which requires authentication, but I couldn’t figure it out.  If you can, then you probably don’t need this Google spreadsheet hack, anyway — you can write and read directly from a MySQL database.

The script — which has a touch-to-reset feature — is attached as a text file here:

Show last 25 visitors

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.

  • http://excelsior-station.wikidot.com Sarge Misfit

    I wonder how much can be done with this. I’m thinking RPG where such a spreadsheet could be used for a grid-wide system, keeping a record of experience levels, allegiances, whatever. Point-of-sales recording, too, so it would be easier to send updated items to the individuals that bought those items.

    • http://www.hypergridbusiness.com/ Maria Korolov

      It’s not a secure system, so I wouldn’t advise using it for anything retail-related. There, you’d probably want to shell out some money for a programmer who knows how to work with MySQL.

      In my case, I’ll be using it to track visitors — no big secret there. The visitors certainly know who they are! 

      And to track display ads. Again, if you want to see the ads, just go in-world. They’re right there. 

      The way these spreadsheets work, is that anyone with the URL will be able to get this data. They won’t be able to change it — but they will be able to see it.

      Of course, if you’re displaying the data publicly — say, in leader boards — then this is a non-issue. 

      And, as you said, it works grid-wide. Hypergrid-wide, even. :-)

      • http://excelsior-station.wikidot.com Sarge Misfit

         Cool. That actually works very well for an RP system. People can see what levels and class and such others are at but nobody can change it. Which also secures it against people who want that “extra edge” of upping their abilities by editing the HUD notecards. That is, if the “owning” script can modify it, rather than just append. I have to admit to not checking the script yet. Working on the aforementioned RP system :-D

        • http://www.hypergridbusiness.com/ Maria Korolov

          You can also just use media-on-a-prim to show a read-only version of the Google spreadsheet in-world, if you just want to display the data. The text-on-a-prim script is a little slow! 

          But it’s perfect for pulling in data from a website for internal script use. Keep in mind that if you expose the script itself, folks can do things like point it to a different spreadsheet — where they can put in their own numbers — or replace some of the lookup commands with straight assignments. So if security is an issue, I’d recommend using it on closed grids where users can’t access the server and change the perms and modify the scripts as they see fit.

          You can also put the lookup logic in a central control script that downloads the necessary data and passes it along to the various other objects and attachments that need it.

          • http://excelsior-station.wikidot.com Sarge Misfit

             I’m actually not too worried about security. This isn’t SL, after all. On the other hand, I’m not going to leave the barn door open either. :-)

            In any event, I’ve bookmarked the script for future work.

  • http://twitter.com/iliveisl Ener Hax

    that’s a very cool script! we want to list “current events” for each field trip activity in Enclave Harbour and this is a great solution, we may just pull it in as a webpage so that it can have clickable links – i did not know that a Google Doc could update a webpage like you mentioned!

    thank you Maria! you continue to be an innovator that inspires us! (if it wasn’t for Maria, i doubt i would be in OpenSim!) =)

  • David John

    Nice article. Well can some one help me with accessing transient data on Google Docs? Well its a serious concern with Google Docs, I am looking for some alternatives and also waiting for some new players like CollateBox http://www.collatebox.com/ and IBM docs. Hoping that these tools can solve my problem.

    • http://www.hypergridbusiness.com/ Maria Korolov

      What is “transient data”?

  • aria

    Maria, can I update the data after I have published? I want to use it for classes that I will be delivering

  • aria

    ok I saw your info about date changes thanks

  • visitor

    Hi Maria, Google spreadsheet has changed now and the “Export to CSV” is deleted. All the old documents will be converted to their new format which is very very annoying.
    I am currently looking for a solution to this problem but I have not found it yet. If you already know a solutoin you are welcome.