How to use Google Script Library to call order of operation for scripts across multiple Worksheets

Question

I've been attempting to find a way to run scripts across multiple worksheets in a specific order. Each worksheet has its own number of scripts that run just fine when I use them (by creating a "combined" script attached to a button and/or menu).

There are many scripts and going through each one manually to run scripts is not efficient.

I've been trying to utilize libraries, but have not found a way to make it work correctly.

I've seen the Google Apps Script section on libraries, and relevant stackoverflow threads like this one, but haven't been able to figure it out.

Here's the last attempt I put together (after trying many different methods...)

function mastercombine() {
    
    const west = SpreadsheetApp.openByUrl(`https://docs.google.com/spreadsheets/d/1NmN7h2wEGFlY1FatjEpJ9MRpZ_I_MtlyoApGG4F6rcw/edit#gid=589651642`);

    west.West.combine();
    
    
    freezeValues1();
    
}

I think maybe I'm not entirely understanding how to call each item within the library to run the function on its respective worksheet. I've tried calling it a few different ways, but I'm stumped because they all give null errors.

Textual error I receive:

TypeError: Cannot read property 'masterSheetInfo' of undefined mastercombine @ Copy of Combined Reset.gs:5

The section beginning with west.West is attempting to call library worksheets/functions. "west" being the const, "West" being the Library ID, "combine" being the function within West (which runs correctly on its own within its own worksheet).

The ending section (freezeValues1) is a function that should run on the current worksheet (and it works stand-alone without library section).

The closest I've come to getting things to run in a certain order is attaching installable triggers to each worksheet, but I can't have the "will run sometime within the hour" bit that seems to come with it. I'm looking to call them back-to-back with one trigger.

Script being called from within the library:

function combine() {

  sortSheets();

  SpreadsheetApp.flush();
  Utilities.sleep(500);

  sheetNames();

}

which calls the following function:

function sheetNames() {
    var ss, list, tar;
        ss = SpreadsheetApp.getActive()
        list = ss.getSheets().slice(4).map(function (s, ind) {
            return ['=HYPERLINK("https://docs.google.com/spreadsheets/d/1tfi_L668w7hpDqeGl84TEfVIm4zKMkyuwrKsG-uKx9A/edit#gid=' + s.getSheetId() + '", "' + s.getName() + '")'];
        })
        tar = ss.getSheets()[0]; //List will be written to FIRST sheet in the workbook.
        tar.getRange(2, 1, tar.getLastRow(), 1).clearContent();
    tar.getRange(2, 1, list.length, 1).setValues(list)
   
}

Answer 1

From the question

The section beginning with west.West is attempting to call library worksheets/functions. "west" being the const, "West" being the Library ID, "combine" being the function within West (which runs correctly on its own within its own worksheet).

1. A Google Apps Script library hasn't worksheets.
2. west.West is not working because west has assigned a Class Spreadsheet object and this class hasn't a West property/method. Because of this west.West returns undefined.

One option is to convert West.masterSheetInfo() into a parametized function i.e. West.masterSheetInfo(west)

NOTE: If the parametized function, has a line like

const west = SpreadsheetApp.getActiveSpreadsheet();

might be replaced by something like

var west = west || SpreadsheetApp.getActiveSpreadsheet();

Regarding the order of execution, in Google Apps Scripts function statements are executed in the order they are wrote.

Resources

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Functions

Related

• Order of execution of functions in functional programming
• Google script - call library function with .getActiveSpreadsheet()

Answer 2

I'm afraid that is not possible

Not via Apps Script itself anyway. You can run functions remotely, but only via the Apps Script API.

https://developers.google.com/apps-script/api/how-tos/execute

Though that comes with its own complexity and limitations.

Even triggers will not fire from changes to the sheet made by other scripts. So you can't have a utility sheet where your external script can change something and have an onChange trigger on the sheet.

From:

https://developers.google.com/apps-script/guides/triggers/installable#restrictions

Script executions and API requests do not cause triggers to run.

Workaround

Why is it necessary for the scripts to live on the target spreadsheets? Can't you just have a master script that contains all the code and then operates on all the spreadsheets?

For example:

function main() {
  let ssIds = [
    "1b2RdsafasdfasdfE4GZy5y5465y54y54sMw",
    "1b2R40ierqwerqwerf9Mvkm6I-TqE4GZsyMw",
    "1b2R40iHFqqwerewqrwerkm6I-TqE4GZysMw",
    "1b2R40iHFqOV7-ytqwertywerkm6I-TqGZsMw",
    "1b2R40iHFqOVtryertyerf9Mvkm6I-T4GZsMw"
  ];

  ssIds.forEach(id => {
    let ss = SpreadsheetApp.openById(id);

    // Do stuff here.
    //...
    //...
  })
}