Log your visitors to Google spreadsheet

By:
On:
With: 33 Comments

Update 3: There an simpler version of this script here: Easier Google Spreadsheet Visitor Logger

Update 2: Reader Zuza Ritt has found a solution to working with the new format. Read the full instructions here.

Update: Google has updated their form system so that this method doesn’t work anymore. However, you can get around it — for now — by setting up an old-style form. Just go to this link to create your form - drive.google.com/spreadsheet/newform – then follow the same instructions as below.

—

I have a problem. I want to be able to see who visits my region in a nice spreadsheet. Specifically, a Google spreadsheet.

There are reasons for this. For example, I want to be able to track who sees a particular billboard, and track that data with the advertiser. Or record who picks up a copy of a hypergate. Or simply know who stops by.

I don’t want to save IP addresses, or do anything creepy. Just the avatar name is enough.

So I ask around. How do I get data out of OpenSim without going in and checking notecards inside objects?

For example, in this free Visitor List Maker script, you have to walk up to the counter and tell it to show you the list.

The experts tell me I have to create a script that writes to an external database — and that I need a database and a server to run it on.

So much for that! But a couple of days later, I realized — I do have a database and a server, a Filemaker database and a hosted server for my company’s workflow. But the idea of trying to figure out the Filemaker API and send in database update requests — well, that would probably take the rest of my lifetime.

And then, a couple of days after that, I thought — why not use a Google form?

So I do some Googling and find out that you can send in form responses through a URL, pre-filling in all the fields.

And, a few hours of tinkering later, I had a script — a weird hybrid of the Visitor List Maker script and my old stand-by Hyperica Lookup HG Teleport Script (thanks, Paul Emery!). And it worked!

For anyone out there looking to get data out of an OpenSim region — or a Second Life region for that matter — feel free to use, adapt, and redistribute this script. Only restrictions — since it’s based on a Linden script, you can’t sell it, and you must leave the attributions in place.

The script itself is attached as a text file at the bottom of this post. If you can read LSL, and know your way around Google forms, just get it, change the first line, and you’re good.

If you need a little bit more information, here we go.

The Google form part

To create your Google form, go to Google Docs and click Create (on left side) and then choose Form from the drop down-menu.

To use the script attached as is, add two questions.

The first question is “Avatar.” The answer is a text field.

The second question is “Region.” The answer is also a text field.

You can name the form anything you want. In fact, you can name your questions anything you like. It doesn’t matter for this script. Save the form.

Now click on See Responses and pick Spreadsheet from the drop-down menu. This is where the incoming data will show up. Bookmark it.

But you’re not done yet.

In your spreadsheet view, in the menu bar on top, click on Form and then pick Go to live form from the drop down window.

Your form’s URL should look something like the one here:

https://docs.google.com/a/tromblyinternational.com/spreadsheet/viewform?hl=en_US&formkey=dEE5akZOWUM4WmdOSC0wZzFJUHFyNlE6MQ#gid=0

Now, there’s a reason I pasted the whole long URL above. It’s so that you can see that there’s a long string of characters after &formkey= and before #gid=0. Copy those characters. In my case, they were dEE5akZOWUM4WmdOSC0wZzFJUHFyNlE6MQ.

Now paste those characters in the first line of the attached script and drop the script on any object.

That’s it.

More info about the script

You don’t need to track the date and time the avatar stops by because, if you need it, Google automatically puts a date and time stamp on each submission. Handy.

The way the script works is that it remembers the names of the last ten avatars that walk by. If it sees an avatar that’s not in that list, it adds them to the spreadsheet. That way, if you just happen to be standing around the visitor counter, you don’t get added to the spreadsheet a million times. (Yes, that happened to me the first time I ran the script, because I thought I could leave that part out. Hah!)

You can change this by editing the second line of the script. It’s the list_size variable, and is set to 10 in my script. But if you have large groups of avatars stopping by, you might want to increase it. Otherwise, if, say, avatar number 11 isn’t on the list, his name gets added to the spreadsheet — and the first entry on the list is knocked off. But then that first avatar isn’t on the list anymore! So now the counter adds her, too — and knocks off guy number two. But now guy number two isn’t on the list! So the counter goes crazy cycling through all the avatars milling around. But don’t make the maximum size of the list too long because it will slow down the script.

The other thing you can change is that you can comment out the line that checks whether the avatar is you, the owner of the script. Maybe you want to find out whether your avatar is having a good time without you, when you’re logged out. Or maybe you just want to test the script. Comment out this line: if( llDetectedKey( i ) != llGetOwner() ) by putting two slashes in front of it — //.

So that’s about it for basic tinkering.

Moving on.

Say you want to adapt the script to track other types of stuff.

The secret sauce is in this line:

string URL = "http://spreadsheets.google.com/formResponse?hl=en_US&formkey=" + google_formkey
+ "&entry.0.single=" + detected_name + "&entry.1.single=" + region_name + "&submit=Submit";

The first part is the URL Google uses to send in form responses. The last part — “&submit=Submit” — tells it that you’re submitting the form. Don’t mess with these parts.

In between, you’ve got the google_formkey — you copied and pasted this from your Google Form URL into the first line of the script, and this is the variable holding it.

And right after that you’ve got the data you’re saving.

The first data saved is preceded by “&entry.0.single=” — then you put in the variable holding your data. In my case, I put in the detected avatar name. Yes, Google starts counting at zero, not one.

The second data saved is preceded by “&entry.1.single=”, and I’ve got it followed by the region name, thinking that I can drop this script into counters all over my grid and it will track who went where. If I wanted to, I could add additional form fields —  “&entry.2.single=”, “&entry.3.single=”, and so on, and track the exact positions of the avatars, or their heights, or anything else I wanted. Just add more fields to your original form. Remember to put an ampersand — & — in front of each new field, and make sure your quotation marks are in the right places, and the variable name is set off by plus signs.

This is why the text of the questions in the forms doesn’t matter — all Google cares about here is the order of the fields.

And here is what the final result looks like:

Google form spreadsheet visitor counter

Hey, Breen stopped by. Hi, Breen!

Avatars local to my grid just show their avatar names — avatars from other grids show their full hypergrid name.

Now what?

Now that you’ve got your data in spreadsheet form, you can share the spreadsheet with your colleagues. You can embed the spreadsheet on your website. You can share it with your customers.

Is it totally secure? Probably not. If someone knows your Google Form ID, they can flood it with useless data. That means, for example, that anyone who can see your script can mess with your head. You can reduce this risk by running the script on regions or grids that you control, and set the permissions of the objects so that folks can’t peek in.

But it’s a great option for folks who don’t want to set up a separate database and just want to track stuff. This is especially useful for anyone tracking the behavior of in-world simulations and is collecting a lot of data. Google spreadsheets are perfect — you can collect the data, and then analyze it, all in one place.

You can also use it for quick in-world surveys — put up a sign with your survey questions and nice big boxes showing the possible responses on the side of a prim. Check to see which box folks press, and save that information to your Google spreadsheet along with the avatar name. Since Google can instantly generate a graphic showing the responses, you can pull in that graphic’s URL onto a nearby prim to show voting results in real-time. You can put these signs up on multiple regions — even multiple grids — and people can see the combined results from all these sources. Or you can use it to put up a monitor screen on one region, to show you what’s going on in a different region — or many different regions.

You can also track interactions with objects. For example, you can have objects that ask questions when avatars walk by, and then save their responses.

And you don’t have to update just one spreadsheet. Say you’ve got a sign showing four different destinations, and you want to know who clicks on each destination. You can set up a separate spreadsheet for each one, and then send the avatar names to just the appropriate sheet. And then you can share different spreadsheets with different people, or embed them on different Web pages.

And there’s other cool stuff you can do, as well. For example, say you don’t want to embed the whole spreadsheet on your Website or blog. You might not want everyone to see all the data. You might want some people to see some data, and other people to see other data, or you might want to process and massage the data first.  Here’s a sample bit of code showing how to pull data out of a Google spreadsheet into a Webpage. I was thinking of playing around with that, but then I took a look at the sample code, and maybe I’ll wait a while.

Get the script

Click here to download the text file with the script: Google spreadsheet visitor logger

I’ve been having problems uploading scripts recently, so please let me know if there are any problems with it. Either with the formatting of the file, or with the script itself. For some reason, between the time I get the script working and the moment at which I cut-and-paste it into a text file, variable names mysteriously change, closing brackets disappear, and semi-colons turn into colons.

Maria Korolov
Latest posts by Maria Korolov (see all)