Motivation for apps script example
I originally create both qottle and rottler for various node projects, but realized that I had some Apps Script that could be solved with them too. One of them was polling when checking for changes in a spreadsheet from an add-on, but another was the perennial problem of conflicting rate limited APIS. Both qottle and rottler work both client and server side on apps script so they were ideal to solve them. In particular, I wanted something that could be just dropped in to existing Apps Script projects without any redesign.
apps script example problem
I use gasGit to keep all my apps script code synched to gasgit repos, even when I’m just using the IDE. Gasgit examines all my public scripts and their dependencies from time to time, and if there are any changes, it automatically pushes them to git hub. I have a couple of hundred public projects, which multiply up to over a thousand individual script files, so it’s not really something I relish doing manually.
However I kept hitting a problem with rate limits, and the usual exponential backoff solution was getting tedious to deal with because I’m using multiple APIs each with their own rate limits. In particular, I wanted something I could just drop a couple of lines of code in and avoid restructuring. Here’s a very simplified schema of the solution.
- get all the projects and their script files – rate limits on Drive API, Script management API
- detect changes and construct any support files – rate limits on Drive API
- find dependencies (libraries referenced, Advanced services referenced) – rate limits on Script management API
- push everything to github – rate limits on Drive API and github API
Drop in Solution
Using rottler, I can set up the rate limits on each API globally
Then wherever I need to access any APIS
It’s really that simple
Using an iterator
One of the great things that came with V8 are iterators. In this example, let’s take a look at these different approaches to dealing with enriching a spreadsheet with data from a rate limited API. (For simplicity, I’m using fiddler for all spreadsheet access)
Set up the rottler for the openweather API
Get the input sheet data
It’s just some airport data
|Port Moresby Jacksons International Airport||-9.443380356||147.2200012||146||PG||PG-NCD||Port Moresby||POM|
|Keflavik International Airport||63.98500061||-22.60560036||171||IS||IS-2||Reykjavik||KEF|
|Prishtina International Airport||42.57279968||21.03580093||1789||KS||KS-U-A||Prishtina||PRN|
|Edmonton International Airport||53.30970001||-113.5800018||2373||CA||CA-AB||Edmonton||YEG|
|Halifax / Stanfield International Airport||44.88079834||-63.50859833||477||CA||CA-NS||Halifax||YHZ|
|Ottawa Macdonald-Cartier International Airport||45.32249832||-75.66919708||374||CA||CA-ON||Ottawa||YOW|
And we want to copy the sheet and use the openweather API to add some weather info, so it looks like this
|Port Moresby Jacksons International Airport||-9.443380356||147.2200012||146||PG||PG-NCD||Port Moresby||POM||1610038185000||scattered clouds||298.15||94|
|Keflavik International Airport||63.98500061||-22.60560036||171||IS||IS-2||Reykjavik||KEF||1610038183000||broken clouds||268.32||80|
|Prishtina International Airport||42.57279968||21.03580093||1789||KS||KS-U-A||Prishtina||PRN||1610038182000||broken clouds||276.32||87|
|Edmonton International Airport||53.30970001||-113.5800018||2373||CA||CA-AB||Edmonton||YEG||1610038183000||scattered clouds||262.65||79|
|Halifax / Stanfield International Airport||44.88079834||-63.50859833||477||CA||CA-NS||Halifax||YHZ||1610038184000||light snow||273.86||93|
Get the data
Set up the rottler for the openweather API
Create a function to enrich the airport data with the open weather API result
Create a function to query the api
Do the work
Before we get to the use of iterators, first let’s explore a couple of more traditional methods. The work is to
- loop through each row of the sheet
- decorate with data from the api about that row
- write back all the decorated data to a new sheet
- All the while, make sure we are accessing the api as efficiently as possible yet without breaking any rate limits
Method 1 – A simple loop, with manually controlled waiting
Method 2 – a simple loop, auto waiting
Method 3 – use an iterator
V8 introduced iterators to Apps Script. You actually will already be familiar with iterators if you use the DriveApp API
An iterator returns an object each time .next() is called. At a minimum that object looks like this
So the iterator needs know about what it’s iterating through, as well as how to iterate through it and keep track of where it is. Rottle knows how to create an iterator and will give you one in return for an object containing the rows to be iterated through.
So now the loop can be replaced by for ..of, which knows how to interpret the iterator return object, and the the iterator returned by rot will respect the rate limits – only returning each item when it’s safe. So now we’ve completely abstracted the ‘getting data safely’ from ‘doing something with it’.
But there’s more…
The rottler iterator also supports transformers. That means it can not only return data in a controlled way, it can also transform it.
And there’s even more
Your can use Array.from() to convert iterable objects into arrays. That means it also works with iterators – so we can turn the iterator -(which is returning rate limited transformed data from an API) back into a set of transformed rows. See if you can figure it out
This iterator is synchronous – because Apps Script is. V8 does support async/await and rottler can still work in an quasi-async way. Rottler detects which kind of iterator (sync -vs- async) is needed by the nature of the looping mechanism. An async loop uses
for await ... of
whereas a sync loop uses
for ... of
Apps script doesn’t support for await … of , even in a fake way – so that’s why we have operate in synch mode for Apps Script. That may change in the future, but for now we have to use the for … of approach.
If you are interested in what the Rottler iterator looks like – here it is
Picking qottle or rottler
Qottle is another way of handling rate limited queues.
In many cases rottler and qottle are interchangeable, but there are some differences that may help to choose one over the other. If you need an apps script drop-in solution then rottler is best. If you are writing something new, use this table to help decide.
qottle and apps script
We can use qottle in this example in place of rottler – and for some cases, like this one where we have an initial array of all the work that needs to be done, it’s probably an easier choice than rottler. There are many more options for qottler, but for single threaded Apps Script we don’t need to use many of them. Qottle is purely async, but it only uses async style commands that Apps Script V8 emulates.
Here’s how to set up the demo above using qottle
And running the whole thing is just this
Qottle will run all the items in its queue according to the rate limit rules, and when all are done will resolve allowing you to dump out the results.
We’ve looked at Rottler to manage rate limits in Apps Script, and also introduced iterators as an alternative to loops. You can imagine a library of prebaked iterators all set up to handle the rate limits of the APIS you commonly use.