//CRUD Web App for Google Sheets and AppInventor

function doGet(e){
  
  return CRUDHandler(e);
}

function doPost(e){
  
  return CRUDHandler(e);
}


function CRUDHandler(e) { 
  
  //lock sheet to prevent concurrent changes  
  var lock = LockService.getPublicLock();
  lock.waitLock(20000);
  
  
  //CREATE NEW RECORD  
  if (e.parameter.func == "Create") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues();
    var newdata = [[]];
      for (var i=0;i<headers[0].length;i++) {
        var par = eval("e.parameter." + headers[0][i]);
        newdata[0].push(par);
        }
    newdata[0].shift();
    newdata[0].splice(0, 0, "");
    sh.appendRow(newdata[0]);
    return ContentService.createTextOutput("Success, requested action '" + e.parameter.func +"' completed");
  
    
  //READ ALL RECORDS
  } else if ( e.parameter.func == "ReadAll") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0]; 
    var rg = sh.getDataRange().getValues(); 
    var outString = '';
      for(var row=0 ; row<rg.length ; ++row){
        outString += rg[row].join(',') + '\n';  
      }
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
 
    
  //READ SINGLE RECORD
  }else if ( e.parameter.func == "ReadRecord") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    outString += rg[parseInt(e.parameter.id)].join(',');
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
    
    
  //READ QUERY
  }else if (e.parameter.func == "ReadQuery") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getName() +"!"+ sh.getDataRange().getA1Notation();
    var SQL = e.parameter.sql;//"select * where B starts with 'M'";
    var Query = '=QUERY('+rg+',\"'+SQL+'\")'
    var ts = ss.insertSheet();
    var pushQuery = ts.getRange(1, 1).setFormula(Query);
    var pullResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    //return pullResult;
    var outString = '';
    for(var row=0 ; row<pullResult.length ; ++row){
        outString += pullResult[row].join(',') + '\n';  
      }
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  
  
  //UPDATE SINGLE RECORD
  } else if (e.parameter.func == "Update") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues();
    var data = [[]];
      for (var i=0;i<headers[0].length;i++) {
        var par = eval("e.parameter." + headers[0][i]);
        data[0].push(par);
        }
    var start = parseInt(data[0][0]) + 1;
    var end = sh.getLastColumn() - 1;
    var range = sh.getRange(start, 2, 1, end);
    data[0].shift();
    range.setValues(data);
    return ContentService.createTextOutput("Success, requested action '" + e.parameter.func +"' completed");
  
    
  //DELETE SINGLE RECORD
  } else if (e.parameter.func == "Delete") {
    var record = e.parameter.id;
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    sh.deleteRow(parseInt(record) + 1);  //makes the correct row to delete (because of header row)
    return ContentService.createTextOutput("Success, requested action '" + e.parameter.func +"' completed");
  
    
  //REPORTS ERROR IF CONTENT OF FUNC PARAMETER DOES NOT MATCH
  } else {
    return ContentService.createTextOutput("Fail, requested action '" + e.parameter.func + "' did not complete");
    }
 
  
 //release spreadsheet for editing
 lock.releaseLock();
 
}