Log your visitors to Google spreadsheet

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@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.

  • holy crap! makes my head spin just reading this! but it is very clever and i can see that it would be rather useful! thanks for stretching my yardstick! =)

    • This really is nice maria. The idea that you can send info from in world out to the rest of the internet is important. This is good example.Hope people get the larger idea.

  • Tim Taylor

    Looks interesting

  • Doxie Sixpence

    We have a much simpler visitor logger working in OSGrid. Wellll when i say simpler it wasn’t me who made it so it might be quite complicated. I can see who has visited each part of the region – again just the avatar name as i only want to know who has visited when i’m not there. It all comes up on a secure webpage so i can look at it any time. Quite a few come through the hypergate and then nowhere else 🙂  And in the shops the vendors for items tell me when someone “buys” one of the freebies and keeps count of the total sold. Brown hair packs are the top item so far.

    • Who owns the webpage where it comes up? Is it on your own website, or on the site of whoever created the script?

      The advantage of this approach is that no outsiders are in charge of your list — so you don’t have to worry about them no longer supporting the service or peeking at your data.

      Plus, with the data already in spreadsheet form, it’s easy to export it to Excel, or upload it to any other database you’re using, or sort it, or whatever.

      • Doxie Sixpence

        The webserver is on the same server that runs the opensim region – after all it uses php to access the opensim database. So here we have no outsiders involved. The scripts in the region are all ours too. And we can export directly to excel from the page though i have never needed to do that. Of course it does help to have someone who knows all about databases and webpages and opensim to do this.

        • Does is update the actual OpenSim database, or does it create a new MySQL database for the script? If it updates the OpenSim database, does it make it difficult to upgrade to new versions of OpenSim?

          Or does it just update a stored HTML page?

          Not that it matters… I wouldn’t be able to do either of those things! So Google spreadsheets for me. 🙂

          • Doxie Sixpence

            I can’t do it either – I just do the in-world bits. But I think it uses a different database and uses the opensim one for information only. I will try to listen more next time it is explained.

          • The thing I like about this script is that it’s easy enough for me to fiddle with it, if I wanted to collect any other kind of data, as long as I can get to it, I can save it.

            The one thing I would LOVE to do, but can’t yet, is figure out how to get the name of the grid the script is on!

            Say someone takes one of my hypergates and adds it to their grid, and wants it to be automatically added to our directory — there’s no command in LSL to get the grid name! There’s a command to get the name of the server running the region, but that doesn’t get you very far. The Lindens do run more than one grid, so you’d think they’d have created a command to do this…

          • Blake Bourque

            In OSSL there is a function osGetGridName. I think that is what your are looking for: 
            http://opensimulator.org/wiki/OsGetGridName

  • Looks cool, after a couple of stumbles of having the 2 default columns left in place I have Avatar and Region columns, but the region column doesnt fill in now for some reason.

    • Prairie —

      I did the same thing the first time I set it up. I wound up having two blank columns in the middle of my spreadsheets.

      What I did was adjust the field number in the script — I replaced:

      entry.1.singlewith:
      entry.2.single

      Then it logged everything correctly.

      • Also, if you walk away and come back to the vendor it’s not going to log you unless nine other people have come past in the meantime. That’s the check the vendor uses to avoid uploading the same person a million times.

        Another approach would be to upload a person only, say, every five minutes. So if they hang around for five minutes, it would upload them again. 

        You can also upload a person each time they click on an object — in that case, you don’t have to worry about checking if they’ve been by before, since nobody is going to click on anything a million times. 

      • Heh, that worked like a charm Maria, you get the gold star tonight

        The line in part WAS;

         “&entry.0.single=” + detected_name + “&entry.1.single=” +

        The line now in part reads;

         “&entry.0.single=” + detected_name + “&entry.2.single=” +

        easy fix 🙂

  • Chris Collins

    I did a talk on how to do this in early 2010 had it set to go to Amazon Cloud(you could do it to a spreadsheet) it will even track the path of the avatars so you could do a heatmap. Here is the presentation on how to set it up http://tiny.tipodean.com/xcmag (specific area starts on page 17). Yuu can get it all running and hosted for free and the presentation summarizes how to do it. Then just go crazy with analysis

    • Looks like the LSL side is the same as what you were doing. Having a Google spreadsheet saves having to figure out how to set up an Amazon database and write PHP code.

  • Fleep Tuque

    Thanks much for this, Maria – it’s working like a charm and I’m happy to learn FleepGrid gets more traffic than I thought it did!  🙂

  • Really great stuff Maria! Just wondering if you stumbled across any ways of then accessing data from a Google doc into LSL?

    • Lachlan —

      I haven’t, but offhand, I’d say, publish the doc as read-only to web, pull in the web page itself, and parse the HTML.

  • As promised, I wrote a script to pull all the collected data back in to LSL:

    http://www.hypergridbusiness.com/2012/02/pull-in-data-from-google-spreadsheet/ 

    The script I wrote pulls the names of the last 25 visitors and displays them on a prim, using OpenSim’s text-writing commands. (OpenSim is so cool.) But of course you can use it for anything you want.

    And you can pull in any kind of data you’ve got in a Google spreadsheet — a list of object coordinates or shape specs, game scores, hypergrid landmarks, links to image files (say, for a presentation), and so on and so forth.

  • Just a quick reminder — the attached script adds data to MY spreadsheet. 

    I can see that folks have been running it as is, because funky data is being uploaded. Not that I care! I use a different spreadsheet for the real data collection. 

    But if you can’t figure out why your spreadsheet isn’t being updated like it’s supposed to, check to make sure that you pasted in your spreadsheet’s form ID, and didn’t leave in my default one!

  • Pepper

    Hello, thanks Maria Korolov for you sharing.
    But a question, I’m a begginer; and you source code ask my :
    Why ask a visitor? if can put any false information..
    Why you d’ont creat a direct request for information about visitor?

    • I’m sorry, but I don’t understand the question.

  • Peter

    Really neat Maria.

    I have searched around the web looking for details on this method to submit data to the Google spreadsheet using a url with no success. Is this an official API or is it something that is undocumented that has been found out through clever detective work?

    Well done and thank you.
    Peter

    • I think it’s something I stumbled on online somewhere and the rest is a lot of trial and error.

  • Potter

    hello,
    if a visitor can input fake data (may be if i request his email id but he enter fake one)..so any way that i track him….like any ip tracking or email traking

  • Claudia Sanz

    I am not able to make this script work. The google spreadsheet i generate has a url like this https://docs.google.com/spreadsheet/ccc?key=RANDOMCHARACTERSHERE#gid=0. I take the info between key= and #grid.. and place it in the script. Just as you instructed above. However, you say to go to Form> Live view to get the above url. When I do that my url looks different https://docs.google.com/forms/d/DIFERENTRANDOMCHARACTERS/viewform. My spreadsheet info is different from the live form view

    • Claudia — You’re the second person to contact me about this, and it seems that Google has completely revamped how their forms work. I’m still trying to figure out whether my approach will be possible with the new forms. Meanwhile, I’m recommending that folks use legacy forms, which will continue to be supported for an unknown amount of time.

      You can create a new legacy form by logging into your Google account, and then going to: drive.google.com/spreadsheet/newform

      The other alternative is to use a database, and I’ll be posting more of those examples, as well.

  • Zuza Ritt

    Thanks for this script Maria. Script currently works, but some changes are needed. Because it is too long for comment, I made the howto in my web:
    http://opensim.21strom.com/en/2013/visitor-counter-google-spreadsheet-update-for-2013/

    • Thanks for the info! I couldn’t figure out how to do this.

      • Zuza Ritt

        Actually it is only change of the URL for submitting values… anybody can do it, even non-programming begginers:)

  • Cordts Baxton

    Messed with this today and started searching for some way to get this to put the info on the spreadsheet and found this page a huge help 🙂
    http://opensim.21strom.com/en/2013/visitor-counter-google-spreadsheet-update-for-2013/
    I hope others see this as I did not read all the comments here lol if I did I would have found this link here and saved myself time setting this up.

  • Sam

    Dear Maria – you are a STAR …

    Several days looking around for the format of the new command ref Google Forms (to send data)…. What I would like to comment that you replied to my query promptly and in a professional and friendly way… THANKS a million