Instantly share code, notes, and snippets.

What would you like to do?
Read and write data to Google Sheets with a JSON API
// Post method modified from
// Get method modified from
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
function doPost(e) {
var lock = LockService.getScriptLock();
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 = {
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 }))
catch (error) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': error }))
finally {
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];
return jsonArray;

This comment has been minimized.

Copy link

ludvigeriksson commented May 14, 2018


  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 "".


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