103 Apps Script: Google Sheet Add Record
Objective:
1. Parameter cmd for getting commands from clients.
2. Function taskManager() to handle app-specific commands.
3. Function addRecord() to perform add record operation.
3.1. Parameter SHT for sheet name.
3.2. Parameter tid, timestamp, name, gmail for new record.
3.3. Return record with rownum by calling getRecords by tid
1. Create Spreadsheet and Script.
Spreadsheet: 103appdatacrud.
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: 103appdatacrudscript.
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);
}
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;
}/*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()*/
|
3. Test
Test URL:
.
No comments:
Post a Comment