Pull in data from Google spreadsheet

By:
On:
With: 12 Comments

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