Google Sheet to JSON Web App

Google Sheet to JSON Web App

///////////////////////////////////////////////////////////////////////////////
//
//  Google Sheets to JSON Web App by Nathaniel
//
//  Permission is hereby granted, free of charge, to any person obtaining a copy
//  of this software and associated documentation files (the "Software"), to deal
//  in the Software without restriction, including without limitation the rights
//  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the Software is
//  furnished to do so, subject to the following conditions:
//
//  The above copyright notice and this permission notice shall be included in
//  all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//  THE SOFTWARE.
//
/////////////////////////////////////////////////////////////////////////////////

const VALID_SHEETS = ["SomeSheet", "Another Sheet", "etc."] // specify sheets to be json-ified
const ACCESS_KEY = "super-secret-password" // specify a strong password
const SPREADSHEET_ID = "SPREADSHEET ID GOES HERE"

function doGet(e) {
  var key = e.parameter.key;
  if(!key || !(key == ACCESS_KEY))
  {
    return ContentService.createTextOutput("no access")
  }
  
  var configSpreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID)
  var allSheets = configSpreadsheet.getSheets()
  var validSheets = []
  
  for(var i = 0; i < allSheets.length; i++)
  {
    if(!VALID_SHEETS.includes(allSheets[i].getSheetName()))
    {
      continue
    }
    
    validSheets.push(buildSheetObj(allSheets[i]))
  }
  
  textOutput = ContentService.createTextOutput(JSON.stringify(validSheets))
  return textOutput
}

function buildSheetObj(sheet)
{
  var sheetObj = []
  var columnCount = sheet.getLastColumn()
  var rowCount = sheet.getLastRow()
  
  var firstRowValues = sheet.getRange(1, 1, 1, columnCount).getValues()
  var keys = firstRowValues[0].toString().split(",")
  
  rowArray = []
  for (i = 1; i < rowCount; i++)
  {
    var rowValues = sheet.getRange(i + 1, 1, 1, keys.length).getDisplayValues()[0]
    rowArray.push(rowValues)
  }
  
  for(i = 0; i < rowArray.length; i++)
  {
    rowObj = {}
    for(j = 0; j < keys.length; j++)
    {
      rowObj[keys[j].toString()] = rowArray[i][j]
    }
    sheetObj.push(rowObj)
  }
  
  return sheetObj
}