Using the `googleapis` library in node.js to create an installable trigger in a Google Sheet

Question

I am trying to create an "onEdit" trigger programmatically in a Google Sheet using node.js. I can create a new script project and add a script (with associated code) to it. However, I can't seem to find a way to create an "onEdit" trigger that will run the script. ChatGpt suggests (nay, insists 😅) that script.projects.triggers.create is the way to go, but triggers doesn't exist in the script.projects object and so the code fails.

Here is my code:

async function addOnEditScriptToGoogleSheet(googleSheetId, auth) {
    let scriptAddRes = {
        ok: true,
        message: ''
    }

    const syncScript = fs.readFileSync(path.resolve(__dirname, '../data/syncGoogleScript.js'), 'utf-8');
    const script = google.script({version: 'v1', auth});

    // Create the script
    try {
        // Create a new project
        const createResponse = await script.projects.create({
            requestBody: {
                parentId: googleSheetId,
                title: 'mySyncProject'
            }
        });
    
        const scriptId = createResponse.data.scriptId;
        console.log('Created new script project with ID:', scriptId);

        // Get all the files created (including the manifest file)
        const contentResponse = await script.projects.getContent({
            scriptId: scriptId
        });

        // Add sync script
        const files = contentResponse.data.files;
        files.push(
            {
                name: 'syncScript',
                type: 'SERVER_JS',
                source: syncScript
            }
        );
    
        // Update the newly created project
        await script.projects.updateContent({
            scriptId: scriptId,
            resource: {
                files: files
            }
        });
    
        console.log('Script content updated successfully.');

        // Create the trigger
        await script.projects.triggers.create({
            scriptId: scriptId,
            requestBody: {
                function: 'syncToMyServer',
                triggerType: 'ON_EDIT'
            }
        });

        console.log(`Trigger created with ID: ${res.data.triggerId}`);
    } catch (error) {
        const message = `Failed to create or update script: ${error.message}`;
        console.error(message);
        return {
            ok: false,
            message: message
        };
    }

    return {
        ok: true,
        message: 'Trigger successfully created';
    };
}

Everything works up until the line await script.projects.triggers.create. This line fails with the error 'Cannot read property 'create' of undefined'.

I can't find any documentation to support ChatGpt's claim that there is a script.projects.triggers.create function. But assuming that ChatGpt is wrong (😳😛), is there any way to actually do what I'm trying to do using the googleapis node.js client library?

Thanks.

Answer 1

The Google APIs for Node.js documentation does not provide a specific request for creating installable triggers. This is due to the nature of how triggers are set up and managed within the Google Apps Script environment, which requires user authentication and script execution within the Google environment.

First, I suggest that you create a setup function in another Apps Script project (used as a template) that sets up the installable triggers, such as the one below.

  function createEditTrigger() {

 ScriptApp.newTrigger("runOnEdit")
  .forSpreadsheet(SpreadsheetApp.getActive())
  .onEdit()
  .create()

 }

In order to integrate this with Node.js, you can use the projects.getContent method to retrieve the content of your Apps Script project, then update the project with your trigger creation function using the projects.updateContent method.

Finally, you can use the scripts.run method from the Google Apps Script API to execute your trigger creation function.

Basically while you cannot add a function directly through Apps Script API, you can copy and paste the content of another project in the new one and then run the pasted function.