Skip to content

Instantly share code, notes, and snippets.

@chrsstrm
Last active June 9, 2023 10:38
Show Gist options
  • Save chrsstrm/3fb0ce6820acecf62c5490d220d4ec5f to your computer and use it in GitHub Desktop.
Save chrsstrm/3fb0ce6820acecf62c5490d220d4ec5f to your computer and use it in GitHub Desktop.
Treat a Google Sheet like a JSON API
/**
* create a Google Sheet then go to Tools > Script Editor
* Paste this code into the editor. Save.
* Publish > Deploy as Web App
* Set new version, publish as me, who has access - anyone, even anon.
* GET to the URL, add on end ?sheet=[sheet name]
* Sheet name is the sheet name, manage appropriately (no spaces or symbols to keep it simple)
* Request returns JSON representation of the sheet.
*/
/**
* doGet is an installed trigger that responds to a GET request
* to the deployed app URL (Publish -> Deploy as web app -> execute as
* Me and who has access is Everyone, even anon).
* We are going to use the ?sheet=[sheet name] query params on the end of
* the script URL to tell the script which sheet to return (it only returns
* one sheet per request, not all of them at once).
* Response is JSON structured as an array of items under the "items"
* key.
*/
function doGet(e) {
return ContentService
.createTextOutput(JSON.stringify(getData(e.parameter.sheet)))
.setMimeType(ContentService.MimeType.JSON);
}
/**
* This function takes the requested sheet and gets the values
* in the sheet as an array of arrays with the first row being
* the header. The entire array is then sent to a helper which
* will convert the array to an appropriate object.
*/
function getData(sheet) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheet);
var range = (sheet) ? sheet.getDataRange() : null;
var values = (sheet) ? range.getValues() : null;
return (sheet) ? objectify(values) : null;
}
/**
* Our sheet data is retrieved as an array of arrays, but
* what we really want to send back to the client is JSON.
* This helper takes the array and converts it to an object
* by using arr[0] as the keys and each subsequent arr item
* as the values. We'll deal with converting to JSON when
* we actually send the object as a GET response later.
* Note that this func will err if the arr sent has a length
* of less than 2 - make sure to do error checking on your
* arr before you call this func.
*/
function objectify(arr) {
if (arr.length > 1) {
var header = arr.shift();
var arrItems = [];
arr.forEach(function(item, itemIndex) {
var obj = {};
obj['index'] = itemIndex;
item.forEach(function(i, index) {
obj[header[index]] = i;
});
arrItems.push(obj);
});
return {items: arrItems};
} else {
return null;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment