Read and write data to Google Sheets with a JSON API

google-sheets-json.js

// 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;
}

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.