You can access data in a Google Sheet within an Infoplus Script, using the sample script below as a recipe.
In this example, our sheet has a simple format:
The process of Publishing a Google Sheet and accessing it as JSON is described at https://coderwall.com/p/duapqq/use-a-google-spreadsheet-as-your-json-backend - a short version of the instructions is:
- In your Google Sheet, go to File -> Publish to Web
- Choose if you want to share the Entire Document or a single tab.
- Hit Publish.
Note: When publishing, please make sure the "Require viewers to sign in with their ... account" is unchecked. If the option to uncheck is disabled, please contact your Google administrator to open that up.
After doing those steps, the last thing you should need is just your Sheet's "Key" - which is the long identifier of the sheet's URL, for example, in this sheet URL:
You'll need to put that key into a URL like this https://spreadsheets.google.com/feeds/list/YOUR-SHEET-KEY/od6/public/values?alt=json - which the example script below does.
The exact format of the JSON data isn't as simple and clean as you may like - but with some logging and debugging you should be able to get to your data. Again, the example script here provides some recommendations on how to do this.
Important Note: Only bulk edit in batches of 500 or less. If you select a batch larger than 500, you will see significant system slow-downs.
- Best practice is to perform large bulk edits and/or loads outside of peak business hours