How to use data from a Google Sheet in an Infoplus Script for a Custom Bulk Edit

You can access data in a Google Sheet within an Infoplus Script, using the sample script below as a recipe.  

This can be used as an alternative approach to the technique described in Using an Infoplus Script to perform a custom Bulk Edit, so instead of manually manipulating your data into a javascript data structure, you can instead Publish the Google Sheet, then load it through an HTTP request into your script, and use it directly.  Or, any other Infoplus Script can use this technique to provide whatever solutions you can think of.  


In this example, our sheet has a simple format:

SKU UPC
BASIC1 123456789012
BASIC2 234567890123
BASIC3 345678901234
BASIC4 456789012345
BASIC5 567890123456

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:

  1. In your Google Sheet, go to File -> Publish to Web
  2. Choose if you want to share the Entire Document or a single tab.
  3. 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