Publish Google Spreadsheets as JSON with Apps Script

CodeCanyon · May 2

The previous snippet on getting Spreadsheets as JSON required you to make a sheet public and also publish as HTML before other apps can pull data as JSON from the sheet.

If you would not like to make your Google spreadsheet public, you can use Google Apps Script to create a web app that will expose the sheet data as JSON.

/* Source: https://gist.github.com/daichan4649/8877801 */
function doGet(e) {

  var sheetName = "Sheet 1";
  var sheetId   = "1234...";

  var book = SpreadsheetApp.openById(sheetId);
  var sheet = book.getSheetByName(sheetName);

  var json = convertSheet2JsonText(sheet);

  return ContentService
          .createTextOutput(JSON.stringify(json))
          .setMimeType(ContentService.MimeType.JSON);
}

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}