Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Friday, April 14, 2017

104 Apps Script: Google Sheet Edit Record


.
104 Apps Script: Google Sheet Edit Record

Objective:

1. Parameter cmd for getting commands from clients.
2. Function taskManager() to handle app-specific commands.
3. Function setRecord() to perform edit record operation.
3.1. Parameter SHT for sheet name.
3.2. Parameter tid, timestamp, name, gmail for selected record.
3.3. Return record with rownum by calling getRecords by tid.

1. Create Spreadsheet and Script.

Spreadsheet: 104appdatacrud.
Dummy Data:
A
B
C
D
1
tid
timestamp
name
gmail
2
123
notarazi
notarazi@gmail.com
3
456
notaraza
notaraza@gmail.com
4
789
notarazu
notarazu@gmail.com
5
6
7
Script: 104appdatacrudscript.

2. Add codes.

/*script id*/
var SCPID=ScriptApp.getScriptId();
/*active spreadsheet */
var SST=SpreadsheetApp.getActiveSpreadsheet();
/*active spreadsheet id*/
var SSTID=SpreadsheetApp.getActiveSpreadsheet().getId();
/*OR if you are writing a standalone script*/
/*var SSTID=1FhDxI7j0PFtQYF0gCsiAQauQ1QZ-nDTWNPydpQPD3GE*/
/* web request listeners */
/* pass request to handleResponse */
function doGet(e) {return handleResponse(e);}
function doPost(e) {return handleResponse(e);}
/* handle action request */
function handleResponse(e) {
 var lock = LockService.getPublicLock();
 lock.waitLock(30000); // wait 30 seconds before conceding defeat.
  try {
    var cmd = e.parameter.cmd;
    var output=[];
    if (cmd == "app") {
      output = taskManager("app",e);
    }else if (cmd == "getadmins") {
      output = taskManager("getadmins",e);
    }else if (cmd == "addadmin") {
      output = taskManager("addadmin",e);
    }else if (cmd == "setadmin") {
      output = taskManager("setadmin",e);
    }    
       
   
   
    return ContentService.createTextOutput(JSON.stringify({"result": "success","data": output})).setMimeType(ContentService.MimeType.JSON);
    //return output
  }
 catch (e) {/*if error return this*/return ContentService.createTextOutput(JSON.stringify({"result": "error","error": e})).setMimeType(ContentService.MimeType.JSON);}
 finally { /*release lock*/ lock.releaseLock();}
}
/*taskManager*/
function taskManager(cmd,e){
  switch (cmd){
    case "app":
      output=[];
      return output;
      break;
    case "getadmins":
      var SST=SpreadsheetApp.openById(SSTID);
      var SHT = SST.getSheetByName("admins");
      output=getRecords(SHT,e);
      return output;
      break;  
    case "addadmin":
      var SST=SpreadsheetApp.openById(SSTID);
      var SHT = SST.getSheetByName("admins");
      output=addRecord(SHT,e);
      return output;
      break;    
    case "setadmin":
      var SST=SpreadsheetApp.openById(SSTID);
      var SHT = SST.getSheetByName("admins");
      output=setRecord(SHT,e);
      return output;
      break;         
  }/*switch*/
}/*taskManager*/
/*CRUD Manager*/
function getRecords(Sheet,e){
  try {  
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = parseInt(e.parameter.header_row) || 1;
    Logger.log(headRow);
    var tid =parseInt(e.parameter.tid) || 0;
    Logger.log(tid);
    var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var dataRows = Sheet.getLastRow()-headRow;
    var rows = []; /*default or not found values*/    
    if (dataRows != undefined && dataRows>0) {    
      var values = Sheet.getRange(headRow+1, 1, dataRows, Sheet.getLastColumn()).getValues();
      Logger.log(values.length);
      for (var i = 0; i < values.length ; i++) {
        var row = {};
        row['_row']=String(i+1);/*change from zero-based to one-based and add the headRow*/    
        for (j in headers) {
          row[headers[j]] = String(values[i][j]);
        }
        /*if tid>0 and matched then push single row to rows and break loop*/
        if (tid>0){
          if (row.tid===String(tid) ){
          rows=[];
          rows.push(row);
          break;
          }
        }else{
          rows.push(row);
        }
    }
  }
  Logger.log(rows);  
  return rows;
} catch (e) {
  return e;
  }
}/*getRecords()*/
function testGetRecords(){
  var e={parameter:{cmd:"getadmins",header_row:4}};
  //var e={parameter:{cmd:"getadmins",header_row:4,tid:123}};
  Logger.log(handleResponse(e));
}/*testgetRecords()*/
function addRecord(Sheet, e) {
  try {
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data    
    var headRow = e.parameter.header_row || 1;
    var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var dataRowNew = Sheet.getLastRow() + 1; // get next row
    var row=[];
    var d = new Date();
    var t = d.getTime();
    // loop through the header columns
    for (header in headers) {
      if (headers[header] == "tid") { // special case if you include a 'timestamp' column
        row.push(t);
        } else if (headers[header] == "timestamp") { // special case if you include a 'timestamp' column
          row.push(d);
          } else { // else use header name to get data
            row.push(e.parameter[headers[header]]);
            }
      }
    // more efficient to set values as [][] array than individually
    Sheet.getRange(dataRowNew, 1, 1, row.length).setValues([row]);
    /* select the new record by tid and return it with row num */
    e.parameter.cmd="getadmins";
    e.parameter.tid=String(t);
    Logger.log(e);
    return getRecords(Sheet,e);
    } catch (e) {
      // if error return this
      return 0
      }
}
function testAddRecords(){
  var e={parameter:{cmd:"addadmin",name:"aba",gmail:"aba@gmail.com",tid:"aba123"}};
  Logger.log(handleResponse(e));
}/*testAddRecord()*/
/* findInArray(array,colno,searchvalue){} */
/* find a string in array row and columns */
/* if colnos is null then concat all cols */
function findInArray(array,colnos,searchvalue){
  output=[];
  for (var rowno in array){
      if (colnos==null){ /*concat cols*/
        //Logger.log(array[rowno]);
        var strtest=JSON.stringify(array[rowno]);
        var n = strtest.search(searchvalue);
        if (n>=0){
          array[rowno].unshift(rowno);
          output.push(array[rowno]);
        }
      }/*if (colnos==null)*/
        else{
          var strtest='';
          for(var colno in colnos){
            strtest=strtest.concat((strtest==''?'':','),array[rowno][colnos[colno]]);
          }
          //Logger.log(strtest);          
          var n = strtest.search(searchvalue);
          if (n>=0){
           array[rowno].unshift(rowno);
           output.push(array[rowno]);
          }/*if*/    
        }/*else*/
  }/*row*/
  return output;
}/*function findInArray*/
function testFindInArray(){
  var array=[['dadu','0','badi'],['didi','1','bada'],['dida','2','bidi'],['didu','3','bidu']];
  //output=findInArray(array,null,"da");
  //Logger.log(output);
  //output=findInArray(array,[2],"da");
  //Logger.log(output);
  output=findInArray(array,[0,2],"da");
  Logger.log(output);  
}
function setRecord(Sheet, e) {
  try {
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data    
    var headRow = parseInt(e.parameter.header_row) || 1;
    Logger.log(headRow);
    var tid =parseInt(e.parameter.tid) || 0;
    Logger.log(tid);
    var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
    var row=[];
    var values =Sheet.getRange(2, 1, Sheet.getLastRow()-1, 1).getValues();
    var found=findInArray(values,[0],tid);/*found is array*/
    if (found.length>0){
      var rownum=Number(found[0][0]);/*pickup the first item of the first row ie the array row num*/
      rownum+=2;/*change from zero-based to one-based and add the headRow*/
    }
    Logger.log(tid);
    Logger.log(rownum);
    if (rownum != undefined) {
    var d = new Date();
    // loop through the header columns
    for (i in headers) {
      if (headers[i] == "tid") { // special case if you include a 'tid' column
        row.push(tid);
        } else if (headers[i] == "timestamp") { // special case if you include a 'timestamp' column
          row.push(d);
          } else { // else use header name to get data
            row.push(e.parameter[headers[i]]);
            }
      }
  }
    Sheet.getRange(rownum, 1, 1, row.length).setValues([row]);
    /* select the new record by tid and return it with row num */
    e.parameter.cmd="getadmins";
    e.parameter.tid=String(tid);
    Logger.log(e);
    return getRecords(Sheet,e);    
    } catch (e) {
      // if error return this
      return 0
      }
}
/* test set record */
function testSetRecord(){
  var e={parameter:{cmd:"setadmin",tid:"788",name:"vovi",gmail:"vovoi@gmail.com"}};
  Logger.log(handleResponse(e));  
}

3. Test

Test URL:
Test Platform:

.

No comments:

Post a Comment