Google Sheets Script to automatically add (and update) a list of all sheets into a range?

Question

I am trying to pull a list of all sheets and update this list if something is changed in the workbook.

In my google sheet I am using the below function to place all sheets into a list:

=SHEETNAME()

With the script below I am aiming to update it at a change event:

    function sheetName(e) {
      return SpreadsheetApp.getActive()
        .getSheets()
        .map(function(sheet) {
          return sheet.getName();
        });
    }

    /*Create a installable trigger to listen to grid changes on the sheet*/
    function onChange(e) {
      if (!/GRID/.test(e.changeType)) return; //Listen only to grid change
      SpreadsheetApp.getActive()
        .createTextFinder('=SHEETNAME\\([^)]*\\)')
        .matchFormulaText(true)
        .matchCase(false)
        .useRegularExpression(true)
        .replaceAllWith(
          '=SHEETNAME(' + (Math.floor(Math.random() * 500) + 1) + ')'
        );
    }

Have set up the below trigger - which also runs at change:

Yet unfortunately, the list is not automatically updated.

Any help much appreciated!

Answer 1

You have to set your installable Trigger, in order to do it, follow these steps:

1) Go to your Apps Script project

2) Click Edit->Current project's triggers

3) Click "+ Add Trigger"

4) Select :

• Choose which function to run -> Function Name

• Select event source-> From spreadsheet

• Select event type -> On change

Now, I modified your onChange function a little because otherwise, you would enter in an infinite loop

function sheetName(e) {
  return SpreadsheetApp.getActive()
    .getSheets()
    .map(function(sheet) {
      return sheet.getName();
    });
}

/*Create a installable trigger to listen to grid changes on the sheet*/
function onChange(e) {
  Logger.log(e.changeType)
  if (/GRID/.test(e.changeType)){
    SpreadsheetApp.getActive()
    .createTextFinder('=SHEETNAME\\([^)]*\\)')
    .matchFormulaText(true)
    .matchCase(false)
    .useRegularExpression(true)
    .replaceAllWith(
      '=SHEETNAME(' + (Math.floor(Math.random() * 500) + 1) + ')'
    );
  }
}

Docs

These are the docs I used to help you:

• Installable Triggers.

• Change Event.