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
})

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()

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
  })

Get the input sheet data

It’s just some airport data

And we want to copy the sheet and use the openweather API to add some weather info, so it looks like this

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 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
  });

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
    }
  }

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())

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 ();

Method 2 – a simple loop, auto waiting

  copy.setData (rows.map (row => {
    rot.rottle()
    return decorateRow({result: getWeather({row, apiKey}), row })
  })).dumpValues ()

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' }

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 });

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()

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()

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()

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

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.

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,
              });
            }
          },
        };
      },
    };
  }

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.

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
   }) 

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())

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