storing data

[Wulfie Reanimator]

13 hours ago, Estelle Pienaar said:

One thing that I found very helpful as a content creator with external data was that I can analyse the usage of an item. Is the product used one time and the forgotten? Are all the functions used or only some? How long does a customer use a product? For me as a game creator it is especially valuable to see at what point a player might get frustrated and stops playing my product. I have learned a lot by looking at the data. All of this is perfectly possible with google spreadsheets. I have never used experience keys but I doubt that you can as easily access aggregated information.

From what I understand, the Experience database is only accessible while the object is within land with your Experience enabled, so it's not very practical for a general-purpose storage system, yet...

Edited February 9, 2019 by Wulfie Reanimator

[chibiusa Ling]

11 hours ago, Wulfie Reanimator said:

From what I understand, the Experience database is only accessible while the object is within land with your Experience enabled, so it's not very practical for a general-purpose storage system, yet...

Hopefully that will change when global experiences are released. Its a shame there isn't a way to see what data we have stored in our experiences beyond having to look it up via code. If they created an experience database browser then that would be nifty

[Xiija]

@Jazmina Voom

when you right click your google spreadsheet, you choose 'view page source' ( at least i can in firefox)

at the bottom of the source page you will see something like...

<script type="text/javascript">activeSheetId = '653993593'; switchToSheet('653993593');</script></body></html>

-

when you want to GET the info from the linked google spreadsheet, your url looks something like this...

"https://docs.google.com/spreadsheets/d/1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE/pubhtml?gid=653993593&single=true&gid=0&output=txt&range=H4";

( ...&range=H4...returns what is in cell H4 )

so the place where you see ... pubhtml?gid=653993593 ... is the sheet id from the page-source ( activeSheetId = '653993593'; )

so your response in the SecondLife GET request will return what is in

that cell ( H4 in this example) from that google sheet

 

Example:

https://docs.google.com/spreadsheets/d/(your-sheet)/pubhtml?&gid=(your-sheet-id)&single=true&gid=0&output=txt&range=(what cell you want)

 

links:

the sheet from this example

 

the actual url of the cell's return value

 

SL wiki: How to handle a GET request

 

Edited February 13, 2019 by Xiija

[oOPussInBootsOo]

ok I really would like to use this to just get some simple data I need.

"<script type="text/javascript">activeSheetId = '653993593'; switchToSheet('653993593');</script></body></html>" dose not show up in firefox or chrome for me.

your sheet your talking about "Sheet1" correct?

[Xiija]

@oOPussInBootsOo

(ETA: ty to Puss for catching the mistaken link )

to see the id from my example, you have to use the ...pubhtml link, not the edit one

just copy your sheet http link, but change  it like so...

https://docs.google.com/spreadsheets/d/1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE/pubhtml

( click this link below and check the page source )

The link with the sheet ID

Edited February 15, 2019 by Xiija

[oOPussInBootsOo]

I got it working pretty much, when I try and pull the data into sl but I'm pulling the full html into secondlife and none of the data on the sheet. So my thing is is there a trick to only getting the cell info and not the full html code from site?

[Xiija]

so, a kinda hacky trick... when you send your data to the form, use a "marker"

your marker could be anything.. like .... ~start~  or .... ~end~

in this example, i used something easy to find.... @!@ ..... was my marker.

so if the data you send is ...." HTTPtest 2-16-2019"  ... just add your marker to that to get...

@!@HTTPtest 2-16-2019

 

on the sheet, my cell ... ( c6) ...looked like this ....

@!@HTTPtest 2-16-2019

string sheetID = "1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE";
string sheetURL = "https://docs.google.com/spreadsheets/d/1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE/pubhtml?gid=653993593&single=true&gid=0&output=txt&range=C6";
key selfCheckRequestId;

default
{
    state_entry()
    {       
    }
    touch_start(integer total_number)
    {  selfCheckRequestId =
        llHTTPRequest( sheetURL,
        [HTTP_METHOD, "GET",
        HTTP_VERBOSE_THROTTLE, FALSE,
        HTTP_BODY_MAXLENGTH, 16384],
        "");      
    }   
    http_response(key id, integer status, list metaData, string body)
    {     
        if (id == selfCheckRequestId)
        {     string marker =  "@!@"; 
              integer start = llSubStringIndex(body,marker);        
              integer end = llSubStringIndex(body, "</td>");     
              string fetch =   llGetSubString(body,start + llStringLength(marker), end-1);             
              llOwnerSay("\n response: \n" + fetch);
        }       
    } 
}

 the inworld response from putting this code in a box and touching it was...

the value of cell c6 (with the marker bit removed)

Quote

[19:58] Object:
 response:
HTTPtest 2-16-2019

as i said , this is hacky ... it may break depending on where "</td>" appears on the sheet...

you ~could~ add an ending marker to your sent data ... mebbe it would look like ... @!@HTTPtest 2-16-2019 @!!@

to make is more safe to parse?

the NEW google stuff has an API ( you have to get a key) ... https://developers.google.com/sheets/api/

 

Edited February 16, 2019 by Xiija

[Jazmina Voom]

On 2/16/2019 at 1:37 AM, oOPussInBootsOo said:

I got it working pretty much, when I try and pull the data into sl but I'm pulling the full html into secondlife and none of the data on the sheet. So my thing is is there a trick to only getting the cell info and not the full html code from site?

I used the link I get directly from right-clicking the cell in the sheet (Get link to this cell) and I also get all the html when I call it, but I also get all the data. If I understand corrcetly this data needs to be parsed to get what I want out but it seems so clunky that it does not seem like a solution at all. I'd have to know what I am looking for or where it is before I find it.

I feel like there must be an easier way to pull the contents of a cell out of a Google sheet and into SL but so far I have not found it.

Thanks again @Xiija for posting your solution! If I understand the above, I would have to put different markers on all my data in order to find them again and then I need to keep track of all the markers, which defeats the purpose (for me) of keeping data external in the first place.

What I want is to be able to find a particular cell in column A (UUID) and return what is in the next columns on the same row (Name, rank, favorite sausage, etc.).

I don't know if this is possible. Maybe if I can import the whole list I can search it in SL? Will keep trying...

[steph Arnott]

On 2/9/2019 at 5:22 PM, chibiusa Ling said:

Hopefully that will change when global experiences are released. Its a shame there isn't a way to see what data we have stored in our experiences beyond having to look it up via code. If they created an experience database browser then that would be nifty

Will be premium paying members only.

[chibiusa Ling]

13 minutes ago, steph Arnott said:

Will be premium paying members only.

Yep, I know. I have premium myself. Experience keys come in very handy I just can't wait for them to go global. I have some neat ideas I want to do but currently hampered by the lack of global auto granted experiences 😁

[steph Arnott]

1 minute ago, chibiusa Ling said:

Yep, I know. I have premium myself. Experience keys come in very handy I just can't wait for them to go global. I have some neat ideas I want to do but currently hampered by the lack of global auto granted experiences 😁

99.5% of agents in SL are not and never will be premium.

[Qie Niangao]

Important to note that an Experience owner (who must be Premium) can grant anybody developer permission for their Experience. There are a lot of Premium users who won't be using their Experiences, so it pays to have friends who trust you not to abuse their stuff.

More importantly, Experiences can be used by anybody, so any handy KVP store can be accessed by Experience-compiled scripts owned by anybody. (Naturally, the Experience owner / developer will want to keep those scripts from getting into too much trouble.) Right now those scripts will only run on Experience-enabled land, and getting rid of that specific limitation is the most coveted feature of future grid-scope Experiences.

Also of note, it's very possible to access Experience data remotely, anywhere, even now, by whatever means one chooses to propagate the data accessed first by a script on an Experience-enabled parcel. That gets us back to the age-old problem of how best to propagate data outside a region; such transport is something at which Experiences themselves are extraordinarily adept, but only to other Experience-enabled land.

None of this is to say that I support keeping KVP persistent store bundled together with all the rest of Experiences, nor limiting its ownership to a user class with all the bundled baggage of Premium. Rather, my point is that non-Premium users can already benefit from KVP even now, and can anticipate further benefits with grid-scope experiences even if nothing else changes.

Edited February 21, 2019 by Qie Niangao

[steph Arnott]

12 minutes ago, Qie Niangao said:

Important to note that an Experience owner (who must be Premium) can grant anybody developer permission for their Experience. There are a lot of Premium users who won't be using their Experiences, so it pays to have friends who trust you not to abuse their stuff.

More importantly, Experiences can be used by anybody, so any handy KVP store can be accessed by Experience-compiled scripts owned by anybody. (Naturally, the Experience owner / developer will want to keep those scripts from getting into too much trouble.) Right now those scripts will only run on Experience-enabled land, and getting rid of that specific limitation is the most coveted feature of future grid-scope Experiences.

Also of note, it's very possible to access Experience data remotely, anywhere, even now, by whatever means one chooses to propagate the data accessed first by a script on an Experience-enabled parcel. That gets us back to the age-old problem of how best to propagate data outside a region; such transport is something at which Experiences themselves are extraordinarily adept, but only to other Experience-enabled land.

None of this is to say that I support keeping KVP persistent store bundled together with all the rest of Experiences, nor limiting its ownership to a user class with all the bundled baggage of Premium. Rather, my point is that non-Premium users can already benefit from KVP even now, and can anticipate further benefits with grid-scope experiences even if nothing else changes.

Only if you give perms in a experience.  Out side it is useless.

[Qie Niangao]

54 minutes ago, steph Arnott said:

Only if you give perms in a experience.  Out side it is useless.

If somebody just wants to use an Experience's KVP for storage, they don't need any permissions to piggyback on some other developer's Experience script that exposes a storage API.

(Nobody needs experience permissions -- the stuff granted through llRequestExperiencePermissions() -- for that Experience's KVP store to be used in scripts. That's why it's kinda dopey that KVP is bundled together with Experiences.)

[Innula Zenovka]

2 hours ago, steph Arnott said:

99.5% of agents in SL are not and never will be premium.

As Qie says, it doesn't really matter for the purposes of KVP values.   The various LL games like Linden Realms, Horizons, TCMG and so on all use KVP to keep track of players' scores, and obviously they aren't restricted to premium members.

Similarly, if you call one of the KVP functions like llReadKeyValue or llUpdateKeyValue in a script, that doesn't require the script's user to grant any experience perms.   The object containing the script needs only to be on land where the experience is allowed to run.   If the script also wants to use experience tools to teleport you, animate you or whatever then it does require experience perms, but not simply in order to read and update your score in a game or contest.

[chibiusa Ling]

They need to hurry up and tell us what the different premium packages are going to be. I am paying monthly at the minute as I don't want to pay for a whole year until I know what the different packages are and what they offer.

[steph Arnott]

5 minutes ago, chibiusa Ling said:

They need to hurry up and tell us what the different premium packages are going to be. I am paying monthly at the minute as I don't want to pay for a whole year until I know what the different packages are and what they offer.

Well, at the moment not a lot. Also i get charged far more than premium on sales and money exchange commission so why would i pay more for basically almost zero?

[Lucent Freiman]

On 3/20/2018 at 12:39 AM, Xiija said:

@Rhiannon

I'm tryin to remember it all heh... something like...

You create a google form, and a google spreadsheet, and link the two together,

so all form submissions go to the sheet.

Then change the form url ... from /edit  to /viewform  and check page source, to get entry for answer boxes.( f12 ... or right click &  inspect element)

To POST to the form/sheet you use something like this for your url string...( my form had 2 boxes, ... region name, and url of the sending prim)

string send =
      "https://docs.google.com/forms/d/e/1FAIpQLSfmFFNdHlOpvkXBU_AyPKj4ATjTJdS8iA2N3K2ervPNxNwE_A/formResponse?entry.1012667783="
         + region + "&entry.457815027=" + url + "&submit=Submit";

 ( notice the .... &entry.457815027= .... those keys come from looking at the page source / inspecting the answer box elements on the form webpage.)

when you want to GET the info from the linked google spreadsheet, your url looks something like this...

"https://docs.google.com/spreadsheets/d/1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE/pubhtml?gid=653993593&single=true&gid=0&output=txt&range=H4";

( returns what is in cell H4 )

you can use normal google sheet formulas to shift the data on the sheet how you need it ...

hope this helps a bit?

 

p.s. i'm guessing this is NOT a secure way to store data?...not even sure about what perms to view/edit you need

   on your sheet & form

 

p.p.s. ( not sure if any can view these, but...)

the form I used...

sim servers google form

the google spreadsheet...

sim server responses google spreadsheet

 

 

i tryed this solution but i dont get my script to send it can you please show me the full script (or the part i need) because i think i missed something that the script do the command

iam realy new to scripting and i need this database thing

[Xiija]

@Lucent FreimanGoogle has changed their api, and will discontinue the v3 in a few months.. will have to check the new stuff

Edited May 20, 2020 by Xiija

[Xiija]

I've tested a repl.it with NeDB and it seems to work ok... prolly not for a large deploy tho?

[Xiija]

There is a newer npm  library for google sheets here ...

https://theoephraim.github.io/node-google-spreadsheet/#/

incase you can use js in your own server etc...

[Lucia Nightfire]

It would be great if some day, someone makes a "Dummies" guide to accessing/changing google sheets cell(s) data via LSL with actual simple working examples.

I've tried messing with it in the past, but never got the security/oath keys to be accepted. Then Experiences came along with KVP and I never looked at it again.

[HajraLegacy]

Hi there, @Xiija. I have been following along with the Google Sheets data storage method and got at least one URL to work (Kinda), but I cannot for the life of me get a second one to work. Can anyone help me? I read that you should wait at least 60 seconds for the next request, which is fine - However I noticed that even when defining my separate URLs, I receive the same response from the first URL even though I am requesting data from an entirely different cell. What do I do? (FYI I am not very script savvy, but I try!)

On 2/15/2019 at 6:04 PM, Xiija said:

so, a kinda hacky trick... when you send your data to the form, use a "marker"

your marker could be anything.. like .... ~start~  or .... ~end~

in this example, i used something easy to find.... @!@ ..... was my marker.

so if the data you send is ...." HTTPtest 2-16-2019"  ... just add your marker to that to get...

@!@HTTPtest 2-16-2019

 

on the sheet, my cell ... ( c6) ...looked like this ....

@!@HTTPtest 2-16-2019

string sheetID = "1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE";
string sheetURL = "https://docs.google.com/spreadsheets/d/1MNKwhVqK8U8urzTWa_q0YtlGnYVn7SnICZBylstR2KE/pubhtml?gid=653993593&single=true&gid=0&output=txt&range=C6";
key selfCheckRequestId;

default
{
    state_entry()
    {       
    }
    touch_start(integer total_number)
    {  selfCheckRequestId =
        llHTTPRequest( sheetURL,
        [HTTP_METHOD, "GET",
        HTTP_VERBOSE_THROTTLE, FALSE,
        HTTP_BODY_MAXLENGTH, 16384],
        "");      
    }   
    http_response(key id, integer status, list metaData, string body)
    {     
        if (id == selfCheckRequestId)
        {     string marker =  "@!@"; 
              integer start = llSubStringIndex(body,marker);        
              integer end = llSubStringIndex(body, "</td>");     
              string fetch =   llGetSubString(body,start + llStringLength(marker), end-1);             
              llOwnerSay("\n response: \n" + fetch);
        }       
    } 
}

 the inworld response from putting this code in a box and touching it was...

the value of cell c6 (with the marker bit removed)

as i said , this is hacky ... it may break depending on where "</td>" appears on the sheet...

you ~could~ add an ending marker to your sent data ... mebbe it would look like ... @!@HTTPtest 2-16-2019 @!!@

to make is more safe to parse?

the NEW google stuff has an API ( you have to get a key) ... https://developers.google.com/sheets/api/

 

 

[Xiija]

there is a good video on the new google apps script stuff here...

these other posts may be outdated now?.. dunno...

 

Edited September 30, 2020 by Xiija

[HajraLegacy]

@Xiija well I actually used what you said in earlier posts to get to where I am with this script, so much appreciated for that. I'll have a look at this video but I'm not certain it'll help me out if I am not that knowledgeable about code in general lol.