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.

  1. get all the projects and their script files – rate limits on Drive API, Script management API
  2. detect changes and construct any support files – rate limits on Drive API
  3. find dependencies (libraries referenced, Advanced services referenced) – rate limits on Script management API
  4. 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

// by default, rottler is asynchronous
// because apps script is synchronous we need to provide a sleep function
const sleep = Utilities.sleep

// drive rate limit  1000 reqs per 100 seconds, 20 ms min delay min between each one
const driveRot = bmRottler.newRottler({
	rate: 100,
	period: 1000 * 60,
	delay: 20,
	sleep,
	synch : true
})
// script management API - couldnt find the official rate on this so trial and error gave this
const scriptRot = bmRottler.newRottler({
	rate: 5,
	period: 1000,
	delay: 20,
	sleep,
	synch : true
})
// github api - 80 reqs per minute - pla on safe side with 60
const gitRot = bmRottler.newRottler({
	rate: 60,
	period: 1000 * 60,
	sleep,
	synch : true
})
rots

Then wherever I need to access any APIS

// accessing any api anywhere - precede with a call to its rottler
// that'll wait till it's safe to call the API again
rotGit.rottle()

// then you can go
callApi()
rottle

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


  const rot = bmRottler.newRottler({
    period: 1000 * 60,
    delay: 100,
    rate: 40,
    synch: true,
    sleep: Utilities.sleep
  })
weather rot

Get the input sheet data

It’s just some airport data

name latitude_deg longitude_deg elevation_ft iso_country iso_region municipality iata_code
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

name latitude_deg longitude_deg elevation_ft iso_country iso_region municipality iata_code timestamp description temp humidity
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


// input data 
  const fiddler  = bmPreFiddler.PreFiddler().getFiddler({ 
	id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ', sheetName: 'airport list'
  });

// make a copy here
  const copy = bmPreFiddler.PreFiddler().getFiddler({
    id:fiddler.getSheet().getParent().getId(),
    sheetName: 'copy sheet',
    createIfMissing: true  
  })
  
// pull the data out
  const rows = fiddler.getData();
set up fiddlers

 

Set up the rottler for the openweather API

  // set up rate limiting for the weather API
  // allow on 40 calls per minute, with at least 100ms betweene ach one
  const rot = bmRottler.newRottler({
    period: 1000 * 60,
    delay: 100,
    rate: 40,
    synch: true,
    sleep: Utilities.sleep
  });
openweather rot

Create a function to enrich the airport data with the open weather API result

 // decorate the spreadsheet data with data from the api
 const decorateRow = ({result, row}) => {
    const {weather, main, dt} = result;
    return {
      ...row,
      description: weather[0].description,
      temp:  main.temp,
      humidity: main.humidity,
      timestamp: dt * 1000
    }
  }
decorate

Create a function to query the api

 const getWeather = ({row, apiKey}) => 
  JSON.parse(UrlFetchApp.fetch (`https://api.openweathermap.org/data/2.5/weather?lat=${row.latitude_deg}&lon=${row.longitude_deg}&appid=${apiKey}`).getContentText())
access weather 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

  copy.setData (rows.map (row => {
    Utilities.sleep(rot.waitTime())
    rot.use()
    return decorateRow({result: getWeather({row, apiKey}), row })
  })).dumpValues ();
manual waiting

Method 2 – a simple loop, auto waiting

  copy.setData (rows.map (row => {
    rot.rottle()
    return decorateRow({result: getWeather({row, apiKey}), row })
  })).dumpValues ()
automatic 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

// this is an example of an iterator
  while (files.hasNext()) {
    const file = files.next()
	// do something with file
  }

An iterator returns an object each time .next() is called. At a minimum that object looks like this

// when done
{ done: true }

// when not done
{ done: false , value: 'whatever the next value is' }
iterator return object

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.

const rowIterator = rot.rowIterator({ rows });
an iterator

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’.

  const rowIterator = rot.rowIterator({ rows });
  // and decorate as we go
  const newData = []
  for   (let {row} of rowIterator) {
    newData.push(decorateRow({result: getWeather({row, apiKey}), row }))
  }
  // write update values
  copy.setData(newData).dumpValues()
iterator loop

But there’s more…

Transformers

The rottler iterator also supports transformers. That means it can not only return data in a controlled way, it can also transform it.

  const rowTransformer = rot.rowIterator({ 
    rows, 
    transformer: ({row})=> decorateRow({result: getWeather({row, apiKey}), row })
  })

  // and decorate as we go
  const newTransformed = []
  for   (let {transformation} of rowTransformer) {
    newTransformed.push(transformation)
  }
  // write update values
  copy.setData(newTransformed).dumpValues()
transformer

 

And there’s even more

Array.from iterators

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

  copy.setData(
    Array.from(rot.rowIterator({ 
      rows, transformer: ({row})=> decorateRow({result: getWeather({row, apiKey}), row })
    }))
    .map(({transformation})=>transformation))
    .dumpValues()
arrayfrom iterator

Async iterators

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

whereas a sync loop uses

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.

Iterator code

If you are interested in what the Rottler iterator looks like – here it is

/**
   *
   * @param {object} options
   * @param {*[]} options.rows an array of data to iterate through
   * @param {function} [options.transformer] an optional function to apply to each row
   * @return {object} an iterator
   */
  rowIterator({ rows, transformer }) {
    const self = this;
    const getItem = ({ waitTime, async, index }) => {
      const value = {
        index,
        row: rows[index],
        rows,
        waitTime,
        transformation: null,
        async,
      };

      const item = {
        value,
        done: false,
      };
      // an optional transformation
      if (transformer) {
        value.transformation = transformer(value);
      }
      return item;
    };

    return {
      // will be selected in for await of..
      [Symbol.asyncIterator]() {
        return {
          rowNumber: 0,
          rows,
          hasNext() {
            return this.rowNumber < this.rows.length;
          },
          next() {
            if (!this.hasNext()) {
              return Promise.resolve({
                done: true,
              });
            } else {
              const waitTime = self.waitTime();
              return self.rottle().then(() => {
                return getItem({
                  waitTime,
                  index: this.rowNumber++,
                  async: true,
                });
              });
            }
          },
        };
      },
      // will be selected in for of...
      [Symbol.iterator]() {
        return {
          rowNumber: 0,
          rows,
          hasNext() {
            return this.rowNumber < this.rows.length;
          },
          next() {
            if (!this.hasNext()) {
              return {
                done: true,
              };
            } else {
              const waitTime = self.waitTime();
              // this#ll be a synchronous wait
              self.rottle();
              return getItem({
                waitTime,
                index: this.rowNumber++,
                async: false,
              });
            }
          },
        };
      },
    };
  }
rowIterator

Picking qottle or rottler

See also https://github.com/brucemcpherson/rottler and https://github.com/brucemcpherson/qottle

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.

feature qottle rottler
synch no yes
async yes yes
smoothing no yes
iterator no yes
multi thread yes no
concurrence control yes n/a
logging yes no
duplicate management yes no
transformers no yes
drop in no yes
apps script yes yes
node yes yes
javascript yes yes

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

   const qot = bmQottle.newQottle({
     rateLimitPeriod: 1000 * 60,
     rateLimited: true,
     rateLimitDelay: 100,
     rateLimitMax: 50,
     concurrent: 1
   }) 
qottle setup

And running the whole thing is just this

   // add all the work to the queue then when it's all over, dump it
   return Promise.all (rows.map(row=>qot.add(({entry})=>{
     console.log(entry.waitTime)
     return decorateRow({result: getWeather({row, apiKey}), row })
   })))
   .then(results=>copy.setData(results.map(f=>f.result)).dumpValues())
running using qottle

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.

Summary

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.

The code discussed above is here or on github. You’ll need to get your own openweather apikey.

Take a look at the articles on qottle and rottler for how to get their libraries