Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Spreadsheet JSON Queries

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

/**
* 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"
}
});
}
@qmacro

This comment has been minimized.

Copy link

@qmacro qmacro commented May 6, 2017

Hi there - nice one. Would you mind updating the URL to my blog post please - I've moved systems (WP to Ghost) and the URL has changed slightly. It's now:

http://pipetree.com/qmacro/blog/2013/10/04/sheetasjson-google-spreadsheet-data-as-json/

Thank you!
dj

@owyongsk

This comment has been minimized.

Copy link

@owyongsk owyongsk commented May 8, 2017

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.

@catherinemaldonado

This comment has been minimized.

Copy link

@catherinemaldonado catherinemaldonado commented Feb 23, 2018

@owyongsk I'm trying to make my own copy for the same reason but I'm having cross origin issues when I try to deploy my own version. What are the settings you are using to publish yours?

@sobujbd

This comment has been minimized.

Copy link

@sobujbd sobujbd commented Dec 15, 2018

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

@qmacro

This comment has been minimized.

Copy link

@qmacro qmacro commented Mar 21, 2019

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!

@danielloretti

This comment has been minimized.

Copy link

@danielloretti danielloretti commented Jul 2, 2019

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!

@ronaldsmartin

This comment has been minimized.

Copy link
Owner Author

@ronaldsmartin ronaldsmartin commented Jul 3, 2019

@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!

@ronaldsmartin

This comment has been minimized.

Copy link
Owner Author

@ronaldsmartin ronaldsmartin commented Jul 3, 2019

@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! 🙃

@qmacro

This comment has been minimized.

Copy link

@qmacro qmacro commented Jul 3, 2019

Thanks very much! :-)

@richardARPANET

This comment has been minimized.

Copy link

@richardARPANET richardARPANET commented May 26, 2020

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

@owyongsk

This comment has been minimized.

Copy link

@owyongsk owyongsk commented May 27, 2020

There's of course the pricing issue.

@richardARPANET

This comment has been minimized.

Copy link

@richardARPANET richardARPANET commented May 27, 2020

Time is money

@owyongsk

This comment has been minimized.

Copy link

@owyongsk owyongsk commented May 29, 2020

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

@memetican

This comment has been minimized.

Copy link

@memetican memetican commented Jun 28, 2020

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.

@Cyber-Hound

This comment has been minimized.

Copy link

@Cyber-Hound Cyber-Hound commented Sep 30, 2020

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/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.