.
105 Apps Script: Google Sheet Delete Record
Objective:
1. Parameter cmd for getting commands from clients.
2. Function taskManager() to handle app-specific commands.
3. Function delRecord() to perform delete record operation.
3.1. Parameter SHT for sheet name.
3.2. Parameter tid for selected record.
3.3. Return tid=0.
1. Create Spreadsheet and Script.
Spreadsheet: 105appdatacrud.
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
|
Sheet name: admins
Script: 105appdatacrudscript.
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);
}else if (cmd == "deladmin") {
output = taskManager("deladmin",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;
case "deladmin":
var SST=SpreadsheetApp.openById(SSTID);
var SHT = SST.getSheetByName("admins");
output=delRecord(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;
//Logger.log(headRow);
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+=headRow+1;/*add the headRow and change from zero-based to one-based*/
}
//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));
}
/* delete record */
function delRecord(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 values =Sheet.getRange(2, 1, Sheet.getLastRow()-1, 1).getValues();
//Logger.log(values);
var found=findInArray(values,[0],tid);/*found is array*/
//Logger.log(found);
if (found.length>0){
var rownum=Number(found[0][0]);/*pickup the first item of the first row ie the array row num*/
rownum+=headRow+1;/*add the headRow and change from zero-based to one-based*/
Logger.log(rownum);
}
//Logger.log(rownum);
if (rownum != undefined) {
// delete row by rownum
Sheet.deleteRow(rownum);
}
return [{tid:0}]
} catch (e) {
// if error return this
return 0
}
}
/* test delete record */
function testDelRecord(){
var e={parameter:{cmd:"deladmin",tid:"789"}};
Logger.log(handleResponse(e));
}
|
3. Test
Test URL:
Test Platform:
No comments:
Post a Comment