102 Apps Script: Google Sheet Select Records

.

102 Apps Script: Google Sheet Select Records

Objective:

1. Parameter cmd for getting commands from clients.

2. Function taskManager() to handle app-specific commands.

3. Function selectRecords() to perform select records operation.

3.1. Parameter SHT for sheet name.

3.2. Parameter header_row for header row (contains header for the data)

3.3. Parameter tid for any request for specific row.

1. Create Spreadsheet and Script.

Spreadsheet: 102appdatacrud.

Dummy Data:

	A	B	C	D
1
2
3
4	tid	timestamp	name	gmail
5	123		notarazi	notarazi@gmail.com
6	456		notaraza	notaraza@gmail.com
7	789		notarazu	notarazu@gmail.com

Script: 102appdatacrudscript.

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);    }            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=selectRecords(SHT,e);      return output;      break;  }/*switch*/ }/*taskManager*/ /*CRUD Manager*/function selectRecords(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;  } }/*selectRecords()*/ function testSelectRecords(){  var e={parameter:{cmd:"getadmins",header_row:4}};  //var e={parameter:{cmd:"getadmins",header_row:4,tid:123}};  Logger.log(handleResponse(e)); }/*testSelectRecords()*/

3. Test

URL: https://script.google.com/macros/s/AKfycbx9e2lXNmpffG7J_jk_iB5D2RU6rMg_i8l_Au7QYXkvIbATLTT5/exec 

No	Parameter	Output
1	none	{"result":"success","data":[]}
2	?cmd=app	{"result":"success","data":[]}
3	?cmd=getadmins	{"result":"success","data":[{"_row":"1","":""},{"_row":"2","":""},{"_row":"3","":"gmail"},{"_row":"4","":"notarazi@gmail.com"},{"_row":"5","":"notaraza@gmail.com"},{"_row":"6","":"notarazu@gmail.com"}]} Notice that there are three invalid data objects
4	?cmd=getadmins&row_header=4	{"result":"success","data":[{"_row":"1","tid":"123","timestamp":"","name":"notarazi","gmail":"notarazi@gmail.com"},{"_row":"2","tid":"456","timestamp":"","name":"notaraza","gmail":"notaraza@gmail.com"},{"_row":"3","tid":"789","timestamp":"","name":"notarazu","gmail":"notarazu@gmail.com"}]} Notice that there are three valid data objects
5	?cmd=getadmins&header_row=4&tid=123	{"result":"success","data":[{"_row":"2","tid":"456","timestamp":"","name":"notaraza","gmail":"notaraza@gmail.com"}]}

You can use online json editor eg http://www.jsoneditoronline.org/ to check JSON data validity.

Output for test no.4

.