Running Google Apps Script functions in the background, Part 1

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.
• Janaka Bandara.
• Dariusz Kuśnierek.
• Agent Provocateur.

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

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 (which transpiles typescript to Google Apps Script).

First up, I use the following class to manage ScriptProperties:

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.

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.

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

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:

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.