.
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