Google Spreadsheet JSON Queries

GoogleSheetJson.md

SheetAsJSON + Filtering

This is an extension of DJ Adams' excellent SheetAsJSON Google Apps Script, which provides a way to GET a published Google Spreadsheet as a JSON feed. This version allows generic filtering for terms, more specific control over which rows to parse, and correct MIME type for JSONP output.

Minimal Usage

The following parameters are required for the script to work.

https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?
+ id=<spreadsheet key>
+ sheet=<sheet name on spreadsheet>

Per the original, the above script serves a representation of all the sheet's data as JSON, using the first row as the set of keys:

{ records : [
    { (row1, column1): (row2, column1), (row1, column2): (row2, column2), ..},
    { (row1, column1): (row3, column1), (row1, column2): (row3, column2), ..},
    ...
  ]
}

Try it: https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE&sheet=Summary&header=2&startRow=3

Optional Params

This version supports the following optional parameters:

* header=<row # of your header - if unsupplied, assumes row 1>
* startRow=<row # for the top row of the search area - if unsupplied, assumes row 2>
* <key>=<value to find>

The header and startRow are particularly useful if, like me, you use the first row to group sections in the header - or maybe you just want to search a different part of your Sheet.

You can add an arbitrary number of key-value pairs (obviously they'll only work if the keys exist in your sheet); results from the basic usage are filtered such that all returned objects satisfy Foo[key] == [value to find]. Due to the way regexps are used to remove white space, your query will also need to replace those fiendish %20s with underscores.

Try it: https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE&sheet=Summary&header=2&startRow=3&First_Name=Greatest&Last_Name=Ever

Have fun!

Built for the APO app by Ronald Martin

sheetAsJson.gs

/**
 * Main method is called when the script receives a GET request.
 * Receives the request, generates and returns the output.
 */
function doGet(request) {
  // Get request params.
  var sheetKey  = request.parameters.id;
  var sheetName = request.parameters.sheet;
  var callback  = request.parameters.callback;
  var headerRow = request.parameters.header;
  var startRow  = request.parameters.startRow;
    
  // Parse the spreadsheet.
  var spreadsheet = SpreadsheetApp.openById(sheetKey);
  var keys = getHeaderRowKeys_(spreadsheet, sheetName, headerRow);
  var data = readData_(spreadsheet, sheetName, keys, startRow);
  
  // Filter for matching terms.
  data = data.filter(function(entry) {
    var matches = true;
    for (var k in keys) {
      var key = keys[k].replace(/\s+/g, '_');
      var searchTerm = request.parameters[key];
      // Use the string form of the value since params are strings by default
      if (searchTerm != undefined)
        matches = matches && ("" + entry[key] == searchTerm);
    }
    // Matches is true iff all params are undefined or all values for keys match.
    return matches;
  });
  
  // Write and return the response.
  var response = JSON.stringify({ records: data });
  var output = ContentService.createTextOutput();
  if (callback == undefined) {
    // Serve as JSON
    output.setContent(response).setMimeType(ContentService.MimeType.JSON);
  } else {
    // Serve as JSONP
    output.setContent(callback + "(" + response + ")")
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
  }
  return output;
}

/**
 * Get a row in a spreadsheet as an Object, using the values in the header row as
 * keys and the corresponding row values as the values.
 * 
 * @param spreadsheet Spreadsheet object housing the sheet and header
 * @param sheetName Name of the specific sheet in the spreadsheet with the data
 * @param properties Optional array of keys to use for the row values. Default is the first row.
 * @param startRowNum Optional top row number of the rows to parse. The default is
 * the second row (i.e., below the header).
 */
function readData_(spreadsheet, sheetName, properties, startRowNum) {
  if (typeof properties == "undefined") {
    properties = getHeaderRowKeys_(spreadsheet, sheetName);
  }
  
  var rows = getDataRows_(spreadsheet, sheetName, startRowNum);
  var data = [];
  for (var r = 0, l = rows.length; r < l; r++) {
    var row = rows[r];
    var record = {};
    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    data.push(record);
  }
  return data;
}

/**
 * Parse spreadsheet data as an array of Javascript Objects.
 *
 * @param spreadsheet Spreadsheet object with the data to get
 * @param sheetName Name of the specific sheet in the spreadsheet with the data
 * @param startRowNum Optional top row number of the rows to parse. The default is
 * the second row (i.e., below the header).
 */
function getDataRows_(spreadsheet, sheetName, startRowNum) {
  if (typeof startRowNum == "undefined") startRowNum = 2;
  
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet.getRange(startRowNum, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
}

/**
 * Return the array of keys used in the header, replacing whitespace with underscores.
 * 
 * @param spreadsheet Spreadsheet object housing the sheet and header
 * @param sheetName Name of the specific sheet in the spreadsheet whose header values to get
 * @param rowNum Optional exact row number of the header. Default is the first row.
 */
function getHeaderRowKeys_(spreadsheet, sheetName, rowNum) {
  if (typeof rowNum == "undefined") rowNum = 1;
  return getHeaderRow_(spreadsheet, sheetName, rowNum).map(function(value) { 
    return value.replace(/\s+/g, '_');
  });
}

/**
 * Get the values in the header row of the given sheet in a spreadsheet
 * 
 * @param spreadsheet Spreadsheet object housing the sheet and header
 * @param sheetName Name of the specific sheet in the spreadsheet whose header values to get
 * @param rowNum Exact row number of the header.
 */
function getHeaderRow_(spreadsheet, sheetName, rowNum) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet.getRange(rowNum, 1, 1, sheet.getLastColumn()).getValues()[0];
}


/********************************** TESTS **********************************/


/**
 * Basic test logs a request and response. Use View -> Logs to check if it's
 * correct.
 * (In the future maybe actually check equality to expected output.)
 * @param request The HTTP request to test
 */
function runTest_(request) {
  Logger.log(request);
  Logger.log(doGet(request).getContent().toString());
}

/**
 * Test the original functionality of returning all objects
 * in the spreadsheet in JSON.
 */
function test1() {
  runTest_({
      parameters : {
        id : "0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE",
        sheet : "Summary",
        header : 2,
        startRow : 3,
      }
  });
}

/**
 * Test filtering on the spreadsheet JSON for one value.
 */
function test2() {
  runTest_({
      parameters : {
        id : "0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE",
        sheet : "Summary",
        header : 2,
        Status : "Associate"
      }
  });
}

/**
 * Test stricter filtering on the spreadsheet for multiple values.
 */
function test3() {
  runTest_({
      parameters : {
        id : "0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE",
        sheet : "Summary",
        header : 2,
        startRow : 3,
        First_Name : "Greatest",
        Last_Name : "Ever"
      }
  });
}

Sir, can you help me to insert data into sheet with this script?

Hi again, sorry to have to do this again - would you please mind adapting the blog post URL - it's changed again, and is now:

https://qmacro.org/2013/10/04/sheetasjson-google-spreadsheet-data-as-json/

Thank you very much!

On line 86, why did you add getLastRow() - 3, this seems to be causing a bug where my last few rows are not part of the query, I removed on my own version.

You're right. This is causing a bug on my script too. Thanks for your comment!

@owyongsk and @danielloretti - thanks for catching that! The original spreadsheet I used this for had additional rows at the bottom that I needed to skip, and it seems like I forgot to remove that part when I posted in 2014. I have updated the gist!

@qmacro I've updated the link to your blog, sorry for the delay. I missed all the notifications and hadn't realized people were using this! 🙃

Thanks very much! :-)

You can do this in a less brittle manner and with no coding by just using https://sheet2api.com/google-sheet-to-json-api/

There's of course the pricing issue.

Time is money

Fair enough, this option can always be available for those in less rich countries.

Fantastic tool, your demo URL though at the top has the 2-end-lines truncation bug. I've tried copying the (fixed) script and setting it up on my own Google account, however, it's unclear how to set it up the way you've done, for external URL access. Can you please post instructions on this? In my case, I need the JSON feed to be accessible from my Zapier account.

follow this link for setup instruction. and just use the code from here.

https://qmacro.org/2013/10/04/sheetasjson-google-spreadsheet-data-as-json/