How to read data directly from Google Sheets.

[Innula Zenovka]

And it turns out it's remarkably easy.  

I had made a note of this url some time ago when I saw it in a discussion here -- I'm sorry, but I can't remember any more than that at the moment.

Anyway, I've been playing with it and it seems great for limited amounts of data -- there's a hard limit of 16,384 bytes you can receive if you set HTTP_BODY_MAXLENGTH in the http request event vs 65,536 in a notecard, but you can still fit a lot into 16 KiB, I've discovered.

You need to read the article to understand how to use the script in the second link, but it's very simple if you feel confident handling lists.

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

https://www.hypergridbusiness.com/wp-content/uploads/2012/02/Script-to-pull-in-data-from-Google-spreadsheet.txt

It also has the advantage of being available all over the grid, without needing the land to be set to a particular experience.

Edited March 4, 2021 by Innula Zenovka

[Fritigern Gothly]

What would be interesting is a method to post data to a sheet. 
For example, an in-world product update server posts its UUID to a sheet (its UUID changes each time the server is rezzed, and even after a region restart). 
Each time a product is worn, it reads the UUID from a sheet and queries the in-world server because with the UUID you can do this. If an update exists, the server sends the updated product.

There are other possible applications, like a master station list for in-world radios or TVs, or a grid-wide hunt server or... or... or... there are many ways that could be used.

[Quistess Alpha]

Weren't people just talking about something similar on another thread? I didn't follow it closely but I thought they said something about google's api changing recently.

https://community.secondlife.com/forums/topic/419831-storing-data/

[Innula Zenovka]

18 hours ago, Fritigern Gothly said:

What would be interesting is a method to post data to a sheet. 
For example, an in-world product update server posts its UUID to a sheet (its UUID changes each time the server is rezzed, and even after a region restart). 
Each time a product is worn, it reads the UUID from a sheet and queries the in-world server because with the UUID you can do this. If an update exists, the server sends the updated product.

There are other possible applications, like a master station list for in-world radios or TVs, or a grid-wide hunt server or... or... or... there are many ways that could be used.

 

 Maria Korolov explains a basic method to post data to a sheet here:

https://www.hypergridbusiness.com/2014/10/easier-google-spreadsheet-visitor-logger/

and she provides a code example here

https://www.hypergridbusiness.com/wp-content/uploads/2014/10/Easier-Google-Spreadsheet-Visitor-Logger-hardcoded.txt

Based on that, this is how I construct a script to parse the spreadsheet URL and use that to post data to a sheet.    This simple example might be used in a vendor, but the method works for anything.

First, decide on the fields you want to store.   For my vendor, I want to store the purchaser's uuid and username, the name of the item they bought, the price and the name of the region in which the vendor is located.    There is no need to store a timestamp, because Google adds one to the recordset automatically. 

Go to Google Drive (you need a Google account for this, obviously) and create a new google form.   Name it.   

 

Change the question format in the dropdown from "Multiple Choice" to "Short Answer" and enter the name of the first field, Avatar UUID.     Click the top button in the toolbar on the right hand side (plus sign in a circle) to add a new question, Avatar UUID, and repeat the process to complete the form.     There's no need to save anything -- Google saves it all automatically.

When you've finished, click the Responses button at the top of the form.

Click the green Google Sheets icon at the top right.   This opens a box that offers you the choice of creating a new worksheet or posting them to an existing one.   Choose the first (create new spreadsheet) and edit the name if desired.    Click "Create".

This creates and opens a new spreadsheet 

https://docs.google.com/spreadsheets/d/1aphV6xptbPCcmQsLv76angzxvDjrnAsXWrs4jjm2dXQ/edit#gid=1386336349

Return to the Form design tab, and click on the icon with the three dots in a vertical row at the top right of the window.

Click the "Get pre-filled link" button.   This opens a new copy of the form.   Enter a brief answer for each question -- these are placeholders that won't be used, but they do make understanding the form's URL a lot simple where you come to try to break it down with LSL, which happens soon.

When you've finished completing the answers, click on Get Link

  

Click Get Link, and click again to copy the link to your clipboard when asked.   

The link will look something like this 

https://docs.google.com/forms/d/e/1FAIpQLSd7P8o7pUqw50Y4ANPXbuwdjpICCMaUk9e_sk6AqXXVisdaSQ/viewform?usp=pp_url&entry.1880737542=AvUUID&entry.1480423389=AvName&entry.203046533=ItemName&entry.805865068=ItemPrice&entry.1286272884=RegionName

That opens a prefilled form in your browser, which is good if you're filling it out online but not so good for LSL.   

If you break the URL down, you can see it comprises several components:

https://docs.google.com/forms/d/e/1FAIpQLSd7P8o7pUqw50Y4ANPXbuwdjpICCMaUk9e_sk6AqXXVisdaSQ //points to the form

/viewform?usp=pp_url //instructs the viewer to open the form 

//and prepopulate it with the following values
&entry.1880737542=AvUUID
&entry.1480423389=AvName
&entry.203046533=ItemName
&entry.805865068=ItemPrice
&entry.1286272884=RegionName

Now, we need to alter this so that it opens the form, pre-populates it with values caught by the script at run-time rather than the placeholders"AvUUID" and so on, and posts it to G-sheets automatically without opening a browser window.

Here's how I do it.

First, you need to store the numerical identifiers, 1880737542=AvUUID and so on,  and store the values to variables, thus

string strFrontPart;
string strPostData;

string strFullURL= "https://docs.google.com/forms/d/e/1FAIpQLSd7P8o7pUqw50Y4ANPXbuwdjpICCMaUk9e_sk6AqXXVisdaSQ/viewform?usp=pp_url&entry.1880737542=AvUUID&entry.1480423389=AvName&entry.203046533=ItemName&entry.805865068=ItemPrice&entry.1286272884=RegionName";

string strAvUUIDIdentifier;
string strAvNameIdentifier;
string strItemNameIdentifier;
string strItemPriceIdentifier;
string strRegionNameIdentifier;


list lTemp;


default {
    state_entry() {

        strPostData = strFrontPart  + "/formResponse" +
        strFrontPart = llList2String(llParseString2List(strFullURL, ["/viewform"], []), 0);
        //this extracts the url for the Google data entra form that the script uses to post data to the G-Sheet, and assigns it to the variable strFrontPart

        strFullURL = llDeleteSubString(strFullURL,0,llSubStringIndex(strFullURL,"entry.")+5);
        //now extract the form's field identifiers from the URL, to use as arguments when posting the data
        lTemp = llList2ListStrided(llParseString2List(strFullURL, ["/", ".","="],[]),0,-1,2);

        strAvUUIDIdentifier = llList2String(lTemp, 0);
        strAvNameIdentifier = llList2String(lTemp, 1);
        strItemNameIdentifier = llList2String(lTemp, 2);
        strItemPriceIdentifier = llList2String(lTemp, 3);
        strRegionNameIdentifier = llList2String(lTemp,4);
    }

}

How to use them?

What follows is a script fragment, obviously, and any real vendor script would be considerably more complex, but it should provide the general idea

integer iMaxAttempts = 4;

integer iCounter;

key kHTTPRequest;

string strPurchase;

string strFrontPart;//holds the url for the Google form that collects data for the sheet
string strPostData;//holds the url + the data to send to the sheet

string strFullURL= "https://docs.google.com/forms/d/e/1FAIpQLSd7P8o7pUqw50Y4ANPXbuwdjpICCMaUk9e_sk6AqXXVisdaSQ/viewform?usp=pp_url&entry.1880737542=AvUUID&entry.1480423389=AvName&entry.203046533=ItemName&entry.805865068=ItemPrice&entry.1286272884=RegionName";

string strAvUUIDIdentifier;
string strAvNameIdentifier;
string strItemNameIdentifier;
string strItemPriceIdentifier;
string strRegionNameIdentifier;


list lTemp;


default {
	state_entry() {

		strPostData = strFrontPart  + "/formResponse" +
			strFrontPart = llList2String(llParseString2List(strFullURL, ["/viewform"], []), 0);
		//this extracts the url for the Google data entra form that the script uses to post data to the G-Sheet, and assigns it to the variable strFrontPart

		strFullURL = llDeleteSubString(strFullURL,0,llSubStringIndex(strFullURL,"entry.")+5);
		//now extract the form's field identifiers from the URL, to use as arguments when posting the data
		lTemp = llList2ListStrided(llParseString2List(strFullURL, ["/", ".","="],[]),0,-1,2);

		strAvUUIDIdentifier = llList2String(lTemp, 0);
		strAvNameIdentifier = llList2String(lTemp, 1);
		strItemNameIdentifier = llList2String(lTemp, 2);
		strItemPriceIdentifier = llList2String(lTemp, 3);
		strRegionNameIdentifier = llList2String(lTemp,4);

		strPurchase = llGetInventoryName(INVENTORY_OBJECT, 0);
	}

	changed(integer change) {
		if(change & CHANGED_INVENTORY){
			llResetScript();
		}
	}

	money(key id, integer amount) {
		//first, check the payment, give the avatar, id,  their purchase, strPurchase, and thank them.
		//now, build an url to send the data to GSheets

		strPostData =
			strFrontPart  + "/formResponse" +"?entry."//open the form, but not in the browser
			//nb line ends with ?entry, not @entry for second and subsequent lines
			+ strAvUUIDIdentifier  + "=" + llEscapeURL((string)id) +"&entry."
			+ strAvNameIdentifier + "=" + llEscapeURL(llGetUsername(id))+"&entry."
			+ strItemNameIdentifier  + "=" + llEscapeURL(strPurchase)+ "&entry."
			+ strItemPriceIdentifier  + "=" + llEscapeURL((string)amount)+ "&entry."
			+ strRegionNameIdentifier  + "=" + llEscapeURL(llGetRegionName())+
			"&submit=Submit";
		//after filling in all fields, tell the form to submit the data to the sheet

		llOwnerSay("sending "+strPostData);
		kHTTPRequest = llHTTPRequest(strPostData, [HTTP_METHOD, "POST"] ,"");
	}


	http_response(key request_id, integer status, list metadata, string body) {
		if(request_id == kHTTPRequest){
			if((status != 200) && (++iCounter < iMaxAttempts)){
				//in case you couldn't make the connection first time
				llSleep(0.25);
				kHTTPRequest = llHTTPRequest(strPostData, [HTTP_METHOD, "POST"] ,"");
			}
			else{
				llOwnerSay("Successfully posted "+strPostData);
			}

		}

	}



}

At the moment, I'm working on a script to post data from SL to a Google Sheet,  which runs a query (or vLookUp) on the last entry in the column that holds the incoming data, and posts the results to a second tab in the spreadsheet.   

Then on receiving the 200 status confirmation, the script reads that second tab (using the method in my first post) and retrieves the results relating to whatever input you just send (so someone selects a genre from a list presented in-world by llDialog, sends that to GSheets, and then reads the list of titles in that genre returned by the code in GSheets.   

Then the user selects a title from that genre and submits that to GSheets, which runs a second query, and the script then downloads the list of sound clip uuids for your juke box to play, along with timings.

I'll post it (or something similar) when I'm finished, though since it's simply a project I'm using to learn how to do this,  anyone is more than welcome to try it for themselves and post the results.   

ETA:  Speaking as someone who often has to write very long and complex scripts that do a lot of list processing, I am beginning to consider the possible benefits of handling all that with G-Sheets' look up tools, with the script just sending the query to GSheets and reading the answer,.   Since I often have to look up values from multiple lists to find what I need, that might be easier to do remotely.    Easier on the script memory, too.

Edited March 5, 2021 by Innula Zenovka

[Innula Zenovka]

14 hours ago, Quistessa said:

Weren't people just talking about something similar on another thread? I didn't follow it closely but I thought they said something about google's api changing recently.

https://community.secondlife.com/forums/topic/419831-storing-data/

Yes, it's been discussed in several threads over the years, and nothing in my posts is particularly original -- I just thought it would be helpful to pull together what I've learned from those discussions and the articles they reference, along with what I've learned from working with G-Sheets and LSL for a couple of years now, on and off, and post a couple of examples in a new thread.   

I hope this helps people who are interested in learning how to connect to G-Sheets from SL, which I think offers us access to very powerful technology without having to set up a mySQL/php database, and maybe encourages them to experiment and post some ideas.

If anyone's interested, there's a detailed discussion at 

https://theconfuzedsourcecode.wordpress.com/2019/11/10/lets-auto-fill-google-forms-with-url-parameters/

about how the various codes in G-Sheets forms' urls work -- you can send a lot more than short text answers, if you want to (e.g. fill in checkboxes), though I'm not sure how often I'll need to do that.

ETA:   I've just noticed that this article shows how to lift the field identifiers directly from the form

https://theconfuzedsourcecode.wordpress.com/2019/11/11/you-may-restfully-submit-to-your-google-forms/

But parsing from the form's URL seems easier, at least to me.

Edited March 5, 2021 by Innula Zenovka

[Fritigern Gothly]

On 3/5/2021 at 5:09 AM, Innula Zenovka said:

Maria Korolov explains a basic method to post data to a sheet here:

https://www.hypergridbusiness.com/2014/10/easier-google-spreadsheet-visitor-logger/

Have you tried this yourself to confirm it still works? Because Google HAS made some changes since 2014 when that article was written.

[Innula Zenovka]

8 hours ago, Fritigern Gothly said:

Have you tried this yourself to confirm it still works? Because Google HAS made some changes since 2014 when that article was written.

Yes, I have.  I've been using the technique for a couple of years now.

You're welcome to try it yourself using the example I posted.     Rez a box, put the test script into it, pay the box some L$ (which go straight back into your account, of course) and check the results here:

https://docs.google.com/spreadsheets/d/1aphV6xptbPCcmQsLv76angzxvDjrnAsXWrs4jjm2dXQ/edit?usp=sharing

 

Edited March 7, 2021 by Innula Zenovka

[Bugs Larnia]

I use it as well. Very handy. You do need to keep in mind that when reading data from Google there can be a slight latency between the actual data update and the time it's visible in the data pull.