Infoplus Support

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:

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 the sheet's URL, for example, in this sheet URL:  https://docs.google.com/spreadsheets/d/1-ug1U2wU7qcrMtPK0aHsGCqUZB3nDwjv2SlioqHrwGA/edit#gid=19949201 they key is:  1-ug1U2wU7qcrMtPK0aHsGCqUZB3nDwjv2SlioqHrwGA

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.  

/////////////////////////////////////////////////////////////////////////////////////
// Fetch a google sheet in JSON format.  Follow the instructions here:             //
// https://coderwall.com/p/duapqq/use-a-google-spreadsheet-as-your-json-backend    //
// to learn how to publish a sheet, then paste the sheet's key into this variable: //
/////////////////////////////////////////////////////////////////////////////////////
var sheetKey = "1-ug1U2wU7qcrMtPK0aHsGCqUZB3nDwjv2SlioqHrwGA";
var httpResponse = utils.httpRequest({
   "method": "GET",
   "url": "https://spreadsheets.google.com/feeds/list/" + sheetKey + "/od6/public/values?alt=json"
});

/////////////////////////////////////////////////////////////////////////////////////
// you can use these log lines to verify you are successfully fetching the sheet.  //
// the response body should start with:                                            //
// {"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/... //
/////////////////////////////////////////////////////////////////////////////////////
// utils.log("Status: " + httpResponse.statusCode);
// utils.log(httpResponse.body);

////////////////////////////////////////////////////////////////
// next parse the http response (a string) into a json object //
// again, you can use logs to verify that it is working       //
////////////////////////////////////////////////////////////////
var sheet = utils.stringToJson(httpResponse.body);
// utils.log(sheet);

//////////////////////////////////////////////////////////////////
// get the entries from the sheet (note, the sheet object and   //
// sub-objects) are java Map objects - use the get(key) method. //
// iterate over the entries.                                    //
//////////////////////////////////////////////////////////////////
var entries = sheet.get("feed").get("entry");
for(var i=0; i<entries.size(); i++)
{
   var entry = entries.get(i);
   // utils.log("Entry: " + entry);

   ////////////////////////////////////////////////////////////////////////////////////////
   // values in the entry object appear to be under keys that match your sheet's         //
   // header row, with the 'gsx$' prefix - you may log the entry object to see your keys //
   ////////////////////////////////////////////////////////////////////////////////////////
   var sku = entry.get("gsx$sku").get("$t");
   var upc = entry.get("gsx$upc").get("$t");
   
   // utils.log("sku: " + sku);
   // utils.log("upc: " + upc);
   
   /////////////////////////////////////////////////////////////////
   // if the record passed into the script is found in the sheet, //
   // then update it.  then return to stop the script.            //
   /////////////////////////////////////////////////////////////////
   if(sku == record.sku)
   {
      record.upc = upc;
      utils.log("Updating Item [" + record.sku + "] UPC to [" + upc + "]");
      infoplusApi.update("item", record);
      return;
   }
}

utils.log("Reached end of script and did not find record in sheet.  Record has not been updated.");

NOTE: