DEV Community

Bruce Axtens
Bruce Axtens

Posted on • Edited on

1

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

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();
    }
}
Enter fullscreen mode Exit fullscreen mode

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));
}
Enter fullscreen mode Exit fullscreen mode

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 + "()");
    }
}
Enter fullscreen mode Exit fullscreen mode

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

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);
}
Enter fullscreen mode Exit fullscreen mode

and I've noticed that timer-controlled function runs don't show toasters.




Note: I am still trying to internalise the Google JavaScript Style Guide. If you see stuff that makes you grit your teeth, please let me know what and why. I'm a consistency-challenged programmer.

Billboard image

Use Playwright to test. Use Playwright to monitor.

Join Vercel, CrowdStrike, and thousands of other teams that run end-to-end monitors on Checkly's programmable monitoring platform.

Get started now!

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay