pangore Posted June 28, 2021 Share Posted June 28, 2021 I am currently scripting something that uses google sheets as a database for ease of editing the information that needs to change for the object I'm scripting. The object is just a role-play HUD that allows for full characters to be made. The majority of the data is already being stored using a Experience with key pairs. Unfortunately the admins of this system cannot maintain the database through scripts and needed something more user friendly. Google Sheets seemed perfect for the solution however I am running into a problem with the body length limit in the system. The information that they need to keep is much longer then the set limit. I was shown another object by someone else that uses google sheets and pulls all the data from it which is clearly more data then the body limits. So my question is how to do I pull more information then the body limit for llHTTPRequests ? I already have the HTTP_BODY_MAXLENGTH set to max while using mono to compile. I'm sure there has to be a way since this over object I was shown is doing it. Just cant seem to figure it out on my own. Thank you. Link to comment Share on other sites More sharing options...
Quistess Alpha Posted June 29, 2021 Share Posted June 29, 2021 (edited) If you need this for anything more than a personal project, you really shouldn't be using Google sheets, and should probably learn how to operate an actual database, for example mySQL. Read at your own peril: Edited June 29, 2021 by Quistessa Link to comment Share on other sites More sharing options...
pangore Posted June 29, 2021 Author Share Posted June 29, 2021 I tried for years to learn how to make a php, sql database but failed time and time again so I'm not capable of that. I am already reading the data from google sheets fine and it works but the problem is in the body length limit. It seems that Linden has a hard cap setup within the llHTTPRequests so that it will only pull the first 16Kb of data from the request. Im trying to find away around that. Link to comment Share on other sites More sharing options...
Coffee Pancake Posted June 29, 2021 Share Posted June 29, 2021 16kb is a lot of data when your script process only has 64kb total for script and variables. 1 Link to comment Share on other sites More sharing options...
Lucia Nightfire Posted June 29, 2021 Share Posted June 29, 2021 (edited) 1 hour ago, pangore said: I tried for years to learn how to make a php, sql database but failed time and time again so I'm not capable of that. I am already reading the data from google sheets fine and it works but the problem is in the body length limit. It seems that Linden has a hard cap setup within the llHTTPRequests so that it will only pull the first 16Kb of data from the request. Im trying to find away around that. Good luck. I asked Oz Linden last year if we could have HTTP's Range header functionality added to llHTTPRequest() and he was worried it would increase HTTP traffic to third party sites. Edited June 29, 2021 by Lucia Nightfire 1 Link to comment Share on other sites More sharing options...
Coffee Pancake Posted June 29, 2021 Share Posted June 29, 2021 (edited) 30 minutes ago, Lucia Nightfire said: Good luck. I asked Oz Linden last year if we could have HTTP's Range header functionality added to llHTTPRequest() and he was worried it would increase HTTP traffic to third party sites. Maybe time to make a fresh JIRA for this one. SL getting "pages" of data 16kb at a time from 3rd party sites is pretty inconsequential in world with node.js Edited June 29, 2021 by Coffee Pancake Link to comment Share on other sites More sharing options...
pangore Posted June 29, 2021 Author Share Posted June 29, 2021 I have seen a product in world that uses google sheets as its database and pulls in more than the max limit for the body. Does anyone have any clues or guesses how they might have accomplished that? Link to comment Share on other sites More sharing options...
Mollymews Posted June 29, 2021 Share Posted June 29, 2021 3 hours ago, pangore said: I have seen a product in world that uses google sheets as its database and pulls in more than the max limit for the body. Does anyone have any clues or guesses how they might have accomplished that? probably multiple http requests that buffer the data in the script: string buffer; buffer += data; Link to comment Share on other sites More sharing options...
Innula Zenovka Posted June 30, 2021 Share Posted June 30, 2021 On 6/28/2021 at 9:06 PM, pangore said: I am currently scripting something that uses google sheets as a database for ease of editing the information that needs to change for the object I'm scripting. The object is just a role-play HUD that allows for full characters to be made. The majority of the data is already being stored using a Experience with key pairs. Unfortunately the admins of this system cannot maintain the database through scripts and needed something more user friendly. Google Sheets seemed perfect for the solution however I am running into a problem with the body length limit in the system. The information that they need to keep is much longer then the set limit. I was shown another object by someone else that uses google sheets and pulls all the data from it which is clearly more data then the body limits. So my question is how to do I pull more information then the body limit for llHTTPRequests ? I already have the HTTP_BODY_MAXLENGTH set to max while using mono to compile. I'm sure there has to be a way since this over object I was shown is doing it. Just cant seem to figure it out on my own. Thank you. This is in the nature of "are you sure it's plugged in?" but have you checked that you're publishing your Google Sheet as a .csv file and reading that URL? I've been caught out a couple of times by forgetting to do that and using the url for the HTML sheet, which retrieves a whole lot or formatting information but no data. Link to comment Share on other sites More sharing options...
pangore Posted June 30, 2021 Author Share Posted June 30, 2021 I double checked and made sure that it was published under a csv. When I use what I have scripted it shows the first full 18 rows of data and then it cuts off. The sheet has 114 rows and 13 columns so tons of data. The information it does pull it is just the data I need and no formatting information so that leads me to think I did set it to csv as well. It pulls so little information though that it makes me feel like something isn't going right. Its very simple script at the moment since I have been testing anything I can think of to pull more data but maybe someone will see something stupid I did. default { state_entry() { httpkey=llHTTPRequest(URL, [HTTP_BODY_MAXLENGTH,16384] ,"");} http_response(key id, integer status, list meta, string body) { DataList = llParseString2List(body, [",","\n"], [] ); RowCount = llGetListLength(llParseStringKeepNulls(body, ["\n"], [])); CellCount = llGetListLength(DataList); ColumnCount = CellCount/RowCount; llSay(0,"Total number of cells: "+(string)CellCount); llSay(0,"Total number of rows: "+(string)RowCount); llSay(0,"Total number of columns: "+(string)ColumnCount); string Tempstring = llDumpList2String(DataList, "~~"); llSay(0,"list - "+ Tempstring); DataList = []; } } 1 Link to comment Share on other sites More sharing options...
Qie Niangao Posted June 30, 2021 Share Posted June 30, 2021 (edited) I've never used Google Sheets from LSL so I may not understand; is this pulling in the entire spreadsheet in one great gulp? Would the API's .values.batchget method be an option, to kinda nibble-away at the data? Edited June 30, 2021 by Qie Niangao (replace with working link) Link to comment Share on other sites More sharing options...
pangore Posted June 30, 2021 Author Share Posted June 30, 2021 I'm going to guess that is something to do with the API for google? I'm not sure how to even get started using that. Link to comment Share on other sites More sharing options...
Wulfie Reanimator Posted June 30, 2021 Share Posted June 30, 2021 You can't just request the entire sheet and expect to be able to receive or store it within LSL. It's just not possible, there's not enough memory to work with. You have to request smaller parts of the sheet, process those, then request more. 5 minutes ago, pangore said: I'm going to guess that is something to do with the API for google? I'm not sure how to even get started using that. Start here: https://developers.google.com/sheets/api/samples/reading#read_a_single_range 1 Link to comment Share on other sites More sharing options...
pangore Posted June 30, 2021 Author Share Posted June 30, 2021 Ok, thank you. Had hoped for a lsl solution since I have no luck with other languages. Ill read and see what I can figure out. Hopefully it will be easier then im thinking. Link to comment Share on other sites More sharing options...
Wulfie Reanimator Posted June 30, 2021 Share Posted June 30, 2021 24 minutes ago, pangore said: Ok, thank you. Had hoped for a lsl solution since I have no luck with other languages. Ill read and see what I can figure out. Hopefully it will be easier then im thinking. All you need is to change the URL within your LSL script. The instructions for what you need to change are in the page I linked. 1 Link to comment Share on other sites More sharing options...
Innula Zenovka Posted June 30, 2021 Share Posted June 30, 2021 17 hours ago, pangore said: Ok, thank you. Had hoped for a lsl solution since I have no luck with other languages. Ill read and see what I can figure out. Hopefully it will be easier then im thinking. As Wulfie suggests, you can still do it all in LSL. You just have to reference the range in the spreadsheet you want it to read, rather than the first however many it is. If you look at the sample code in the link, you should see what you need to change. And if it's a long list of values, then you'll have to read it in multiple calls, stepping down the page so many rows at a time. Link to comment Share on other sites More sharing options...
pangore Posted June 30, 2021 Author Share Posted June 30, 2021 Yes thank you. i have been working through that since I saw the link. Currently attempting to solve a "code": 403 "message": "The caller does not have permission" "status": "PERMISSION_DENIED problem. Set the API up, got my API key as well. just reading through trying to solve it. Link to comment Share on other sites More sharing options...
Drakeo Posted July 3, 2021 Share Posted July 3, 2021 LSL This is within the platform. So the environment is controlled in the platform. what is LSL. Only runs in the platform. only output you get is what the server gives to the client. that may change per updates of the simulator and client. we are dealing with so much much more than platform. W are dealing with abuse. 2 Link to comment Share on other sites More sharing options...
Drakeo Posted July 3, 2021 Share Posted July 3, 2021 (edited) On 6/30/2021 at 2:37 PM, Innula Zenovka said: As Wulfie suggests, you can still do it all in LSL. You just have to reference the range in the spreadsheet you want it to read, rather than the first however many it is. If you look at the sample code in the link, you should see what you need to change. And if it's a long list of values, then you'll have to read it in multiple calls, stepping down the page so many rows at a time. Wulfi should know what creates the output so your putting words in wulfi mouth Edited July 3, 2021 by Drakeo 2 Link to comment Share on other sites More sharing options...
Drakeo Posted July 3, 2021 Share Posted July 3, 2021 On 6/28/2021 at 9:15 PM, Quistessa said: If you need this for anything more than a personal project, you really shouldn't be using Google sheets, and should probably learn how to operate an actual database, for example mySQL. Read at your own peril: who would use that MySQL. only windoze people amazon ebay etc etc all use. mariadb it is opensource drop in for MYSQL why is that big we can see the diff how many times have these guys paid ransom 0 1 Link to comment Share on other sites More sharing options...
Quistess Alpha Posted July 3, 2021 Share Posted July 3, 2021 https://github.com/mysql/mysql-server The "community" version is free and open-source. but that's totally tangential. Database software is all the same to the enthusiast layman. Link to comment Share on other sites More sharing options...
Profaitchikenz Haiku Posted July 3, 2021 Share Posted July 3, 2021 MariaDB is also free and opensource, if it makes a difference. 1 Link to comment Share on other sites More sharing options...
bobsknief Orsini Posted July 4, 2021 Share Posted July 4, 2021 You are probably stuck in having to implement a database solution and use some PHP, how else are you going to implement the Google Sheets API authentication part. I am assuming you eventually want to store data to instead of just reading it and that is something totally different (and to my knowledge you still need a other script outside of SL to do the authentication part). You are limited in the amount of data you get, so you want to get a specific set of data based on some parameters. If you have control over this, you can get your data in multiple steps and store this data in multiple scripts working around the script memory limit. Link to comment Share on other sites More sharing options...
pangore Posted July 4, 2021 Author Share Posted July 4, 2021 I am currently using LSL Experiences for the data storage part of the design. The google sheets was read only and was being used for ease of use by the admins of the sim I'm designing this for. They don't like notecards and scripting to make changes was to difficult for them. I was able to get past the authentication problems and now I'm just trying to learn how to exclude the # symbol from the incoming body of the request. Everything else is working perfectly fine. I learned how to pull parts of the data from Google although i have to say the amount of data I can pull from sheets is remarkable low needing for multiple pulls. Link to comment Share on other sites More sharing options...
Quistess Alpha Posted July 4, 2021 Share Posted July 4, 2021 2 hours ago, pangore said: how to exclude the # symbol from the incoming body of the request. strReplace? // http://wiki.secondlife.com/wiki/Combined_Library string strReplace(string str, string search, string replace) { return llDumpList2String(llParseStringKeepNulls((str = "") + str, [search], []), replace); } Link to comment Share on other sites More sharing options...
Recommended Posts
Please take a moment to consider if this thread is worth bumping.
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now