107 Apps Script Google Sheet CRUD

107 Apps Script Google Sheet CRUD

.

107 Apps Script: Google Sheet CRUD

The following is a minified version of the previous tutorials:
1) Select records from Google Sheet.
2) Add new records into Google Sheet.
3) Edit records in Google Sheet.
4) Delete records in Google Sheet.
5) Search records in Google Sheet.

The codes are for learning purpose. 
They have not been extensively tested for robustness yet.

Use at your own risk :-D

var SCPID=ScriptApp.getScriptId();var SST=SpreadsheetApp.getActiveSpreadsheet();var SSTID=SpreadsheetApp.getActiveSpreadsheet().getId();function doGet(e){return handleResponse(e);} function doPost(e){return handleResponse(e);} function handleResponse(e){var lock=LockService.getPublicLock();lock.waitLock(30000);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);}else if(cmd=="deladmin"){output=taskManager("deladmin",e);} return ContentService.createTextOutput(JSON.stringify({"result":"success","data":output})).setMimeType(ContentService.MimeType.JSON);}catch(e){return ContentService.createTextOutput(JSON.stringify({"result":"error","error":e})).setMimeType(ContentService.MimeType.JSON);}finally{lock.releaseLock();}} 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;case"deladmin":var SST=SpreadsheetApp.openById(SSTID);var SHT=SST.getSheetByName("admins");output=delRecord(SHT,e);return output;break;}} function getRecords(Sheet,e){try{var headRow=parseInt(e.parameter.header_row)||1;var tid=parseInt(e.parameter.tid)||0;var searchval=String(e.parameter.searchval)||;var searchcols=(e.parameter.searchcols)||[];if(searchcols.length>0){searchcols=searchcols.split(",")};var headers=Sheet.getRange(headRow,1,1,Sheet.getLastColumn()).getValues()[0];var colnos=[];if(searchval.length>0&&searchcols!=[]&&searchcols.length>0){for(m in searchcols){for(n in headers){if(String(searchcols[m])==String(headers[n])){colnos.push(n);}}} var result1=Sheet.getRange(2,headRow,Sheet.getLastRow()-1,Sheet.getLastColumn()).getValues();Logger.log(result1);var result2=findInArray(result1,colnos,searchval);Logger.log(result2);} var rows=[];if(result2==undefined){var dataRows=Sheet.getLastRow()-headRow;if(dataRows!=undefined&&dataRows>0){result2=Sheet.getRange(headRow+1,1,dataRows,Sheet.getLastColumn()).getValues();}} Logger.log(result2.length);for(var i=0;i<result2.length;i++){var row={};for(j in headers){row[headers[j]]=String(result2[i][j]);} 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;}} function testGetRecords(){var e={parameter:{cmd:"getadmins",header_row:4}};Logger.log(handleResponse(e));} function testGetRecordsInColumns(){var e={parameter:{cmd:"getadmins",searchval:"nota",searchcols:"name,gmail"}};Logger.log(handleResponse(e));} function addRecord(Sheet,e){try{var headRow=e.parameter.header_row||1;var headers=Sheet.getRange(headRow,1,1,Sheet.getLastColumn()).getValues()[0];var dataRowNew=Sheet.getLastRow()+1;var row=[];var d=new Date();var t=d.getTime();for(header in headers){if(headers[header]=="tid"){row.push(t);}else if(headers[header]=="timestamp"){row.push(d);}else{row.push(e.parameter[headers[header]]);}} Sheet.getRange(dataRowNew,1,1,row.length).setValues([row]);e.parameter.cmd="getadmins";e.parameter.tid=String(t);return getRecords(Sheet,e);}catch(e){return 0}} function testAddRecord(){var e={parameter:{cmd:"addadmin",name:"aba",gmail:"aba@gmail.com",tid:"aba123"}};Logger.log(handleResponse(e));} function findInArray(array,colnos,searchvalue,withrowno){withrowno=withrowno||0;if(withrowno!=1){withrowno=0} Logger.log(searchvalue);output=[];for(var rowno in array){if(colnos==null){var strtest=JSON.stringify(array[rowno]);var n=strtest.search(searchvalue);if(n>0){if(withrowno==1){array[rowno].unshift(rowno);} output.push(array[rowno]);}} else{var strtest=;for(var colno in colnos){strtest=strtest.concat((strtest==?:,),array[rowno][colnos[colno]]);} var n=strtest.search(searchvalue);if(n>0){if(withrowno==1){array[rowno].unshift(rowno)};output.push(array[rowno]);}}} return output;} function testFindInArray(){var array=[[dadu,0,badi],[didi,1,bada],[dida,2,bidi],[didu,3,bidu]];output=findInArray(array,[0,2],"da");Logger.log(output);} function setRecord(Sheet,e){try{var headRow=parseInt(e.parameter.header_row)||1;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,1);Logger.log(found);if(found.length>0){var rownum=Number(found[0][0]);rownum+=headRow+1;} Logger.log(rownum);if(rownum!=undefined){var d=new Date();for(i in headers){if(headers[i]=="tid"){row.push(tid);}else if(headers[i]=="timestamp"){row.push(d);}else{row.push(e.parameter[headers[i]]);}}} Logger.log(row);Sheet.getRange(rownum,1,1,row.length).setValues([row]);if(tid>0){e.parameter.cmd="getadmins";e.parameter.tid=String(tid);Logger.log(e);return getRecords(Sheet,e);} else{return[];}}catch(e){return 0}} function testSetRecord(){var e={parameter:{cmd:"setadmin",tid:1,name:"vivi",gmail:"vovoi@gmail.com"}};Logger.log(handleResponse(e));} function delRecord(Sheet,e){try{var headRow=parseInt(e.parameter.header_row)||1;var tid=parseInt(e.parameter.tid)||0;var values=Sheet.getRange(2,1,Sheet.getLastRow()-1,1).getValues();var found=findInArray(values,[0],tid);if(found.length>0){var rownum=Number(found[0][0]);rownum+=headRow+1;Logger.log(rownum);} if(rownum!=undefined){Sheet.deleteRow(rownum);} return[{tid:0}]}catch(e){return 0}} function testDelRecord(){var e={parameter:{cmd:"deladmin",tid:"789"}};Logger.log(handleResponse(e));}

.

visit link download