REST logic script for google APPs Script

RestEngine.gs

var DOC_ID = ""; // Add spreadsheet ID
var SHEET_NAME = "Sheet1"; // Add spreadsheet name

var setDocAndSheet = function(request)
{
  if(request.parameters.docId !== undefined) DOC_ID = request.parameters.docId;
  if(request.parameters.sheetName !== undefined) SHEET_NAME = request.parameters.sheetName;
};

var requestHasId = function(request)
{
  return request.parameters.Id !== undefined;
};

var doGet = function(request){
  setDocAndSheet(request);
  
  if(requestHasId(request))
  {
    var result = handleFind(request.parameters.Id);
  }
  else
  {
    var result = handleList();
  }
  
  return JsonResponse(result);
};

var doPost = function(request){
  setDocAndSheet(request);
  var data = JsonRequest(request);
  
  if(requestHasId(request))
  {
    var result = handleUpdate(request.parameters.Id, data);
  }
  else
  {
    var result = handleInsert(data);
  }
  return JsonResponse(result);
};

function JsonResponse(data)
{
  return ContentService
          .createTextOutput(JSON.stringify(data))
          .setMimeType(ContentService.MimeType.JSON);
};

function JsonRequest(request)
{
  return JSON.parse(request.postData.contents);
}

var handleList = function()
{
  var sheet = getSheet();
  var lastRow = sheet.getLastRow();
  
  var output = [];
  for(var i = 2; i <= lastRow; i++)
  {
    output.push(handleFind(i));
  }
  return { TotalItems: lastRow, List: output };
};

var handleFind = function(id)
{
  var sheet = getSheet();
  var headers = getSheetHeaders();
  
  var output = {};
  var rowData =  sheet.getRange(id, 1, 1, headers.length).getValues();

  if(sheet.getLastRow() < id) return { result: "error: row number " + id + " not found" };
  for(i in headers)
  {
    output[headers[i]] = rowData[0][i];
  }
  
  return output;
};

var handleUpdate = function(id, data)
{
  var actual = handleFind(id);
  
  var headers = getSheetHeaders();
  
  var row = []; 
  // loop through the header columns
  for (i in headers){
    if (headers[i] == "updated_at"){ // special case if you include a 'Timestamp' column
      row.push(new Date());
    } else if(data[headers[i]] != undefined){ // else use header name to get data
      row.push(data[headers[i]]);
    }
    else
    {
      row.push(actual[headers[i]]);
    }
  }
  // more efficient to set values as [][] array than individually
  getSheet().getRange(id, 1, 1, row.length).setValues([row]);
  return handleFind(id);
};

var handleInsert = function(data)
{
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  
  try{

    var sheet = getSheet();
    var headers = getSheetHeaders();
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "created_at"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else if(data[headers[i]] != undefined) { // else use header name to get data
        row.push(data[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    getSheet().getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return {"result_data":"success", "row": nextRow};
  }
  finally{
    lock.releaseLock();
  }
};

var getSheet = function()
{
  // next set where we write the data - you could write to multiple/alternate destinations
  var doc = SpreadsheetApp.openById(DOC_ID);
  var sheet = doc.getSheetByName(SHEET_NAME);
  return sheet;
};

var getSheetHeaders = function()
{
  var sheet = getSheet();
  // we'll assume header is in row 1 but you can override with header_row in GET/POST data
  var headRow = 1;//data.header_row || 1;
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  return headers;
};