Disclaimer.
My english sucks.
Because I WANT to use Google Sheets !!!
When I think about using Google sheets along with my application, I want a few basic things. They are adding (and possibly removing) rows and columns, reading (and possibly replacing) values in the required cells, and doing these things from my app. Yes, I want custom API for my app, with blackjack and hookers! Is it really that hard? It took me a week to find answers and compile all information, i want to share with you now.
Google made everything possible with its Google Apps Script. And before theory, let’s make some things work. I prepared a working script example with most needed functions for you.
This guide is made more like tutorial. So if you really need working solution, spend 2 hours and make next steps by hands. We need to prepare our google files, and then we will connect our Codular app.
Let’s make some Google things!
-
Create test spreadsheet. You may copy test table .
-
Find what is spreadsheet ID.
-
Go to Drive > New > More > Google Apps Script
Now you see App Script editor.
-
Change name of your project.
-
Change default function. Now we will use my example script. Copy everything from here and paste to script editor (we will learn how it works later).
-
Paste your spreadsheet ID into function.
-
Save project > Deploy > New deployment > Gear > Web app > Set Anyone > Deploy > Authorize access
Are you scared?
Yep. Google doesn’t consider you a whole person.
-
Advanced > Go to (unsafe) >
-
Copy Web app URL
-
Done ! So we finished preparations on a Google side.
How this works? (basics)
So it’s time to understand some basics.
Lets read couple paragraphs of this: The worst manual on the Earth.
So I’ll try to translate for someone alive.
“When a script is published as a web app “(which is our case)”, the special callback functions doGet()and doPost() are invoked whenever a request is made to the script’s URL.” - So when we send Post request with some text to script’s URL, function doPost() fires and processes our text. Get request can’t carry any payload, so we are not interested.
“Instead of returning a user interface object created with the HTML service, the Content service can be used to return raw textual content. This allows you to write scripts that act as “services”, responding to GET and POST requests and serving data of various MIME types.” – Means: Usually response is automated result from some HTML service. But you can make custom text response with some Content service.
The basic echo script (it will return a string you send to it’s URL) will look like this:
function doPost(e) {return ContentService.createTextOutput(JSON.stringify(e.parameter))}
So… Text input, text output and javascript with api classes in between… Bingo!
Lets go to Codular ( or analogs ) and try to communicate with our script . The only thing we need is Web component ( labels and buttons are optional).
Lets make a recipe for our blocks!
-
So we need to set scripts URL for web component. It’s the string we obtained in “google things” step 9.
-
“Post text” is the block which makes Post request with attached text. We can attach our text directly, but there is a huge BUT. Text attached directly to “Post text” will be encoded with ASCII charset, so no non-latin characters for you.
-
Block “Build Request Data” makes a valid JSON-like string coded with “utf-8” charset. Yes! Any language is usable!
-
Block “Build Request Data” requires a list of pairs Name – value. Now we need to fill this list. First we want to invent some apiCommand with any suitable “commandName”, and some arguments with values required to execute this command. We can use any amount of arguments. The set of arguments is the only thing we need to develop.
-
Web request always have some response from server. “when Got Text” is the block that catches our script response. We set Label to “response content” only for testing. You can process “response content” string with anything you like (maybe you’ll need some custom parser - “split text at” block).
It's time. Hands ON !
Make this blocks in Kodular, start companion and push buttons !! You will see results in your sheet and in app!!
Append row to a sheet.
Text blocks you may copy to Codular:
apiCommand
appendRow
row
[“Value1”, “23:45”,"/dʒiː/",“gee”]
row2
maybe,csv,row,converted,from,list
Here “row2” appends earlier then “row”. You can change args names whatever you like. You need to set new name in Kodular and in script in respective places. Just don’t forget to save and redeploy script.
You can have any separator in csv line, just set your custom delimiter.
For multiline csv from table you can append all rows in a loop. Just change this line in script:
sheet.appendRow(array[0]);
To this :
for (var i in array) { sheet.appendRow(array[i]) };
Get values from row.
Text blocks you may copy to Codular:
apiCommand
getRow
rownumber
6
The picture is almost self-explanatory.
Script stringifies “values” array with comma separator, you can set any custom separator.
Trailing commas in result mean that there are empty cells in the row. If you want only some part of the row, then read links included in script.
Get values from column.
Text blocks you may copy to Codular:
apiCommand
getColumn
columnnumber
2
The same as get row, but slightly different stringifier algorithm.
And what happened with last value? - It’s a date/time formatted cell. So its up to you how to live with it. I don’t have a clear solution for it, yet.
Get values from range of cells.
Easiest way to define range is A1notation. For example cells of 4th column are in “D:D” range. You can experiment with it.
And the easiest way to convert array of values to string is JSON.stringify(values). The only downside, - we need custom json parser on Codular side (or some working extension). Hey! There is block “JSON Text Decode” in a web component! (Needs some experimenting).
Change cell value.
Everything is almost easy.
You can set not only a value, but setFormula(formula) too. Its up to you to experiment.
Find which cells contain some text.
I think the result is fascinating!
This part of script is a good example of how classes and methods work in apps script. And I think you are ready for in depth explanation.
Apps Sсript (not so basic)
Apps Script can work with all google products, and there is different types of scripts. So there is a HUGE amount of information in the manual. When you try to read it by yourself, you`ll understand why I consider it The worst manual on the Earth.
The script have a multilevel logic, and a manual have a plane, alphabetical order logic. Using search gives you a page with a lot of text, and you need to search again with your eyes.
So I’ll try to explain with my own words (not so accurate), but with some accurate links.
So let’s imagine that Google stores all information in a single huge data structure. (that’s not true, but convenient for understanding) If you need some small slice of this data, you need somehow to filter this data structure layer by layer. Of course you need a set of filters and some instrument which can apply them.
Let’s draw analogies.
Apps Script includes a lot of classes and methods. Every class is like a layer of data structure. And method of a class, which return smaller object(or array of objects) of some other class(layer), works exactly like filter. And our instrument is script’s function deployed in a form of web app.
So one more time about script type = web app.
The 1 level API class for Google Sheets is:
Class SpreadsheetApp it has some methods
The most interesting of it’s methods are:
SpreadsheetApp.create(name)
SpreadsheetApp. openById(id)
SpreadsheetApp. openByUrl(url)
All these methods return a class spreadsheet object. (now we filtered exactly one spreadsheet from all google spreadsheets)
The 2 level API class for Google Sheets is:
class spreadsheet
The most interesting methods on this level are:
spreadsheet.deleteSheet(sheet) (returns nothing, not a ‘filter’)
Spreadsheet.insertSheet() There are a few variations. Returns new sheet.
spreadsheet.getSheetByName(name) - returns class sheet object.
spreadsheet.getSheets() – returns array of sheets, so we can pick a sheet by number in array. ‘spreadsheet.getSheets()[0]’ - returns first sheet of a spreadsheet.
Also on this level you can manage a list of viewers and editors making some authorization.
The 3 level API class for Google Sheets is:
class sheet
So before learning it’s methods let’s check our example script.
SpreadsheetApp.openById(‘1kd…Vk’) -means : we started with SpreadsheetApp class, applied method openById(). Now the result of this string is an object of class spreadsheet. We could save it to some variable, but it’s not needed on this step. Next we applied a method getSheets(), and the string became like this.
SpreadsheetApp.openById(‘1kd…Vk’).getSheets() – on this level of filtering this string contains array of objects of class sheet. Array numeration starts with 0, so the first element is array[0]. And now we want to save our single object of class sheet to a variable with name “sheet”. And the string becomes like this:
var sheet = SpreadsheetApp.openById(‘1kd…Vk’).getSheets()[0];
As you see, spreadsheet ID and sheet’s serial number are hardcoded in this example. But if you need to work with multiple sheets, you can make variables.
So back to class sheet . This level of API contains everything you can see in browser, when open your sheet.
All methods are in alphabetical order, so you may scroll to such interesting words:
appendRow
createTextFinder
deleteColumn
deleteRow
insert – a lot of things
sort
getRange – there are a few variants. And this is the next ‘filter’-like method.
By the way, middle column indicates which class will be a result of method apply.
The 4 level API class for Google Sheets is
Class Range. From this level we can work with individual cells.
All methods are in alphabetical order, so you may scroll to such words:
createFilter
createTextFinder
get… and
set… for values, formulas and some other interesting staff.
There are a lot of others classes in Spreadsheet Service. Interesting one, we have an example for, is Class TextFinder . It’s up to you to make use of others.
Let’s examine this lines from our example script.
var textFinder = sheet.createTextFinder(data.parameter.text);
var cells = textFinder.findAll();
The core of “searchText” command is .findAll() method. It works like a filter, and returns array of class Range objects. We save this array in a variable named “cells”. But we can apply this method only on class textFinder object. Class textFinder is a special data structure which contains info about :
range in which we want to search,
the string with which we need matches,
Properties which define search process options.
var textFinder = sheet.createTextFinder(data.parameter.text); - this line creates a variable named ‘textFinder’, which contain an object of a class textFinder. We can modify options of search process applying some methods. For example, if we need the string to match the cell content completely, we need to add this line of code:
textFinder.matchEntireCell(true);
Or modify first line like this:
var textFinder = sheet.createTextFinder(data.parameter.text).matchEntireCell(true);
What else do we need for successful scripting?
You may also examine Class Utilities and it’s methods.
And of course you need the most basic knowledge of javascript (syntax, operators, loops).
So that’s all guys. Best regards.
) and for those it will be somewhat hard. But over all you have taken much pain to write your guide hats off for that 
Thanks, this helped a lot!