Google SpreadSheetの表をjson形式で返すGAS

spreadSheetToJsonString.js

var BOOK_ID = "{book_id}";

function doGet(e) {
  var bookId = BOOK_ID;
  
  var rootJson = new Object();
  var sheetSize = SpreadsheetApp.openById(bookId).getSheets().length;
  for (var i = 0; i < sheetSize; i++) {
    var sheet = SpreadsheetApp.openById(bookId).getSheets()[i];
    var json = convertSheet2JsonText(sheet);
    //Logger.log(json);
    rootJson[sheet.getSheetName()] = json;
  }
  Logger.log(rootJson);
  return ContentService.createTextOutput(JSON.stringify(rootJson)).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<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}