Hi everyone 
,
Today I’d like to share two projects to better understand how 
Tim’s script for Google Sheets works.
Since several users have contacted me privately to integrate it into their own projects, so i decided to create a guide in the hope that it can be helpful.
I divided it into sections to make everything a bit more organized 
Add script to a Google Sheet
- Create a new Google Sheet
- Go to Extensions > Apps Script
- A new Apps Script project linked to the sheet will be created
- Paste the script
Mine is a slightly modified version of
script.txt (6.2 KB)
function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var fn = e.parameter.FN;
var sh = null;
var rg = null;
if (e.parameter.SH) {
sh = ss.getSheetByName(e.parameter.SH);
if (sh) {
rg = sh.getDataRange().getValues();
}
}
// enter row number and column number to return A1 Notation Cell reference
if ( fn == 'getCellRef' ) {
var ref = sh.getRange(e.parameter.ROW,e.parameter.COL).getA1Notation();
return ContentService.createTextOutput(ref);
}
// enter start and end row numbers and column numbers to return A1 Notation Range reference
else if ( fn == 'getRangeRef' ) {
var ref = sh.getRange(e.parameter.ROW,e.parameter.COL,e.parameter.ROW1,e.parameter.COL1).getA1Notation();
return ContentService.createTextOutput(ref);
}
// enter A1 notation reference to return cell value
else if ( fn == 'readCell' ) {
var ref = sh.getRange(e.parameter.REF).getValue();
return ContentService.createTextOutput(ref);
}
// enter row number to return all values in row as a list
else if ( fn == 'readRow' ) {
var ref = sh.getRange(parseInt(e.parameter.ROW),1,1,rg[0].length).getValues().flat();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// enter column number (A=1/B=2/etc.) to return all values in column as a list
else if ( fn == 'readCol' ) {
var ref = sh.getRange(1, parseInt(e.parameter.COL),rg.length,1).getValues().flat();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// enter A1 notation reference to return range values as a list
else if ( fn == 'readRange' ) {
var ref = sh.getRange(e.parameter.REF).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// returns all values on sheet, including headers
else if ( fn == 'readSheet' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}
// returns all sheets, including headers
else if ( fn == 'readAllSheets' ) {
const sheets = ss.getSheets();
let result = {};
sheets.forEach(sheet => {
result[sheet.getName()] = sheet.getDataRange().getValues();
});
return ContentService.createTextOutput(JSON.stringify(result));
}
// outputs results for SQL query of all data
else if ( fn == 'querySheet' ) {
var sql = e.parameter.SQL;
var hdr = e.parameter.HDR;
if ( hdr == 0 ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
} else if ( hdr == 1 ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
}
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}
// enter row number to delete that row
else if ( fn == 'deleteRow' ) {
sh.deleteRow(parseInt(e.parameter.ROW));
return ContentService.createTextOutput('Row ' + e.parameter.ROW + ' has been deleted');
}
// enter row number to delete that row
else if ( fn == 'deleteCol' ) {
sh.deleteColumn(parseInt(e.parameter.COL));
return ContentService.createTextOutput('Column ' + e.parameter.COL + ' has been deleted');
}
// enter A1 notation reference to write cell value
else if ( fn == 'writeCell' ) {
sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput('Value: ' + e.parameter.DATA + ' written to cell ' + e.parameter.REF);
}
// enter row number to write row values
else if ( fn == 'writeRow' ) {
var data = JSON.parse('[' + e.parameter.DATA + ']');
sh.getRange(e.parameter.ROW,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to row ' + e.parameter.ROW);
}
// enter row number to write row values
else if ( fn == 'writeCol' ) {
var data = JSON.parse(e.parameter.DATA)
.map(function(x) {
return [x];
});
sh.getRange(1,e.parameter.COL,data.length,1).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to column ' + e.parameter.COL);
}
// enter A1 notation reference to write cell values
else if ( fn == 'writeRange' ) {
var data = JSON.parse(e.parameter.DATA);
var ref = sh.getRange(e.parameter.REF).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to cells ' + e.parameter.REF);
}
// append row to end write row values
else if ( fn == 'appendRow' ) {
var data = JSON.parse('[' + e.parameter.DATA + ']');
var lr = sh.getLastRow();
sh.insertRowAfter(lr);
rowNum = lr + 1;
sh.getRange(rowNum,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
}
// append row with id to end write row values
else if (fn == 'appendRowWithId') {
var data = JSON.parse('[' + e.parameter.DATA + ']');
var lr = sh.getLastRow();
sh.insertRowAfter(lr);
rowNum = lr + 1;
data[0].unshift(rowNum - 1);
sh.getRange(rowNum, 1, 1, data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
}
// append column to end and write column values
else if ( fn == 'appendCol' ) {
var data = JSON.parse(e.parameter.DATA)
.map(function(x) {
return [x];
});
var lc = sh.getLastColumn();
sh.insertColumnAfter(lc);
colNum = lc + 1;
sh.getRange(1,colNum,data.length,1).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
}
// enter A1 notation reference and formula to set formula to cell
else if ( fn == 'insertFormula' ) {
var ref = sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput('Formula: ' + e.parameter.DATA + ' inserted to ' + e.parameter.REF);
}
}
- Now you just need to deploy the script
Deploy > New deployment
- Then
Select type > Web app
- Set the description, executor, and choose “Anyone” for who has access
- Done!
Deployment ID = Script ID
Important note:
When you update the code, never create a New deployment, because it will change the Script ID.
To keep the same one, always use:
Deploy > Manage deployments
Edit (pencil) >> New version >> Deploy
Integration on Kodular
To call a function of the script, you just need to create a URL in the following format
Script URL
?ID=SHEETID
?ID=1ABqZ9y3dQq-IcLNBuhpqSgETkT_UJhFKeA4HX-g81FE
&FN=FUNCTION
&FN=getCellRef
&PARAMETER=VALUE
&ROW=1&COL=2
Final URL
Now you just need to perform a GET request with this URL using the Web component, and that’s it.
And we’ll receive a response with the requested value, in this case “B1”.
Different types of responses
Not all functions return simple text, in fact, I prefer to divide them into three categories.
Text
In this section, we’ll receive responses like
“A1”
“item1”
“Row 4 has been deleted”
List response
In this section, we’ll receive responses like
["item1","item2","item3"], which we’ll convert into proper lists/list of lists using Web.JSONTextDecode.
Dictionary response
In this section, we’ll receive responses in JSON format, such as readAllSheets, which returns the sheets as dictionaries in the form "SheetName":[SheetData].
{
"Sheet1":
[
["Column A","Column B","Column C","Column D","Column E"],
["ItemA1","ItemB1","ItemC1","ItemD1","ItemE1"]
],
"Sheet2":
[
["Column A","Column B","Column C","Column D","Column E"],
["ItemA1","ItemB1","ItemC1","ItemD1","ItemE1"]
],
"Sheet3":
[
["Column A","Column B","Column C","Column D","Column E"],
["ItemA1","ItemB1","ItemC1","ItemD1","ItemE1"]
]
}
Anyway, in the script Tim kindly commented all the functions so you can easily consult them.
Functions template
Template
I created a very simple template to have a procedure for each function.
GoogleSheetsForKodular.aia (16.7 KB)
GoogleSheetsForKodular.xlsx (29.8 KB)
Each procedure
E.g. “getCellRef”
calls appScriptFunction
which receives the function name and a dictionary of parameters.
- It shows a progress dialog
- Sets the variable
functionwith the function name (this will be useful inWeb.GotTextto know which function the response refers to) - Generates the URL
- Performs the GET request
In Web.GotText I routed all the responses by setting responseContent to the formatted value so that only one notification is created with the formatted response.
From there, it’s up to you to customize and separate each function according to your needs.
readAllSheets is the only function where, based on the sheet names, I execute different procedures.
CSVTableToDictionary
If the sheet is called “dictionary”
The procedure CSVTableToDictionary, which is a modified version of CSV to dictionary allows creating a dictionary with the first column values as keys and, as values, dictionaries with "HeaderName":"CellValue".
{
"item1":{
"id":"item1",
"name":"name1",
"value":"value1",
"info":"info1"
}
}
firstIndexEachItem
This function was useful for the “list” sheet
which returned a list of lists.
The function simply takes the first index of each element.
from
[
["Items"],
["item1"],
["item2"],
["item3"],
...
]
to
[
"Items",
"item1",
"item2",
"item3",
...
]
sheetName (SH) as global variable
With a small change to Web.URL and the procedures, we can avoid specifying sheetName on every function call by creating a global variable.
GSForKodularSHVariable.aia (15.2 KB)
Error handling
Thanks to Still-learning’s input,
Google Sheets for Kodular (Web component) + Template + Test project - #2 by Still-learning
I noticed that with this script error handling can become tricky, since it used to return HTML text.
So I modified the script a bit to get something more consistent.
Additionally, every time we receive a response, it will always include the parameters used in the request
so I removed the
functionvariable, which is no longer needed
script.txt (9.1 KB)
function doGet(e) {
try {
if (!e.parameter.ID) throw {
error: "MissingParameter",
message: "Parameter 'ID' is required"
};
if (!e.parameter.FN) throw {
error: "MissingParameter",
message: "Parameter 'FN' is required"
};
var ss = SpreadsheetApp.openById(e.parameter.ID);
var fn = e.parameter.FN;
var sh = null;
var rg = null;
if (e.parameter.SH) {
sh = ss.getSheetByName(e.parameter.SH);
if (!sh) throw {
error: "SheetNotFound",
message: "Sheet '" + e.parameter.SH + "' not found"
};
rg = sh.getDataRange().getValues();
}
function validateParams(params) {
let missing = [];
params.forEach(p => {
if (e.parameter[p] === undefined) missing.push(p);
});
if (missing.length > 0) throw {
error: "MissingParameter",
message: "Missing parameters: " + missing.join(", ")
};
}
switch (fn) {
case 'getCellRef':
validateParams(['ROW','COL','SH']);
return ContentService.createTextOutput(JSON.stringify({
result: sh.getRange(e.parameter.ROW,e.parameter.COL).getA1Notation(),
SH: e.parameter.SH,
ROW: e.parameter.ROW,
COL: e.parameter.COL,
FN: e.parameter.FN
}));
case 'getRangeRef':
validateParams(['ROW','COL','ROW1','COL1','SH']);
return ContentService.createTextOutput(JSON.stringify({
result: sh.getRange(e.parameter.ROW,e.parameter.COL,e.parameter.ROW1,e.parameter.COL1).getA1Notation(),
SH: e.parameter.SH,
ROW: e.parameter.ROW,
COL: e.parameter.COL,
ROW1: e.parameter.ROW,
COL1: e.parameter.COL,
FN: e.parameter.FN
}));
case 'readCell':
validateParams(['REF','SH']);
return ContentService.createTextOutput(JSON.stringify({
result: sh.getRange(e.parameter.REF).getValue(),
SH: e.parameter.SH,
REF: e.parameter.REF,
FN: e.parameter.FN
}));
case 'readRow':
validateParams(['ROW','SH']);
return ContentService.createTextOutput(JSON.stringify({
result: sh.getRange(parseInt(e.parameter.ROW),1,1,rg[0].length).getValues().flat(),
SH: e.parameter.SH,
ROW: e.parameter.ROW,
FN: e.parameter.FN
}));
case 'readCol':
validateParams(['COL','SH']);
return ContentService.createTextOutput(JSON.stringify({
result: sh.getRange(1, parseInt(e.parameter.COL),rg.length,1).getValues().flat(),
SH: e.parameter.SH,
COL: e.parameter.COL,
FN: e.parameter.FN
}));
case 'readRange':
validateParams(['REF','SH']);
return ContentService.createTextOutput(JSON.stringify({
result: sh.getRange(e.parameter.REF).getValues(),
SH: e.parameter.SH,
REF: e.parameter.REF,
FN: e.parameter.FN
}));
case 'readSheet':
validateParams(['SH']);
return ContentService.createTextOutput(JSON.stringify({
result: rg,
SH: e.parameter.SH,
FN: e.parameter.FN
}));
case 'readAllSheets':
let result = {};
ss.getSheets().forEach(sheet => {
result[sheet.getName()] = sheet.getDataRange().getValues();
});
return ContentService.createTextOutput(JSON.stringify({
result: result,
FN: e.parameter.FN
}));
case 'querySheet':
validateParams(['SQL','HDR','SH']);
var sql = e.parameter.SQL;
var hdr = parseInt(e.parameter.HDR);
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
if (hdr === 0) rgq = rgq.replace('A1','A2');
var qry = '=query(' + rgq + ';\"' + sql + '\";' + hdr + ')';
var ts = ss.insertSheet();
ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify({
result: getResult,
SH: e.parameter.SH,
SQL: e.parameter.SQL,
HDR: e.parameter.HDR,
FN: e.parameter.FN
}));
case 'deleteRow':
validateParams(['ROW','SH']);
sh.deleteRow(parseInt(e.parameter.ROW));
return ContentService.createTextOutput(JSON.stringify({
result: 'Row ' + e.parameter.ROW + ' deleted',
SH: e.parameter.SH,
ROW: e.parameter.ROW,
FN: e.parameter.FN
}));
case 'deleteCol':
validateParams(['COL','SH']);
sh.deleteColumn(parseInt(e.parameter.COL));
return ContentService.createTextOutput(JSON.stringify({
result: 'Column ' + e.parameter.COL + ' deleted',
SH: e.parameter.SH,
COL: e.parameter.COL,
FN: e.parameter.FN
}));
case 'writeCell':
validateParams(['REF','DATA','SH']);
sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput(JSON.stringify({
result: 'Value written', value: e.parameter.DATA, cell: e.parameter.REF,
SH: e.parameter.SH,
REF: e.parameter.REF,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'writeRow':
validateParams(['ROW','DATA','SH']);
var data = JSON.parse('[' + e.parameter.DATA + ']');
sh.getRange(e.parameter.ROW,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput(JSON.stringify({
result: 'Row written', values: data, row: e.parameter.ROW,
SH: e.parameter.SH,
ROW: e.parameter.ROW,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'writeCol':
validateParams(['COL','DATA','SH']);
var data = JSON.parse(e.parameter.DATA).map(x => [x]);
sh.getRange(1,e.parameter.COL,data.length,1).setValues(data);
return ContentService.createTextOutput(JSON.stringify({
result: 'Column written', values: data, col: e.parameter.COL,
SH: e.parameter.SH,
COL: e.parameter.COL,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'writeRange':
validateParams(['REF','DATA','SH']);
var data = JSON.parse(e.parameter.DATA);
sh.getRange(e.parameter.REF).setValues(data);
return ContentService.createTextOutput(JSON.stringify({
result: 'Range written', values: data, ref: e.parameter.REF,
SH: e.parameter.SH,
REF: e.parameter.REF,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'appendRow':
validateParams(['DATA','SH']);
var data = JSON.parse('[' + e.parameter.DATA + ']');
var lr = sh.getLastRow();
sh.insertRowAfter(lr);
sh.getRange(lr+1,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput(JSON.stringify({
result: 'Row appended', values: data,
SH: e.parameter.SH,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'appendRowWithId':
validateParams(['DATA','SH']);
var data = JSON.parse('[' + e.parameter.DATA + ']');
var lr = sh.getLastRow();
sh.insertRowAfter(lr);
data[0].unshift(lr);
sh.getRange(lr+1,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput(JSON.stringify({
result: 'Row appended with ID', values: data,
SH: e.parameter.SH,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'appendCol':
validateParams(['DATA','SH']);
var data = JSON.parse(e.parameter.DATA).map(x => [x]);
var lc = sh.getLastColumn();
sh.insertColumnAfter(lc);
sh.getRange(1,lc+1,data.length,1).setValues(data);
return ContentService.createTextOutput(JSON.stringify({
result: 'Column appended', values: data,
SH: e.parameter.SH,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
case 'insertFormula':
validateParams(['REF','DATA','SH']);
sh.getRange(e.parameter.REF).setFormula(e.parameter.DATA);
return ContentService.createTextOutput(JSON.stringify({
result: 'Formula inserted', formula: e.parameter.DATA, ref: e.parameter.REF,
SH: e.parameter.SH,
REF: e.parameter.REF,
DATA: e.parameter.DATA,
FN: e.parameter.FN
}));
default:
throw {
error: "UnknownFunction",
message: "Function '" + fn + "' not recognized"
};
}
} catch(err) {
return ContentService.createTextOutput(JSON.stringify({
error: err.error || "ExecutionError",
message: err.message || err.toString(),
ID: e.parameter.ID,
SH: e.parameter.SH,
FN: e.parameter.FN
}));
}
}
Now, for each request, two possible dictionaries are returned
- An
errordictionary, always containing anerrorkey
{
"error":"SheetNotFound",
"message":"Sheet 'Sheet2' not found",
"ID":"1ABqZ9y3dQq-IcLNBuhpqSgETkT_UJhFKeA4HX-g81FE",
"SH":"Sheet2",
"FN":"getCellRef"
}
- A
resultdictionary in case of success, always containing aresultkey
{
"result":"B1",
"SH":"Sheet1",
"ROW":"1",
"COL":"2",
"FN":"getCellRef"
}
I also updated the Web.GotText to make it compatible with the new script, and this is the result.
GSForKodularUpdatedScript.aia (19.9 KB)
Test project
What better way to learn than seeing in real time how the URL changes based on what we set?!
Don’t use this project as a base to create an app
!
It’s unnecessarily complex, entirely managed through Dynamic Components (which makes it even harder to understand) and it was designed to be used in both APK and Companion only to study how the URL is generated.
GSForKodularTestProject.aia (77.3 KB)
GoogleSheetsForKodular.xlsx (29.8 KB)
-
Set Script URL and SpreadsheetID
-
Select/type the function
-
Set all the parameters
-
Send the request
-
As soon as you receive the response, you’ll see it in the “Response content” textbox
Here, ScriptURL, SpreadsheetID, SheetName and Function are set in the urlParts dictionary
I hope I haven’t missed anything, and as always, if you need anything, don’t hesitate to ask.
oding!