스프레드시트를 Db로 사용하기

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e) {
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);
  
  var rawData;
  var columns;
  var limitStart;
  var limitSize;
  var columnArr= [];
  var conditionKey;
  var conditionValue;

  try {
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheetName = e.parameter["table"];
    var sheetInitial = doc.getSheetByName(sheetName);

    var columns = e.parameter["columns"];
    if(columns){
      columnArr = columns.split("/");
    }

    var condition = e.parameter["condition"];
    if(condition){
      var condtionArr =condition.split("-");
      conditionKey = condtionArr[0];
      conditionValue = condtionArr[1];
    }

    var limitSize = e.parameter["limitSize"];
    if(limitSize===undefined){
      limitSize = 0;
    }

    var limitStart = e.parameter["limitStart"];
    if(limitStart===undefined){
      limitStart = 0;
    }

    var headers = sheetInitial.getRange(1, 1, 1, sheetInitial.getLastColumn()).getValues()[0];
    var rawData = sheetInitial.getRange(2, 1, sheetInitial.getLastRow(), sheetInitial.getLastColumn()).getValues();
    
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({"ok": false, "error": error["message"]}))
      .setMimeType(ContentService.MimeType.JSON);
  }
  var data = [];
  var headerLength = headers.length;
  var rowLength = rawData.length;
    
  if (rawData) {
    for (var r=0; r < rowLength; r++) {
      if((r >= limitStart && r < parseInt(limitStart) + parseInt(limitSize)) || limitSize == 0 ){
        if (rawData[r][0] === "") {
          continue;
        }
        var isConditionValid = true;
        if(conditionKey&&conditionValue){
          for(var i=0; i< headerLength; i++){
            if(headers[i]==conditionKey){
              if(rawData[r][i] == conditionValue){
                isConditionValid = true;
              }else{
                isConditionValid = false;
              }
            }
          }
        }
        var row = {};
        if(isConditionValid){
          for (var i=0; i< headerLength; i++) {
            if(columnArr.length == 0){
              row[headers[i]] = rawData[r][i];
            }else{
              for(var c =0; c < columnArr.length; c++ ){
                if(columnArr[c] == headers[i]){
                  row[headers[i]] = rawData[r][i];
                }
              }
            }
          }
          data.push(row);
        }
      }
    }
  }
  
  return ContentService
    .createTextOutput(JSON.stringify({"ok": true, "data": data}))
    .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e){
  //    do nothing
}

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  SCRIPT_PROP.setProperty("key", doc.getId());
}