Running Google Apps Script functions in the background, Part 1 
Tags: javascript, googleappsscript, googlesheets
Published: 2019-09-10 07:45:09

Lately I've been hitting the 6 minute timeout wall in Apps Script. Various solutions have been devised by a variety of brilliant programmers (this is my short list):
 * [Amit Agarwal](https://ctrlq.org/code/20016-maximum-execution-time-limit).
 * [Janaka Bandara](https://dzone.com/articles/beating-the-apps-script-clock-say-hello-to-memshee).
 * [Dariusz Kuśnierek](https://medium.com/@dkodr/how-to-keep-track-of-google-apps-scripts-total-execution-time-c46e9d1dfdef).
 * [Agent Provocateur](http://patt0.blogspot.com/2014/08/continuous-batch-library-update-for.html).

My take on the problem is to put the names of the functions that I want to run in the background into a Script Property and then pull them out each time a Timer is triggered. This is, of course, limited to one function per minute. In a subsequent posting I will demonstrate how to increase that to 4 per minute, and conceivably any slice of a minute (by using [Installable Triggers](https://developers.google.com/apps-script/guides/triggers/installable).)

Also, these need to be functions that don't require any parameters and can handle being run in the background. For example, I have one that changes the background colour of certain cells in one sheet based on values in another.   

The project from which this article derives uses Visual Studio Code, typescript and [ts2gas](https://github.com/grant/ts2gas) (which transpiles typescript to Google Apps Script). 

First up, I use the following class to manage ScriptProperties:
```typescript
class ScptProps {
    scriptProperties: any;
    constructor() {
        this.scriptProperties = PropertiesService.getScriptProperties();
        return this;
    }

    get(name: string): string {
        return this.scriptProperties.getProperty(name);
    }

    set(name: string, value: any) {
        return this.scriptProperties.setProperty(name, value);
    }

    forget(name: string) {
        return this.scriptProperties.deleteProperty(name);
    }

    getKeys() {
        return this.scriptProperties.getKeys();
    }
}
```
Next is the function I call in other parts of the project to specify what function is to be backgrounded.
```typescript
function AddToFunctionQueue(fn: string) {
    const scpt = new ScptProps();
    let funcQueueTxt = scpt.get("FUNCTION_QUEUE");
    if (funcQueueTxt === null || funcQueueTxt === "[null]") {
        funcQueueTxt = "[]";
    }
    let funcQueueJsn = JSON.parse(funcQueueTxt);
    funcQueueJsn.push(fn);
    scpt.set("FUNCTION_QUEUE", JSON.stringify(funcQueueJsn));
}
```
The nature of Properties is that they have to be text, so the function makes sure that a text representation of the array is stored.

Next thing to do is to go the the Edit menu in Script Editor and select "Current Project's Triggers". This will take you to the "G+ Suite Developer Hub". There's a button in the bottom right called "Add Trigger". However, before you click that, you have to have a function already in place to handle the Trigger you're about to create.

I have (well actually _had_ seeing as I'm now doing triggers every 15 seconds) a function called `onTimer`, which calls another function devised to pull the queued name out of Script Properties and evaluate it, storing the reduced queue back into Script Properties.
```typescript
function onTimer() {
    ProcessFunctionQueue();
}

function ProcessFunctionQueue() {
    const scpt = new ScptProps();
    let funcQueueTxt = scpt.get("FUNCTION_QUEUE");
    if (funcQueueTxt === "[null]" || funcQueueTxt === null) {
        funcQueueTxt = "[]";
    }
    const functionQueueJsn = JSON.parse(funcQueueTxt);
    if (functionQueueJsn.length > 0) {
        const functionQueueItm = functionQueueJsn.pop();
        scpt.set("FUNCTION_QUEUE", JSON.stringify(functionQueueJsn));
        eval(functionQueueItm + "()");
    }
}
```
Now go back to where you were about to click "Add Trigger" and click it. Fill out the form and save it. Here's a sample
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/p9enorwyi9q0pe4z7z5r.JPG)

So now, every minute, the `onTimer` function will be called. If there's a function name in the `FUNCTION_QUEUE` it will be retrieved, eval-ed and the name removed from the queue. 

It should be noted that if there are any calls to the Toaster, these won't appear. I wrap the Toaster as follows:
```typescript
function Toast(msg: string, title: string, timeout: number) {
    SpreadsheetApp.getActiveSpreadsheet()
        .toast(msg || "Finished", title || PROGRAM, timeout || 1);
}
``` 
and I've noticed that timer-controlled function runs don't show toasters. 
<hr>
Note: I am still trying to internalise the [Google JavaScript Style Guide](https://google.github.io/styleguide/jsguide.html). If you see stuff that makes you grit your teeth, please let me know what and why. I'm a consistency-challenged programmer.