Skip to content

Instantly share code, notes, and snippets.

@stefanobaghino
Created June 16, 2015 01:48
Show Gist options
  • Save stefanobaghino/6fd25637965a4822f240 to your computer and use it in GitHub Desktop.
Save stefanobaghino/6fd25637965a4822f240 to your computer and use it in GitHub Desktop.
Google Apps Script to turn a well-formed spreadsheet into a JSON ready to be served from Google Drive.
var SPREADSHEET_ID = '<OMISSIS>';
var SHEET_NAME = '<OMISSIS>';
var SPREADSHEET = SpreadsheetApp.openById(SPREADSHEET_ID);
var SHEET = SPREADSHEET.getSheetByName(SHEET_NAME);
var JSON_FILE_ID = '<OMISSIS>';
var JSON_FILE = DriveApp.getFileById(JSON_FILE_ID);
function decorate(object) {
this.withFunction = function (name) {
object[name] = this[name];
return object;
}
return this;
}
function andThen(f) {
return decorate(f(this)).withFunction('andThen');
}
// Gets the raw values with an handy `andThen` function defined on it for further processing
function getAllRawValuesOf(sheet) {
var rawValues = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
return decorate(rawValues).withFunction('andThen');
}
// Makes the raw data into a reasonable JSON value (basically, the first row defines the keys)
function formatThem(rawValues) {
var keys = rawValues[0];
return rawValues.slice(1).map(function (value) {
return keys.reduce(function (object, key, index) {
object[key] = value[index];
return object;
}, {});
});
}
function sheetToJson(sheet) {
return getAllRawValuesOf(sheet).andThen(formatThem).andThen(JSON.stringify);
}
function updateJson() {
JSON_FILE.setContent(sheetToJson(SHEET));
}
function installTrigger() {
ScriptApp.newTrigger('updateJson').forSpreadsheet(SPREADSHEET).onEdit().create();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment