.
106 Apps Script: Google Sheet Search Record
Objective:
1. Parameter cmd for getting commands from clients.
2. Function taskManager() to handle app-specific commands.
3. Add new codes to the existing Function getRecords() :
3.1. Read two new optional parameters searchval and searchcols.
3.2. Call Function FindInArray to search for searchval in searchcols.
3.3. Set result2 as search results.
3.4. If result2 is empty,
1. Create Spreadsheet and Script.
Spreadsheet: 106appdatacrud.
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: 106appdatacrudscript.
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);
/*if param searchval & searchcols exist*/
var searchval = String(e.parameter.searchval) || '';
var searchcols = (e.parameter.searchcols) || [];
/*convert searchcols string to array*/
if (searchcols.length > 0) {
searchcols = searchcols.split(",")
};
//Logger.log(searchval); Logger.log(searchcols[0]);
var headers = Sheet.getRange(headRow, 1, 1, Sheet.getLastColumn()).getValues()[0];
//Logger.log(headers);
var colnos = [];
if (searchval.length > 0 && searchcols != [] && searchcols.length > 0) {
//Logger.log('true');
for (m in searchcols) {
//Logger.log(searchcols[m]);
for (n in headers) {
//Logger.log(headers[n]);
if (String(searchcols[m]) == String(headers[n])) {
//Logger.log(n);
colnos.push(n);
}
}
}
/*result1=all data*/
var result1 = Sheet.getRange(2, headRow, Sheet.getLastRow() - 1, Sheet.getLastColumn()).getValues();
Logger.log(result1);
/*result2=data containing searchval only*/
var result2 = findInArray(result1, colnos, searchval); /*found is array*/
Logger.log(result2);
}
//Logger.log(colnos);
var rows = []; /*default or not found values*/
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]);
}
//row['_row']=String(i+1);/*change from zero-based to one-based and add the headRow*/
/*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
}
};
Logger.log(handleResponse(e));
} /*testgetRecords()*/
function testGetRecordsInColumns() {
var e = {
parameter: {
cmd: "getadmins",
searchval: "nota",
searchcols: "name,gmail"
}
};
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 testAddRecord() {
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 */
/* if withrowno is 1 then return row number for each row */
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) { /*concat cols*/
//Logger.log(array[rowno]);
var strtest = JSON.stringify(array[rowno]);
var n = strtest.search(searchvalue);
if (n > 0) {
/*add rowno column to the front array*/
if (withrowno == 1) {
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) {
/*add rowno column to the front array*/
if (withrowno == 1) {
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, 1); /*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);
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]]);
}
}
}
Logger.log(row);
Sheet.getRange(rownum, 1, 1, row.length).setValues([row]);
/* select the new record by tid and return it with row num */
if (tid>0){
e.parameter.cmd = "getadmins";
e.parameter.tid = String(tid);
Logger.log(e);
return getRecords(Sheet, e);
}
else{
return [];
}
} catch (e) {
// if error return this
return 0
}
}
/* test set record */
function testSetRecord() {
var e = {
parameter: {
cmd: "setadmin",
tid:123,
name: "vivi",
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