Skip to content

Instantly share code, notes, and snippets.

@qmacro
Last active Apr 29, 2019
Embed
What would you like to do?
SheetAsJSON - expose a Google spreadsheet (with a header row) as JSON - See https://qmacro.org/2013/10/04/sheetasjson-google-spreadsheet-data-as-json/ for a writeup
function doGet(request) {
var output = ContentService.createTextOutput();
var data = {};
var id = request.parameters.id;
var sheet = request.parameters.sheet;
var ss = SpreadsheetApp.openById(id);
data["records"] = readData_(ss, sheet);
var callback = request.parameters.callback;
if (callback == undefined) {
output.setContent(JSON.stringify(data));
}
else {
output.setContent(callback + "(" + JSON.stringify(data) + ")");
}
output.setMimeType(ContentService.MimeType.JSON);
return output;
}
function readData_(ss, sheetname, properties) {
if (typeof properties == "undefined") {
properties = getHeaderRow_(ss, sheetname);
properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
}
var rows = getDataRows_(ss, sheetname);
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;
}
function getDataRows_(ss, sheetname) {
var sh = ss.getSheetByName(sheetname);
return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}
function getHeaderRow_(ss, sheetname) {
var sh = ss.getSheetByName(sheetname);
return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment