Instantly share code, notes, and snippets.

Embed
What would you like to do?
Read and write data to Google Sheets with a JSON API
// Post method modified from https://github.com/jamiewilson/form-to-google-sheets
// Get method modified from https://ctrlq.org/code/20005-publish-json-google-spreadsheets
var sheetName = "Sheet1";
var sheetId = "1-FuSfuJW...";
function getSheet() {
var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);
return sheet;
}
function doGet(e) {
var sheet = getSheet();
var json = sheetToJSON(sheet);
return ContentService
.createTextOutput(JSON.stringify(json))
.setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
var lock = LockService.getScriptLock();
lock.tryLock(10000);
try {
var sheet = getSheet();
var json = JSON.parse(e.postData.contents);
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow() + 1;
var newRow = headers.map(function(header) {
if (header === 'timestamp') {
return new Date();
} else if (json[header]) {
return json[header];
}
return '';
});
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON);
}
catch (error) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': error }))
.setMimeType(ContentService.MimeType.JSON);
}
finally {
lock.releaseLock();
}
}
function sheetToJSON(sheet) {
var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
var values = range.getValues();
var headings = values.shift();
var jsonArray = [];
for (var row in values) {
var json = {};
for (var col in values[row]) {
json[headings[col]] = values[row][col];
}
jsonArray.push(json);
}
return jsonArray;
}
@ludvigeriksson

This comment has been minimized.

Show comment
Hide comment
@ludvigeriksson

ludvigeriksson May 14, 2018

Setup

  1. Create a spreadsheet.
  2. Fill in your column headers on row 1. These must match the JSON objects that are sent to/from the API.
  3. Click Tools > Script editor.
  4. Paste this code. Replace spreadsheet ID and sheet name with yours.
  5. Click Publish > As web app.
  6. Select that everyone, even anonymous, should have access to the app.
  7. Note the address, that's the one you'll make requests to. It will look something like "https://script.google.com/macros/s/AKfycb.../exec".

Usage

Reading the data

Make a GET request to the address from step 7 in setup. An array of JSON objects, one for each row, will be returned.

Adding a new row

Make a POST request to the address from step 7 in setup. The body of the request should contain a JSON object representing the row to be added.

Owner

ludvigeriksson commented May 14, 2018

Setup

  1. Create a spreadsheet.
  2. Fill in your column headers on row 1. These must match the JSON objects that are sent to/from the API.
  3. Click Tools > Script editor.
  4. Paste this code. Replace spreadsheet ID and sheet name with yours.
  5. Click Publish > As web app.
  6. Select that everyone, even anonymous, should have access to the app.
  7. Note the address, that's the one you'll make requests to. It will look something like "https://script.google.com/macros/s/AKfycb.../exec".

Usage

Reading the data

Make a GET request to the address from step 7 in setup. An array of JSON objects, one for each row, will be returned.

Adding a new row

Make a POST request to the address from step 7 in setup. The body of the request should contain a JSON object representing the row to be added.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment