CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-macros
Dum Potato
Dum Potato

Reputation: 965

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

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: enter image description here

Yet unfortunately, the list is not automatically updated.

Any help much appreciated!

Upvotes: 0

Views: 2016

Answers (2)

RemcoE33
RemcoE33

Reputation: 1610

Your function name needs to be onEdit(e).

See this article about triggers from Google dev.

Upvotes: 0

alberto vielma
alberto vielma

Reputation: 2342

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:

Upvotes: 1

Related Questions