1

how to get current list of sheet names automatically refresh by google app script when making new sheets or changing sheet name or duplicaing sheets or deleting sheets from google spread sheet

:::::: I need list of sheets name ::::::::::::

  1. There are many sheets
  2. New sheet will be added by other user
  3. Name of new sheet will be changed by other user
  4. some sheets will be deleted by other user
  5. I need existing sheets name list not past

::::::::::::::::::::::::::::::::::::::::

and the list of sheet name should display on second sheet that code expression is sheet[1]

below code are working well. but it's not refresh by adding sheets or deleting sheets

function sheetnames()
{
 return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function(x) {return x.getName();});
}
riore El
  • 61
  • 5
  • Can you describe the problem more clearly and post more code? What do you mean by "it's not refresh by adding sheets or deleting sheets"? Can you post the code where you add or delete sheets? When are you calling `sheetnames()`? – Lucas D Jul 09 '20 at 04:34
  • 1
    In your situation, you are using the function of `sheetnames()` as the custom function. And you want to refresh the custom function of `=sheetnames()`. Is my understanding correct? – Tanaike Jul 09 '20 at 04:59
  • I share google spread sheet with many people which not used to spreadsheet. sometimes they make errors like make new unnecessary sheet . so I have to find and delete it . but it's not easy to find new sheet from tap viewer. so sometime I use it find new sheet locations – riore El Jul 09 '20 at 05:09
  • Does this answer your question? [Is there a faster way to refresh all of my custom formulas?](https://stackoverflow.com/questions/61946438/is-there-a-faster-way-to-refresh-all-of-my-custom-formulas) – TheMaster Jul 09 '20 at 05:10
  • To tanaike...........Yes, it is what I want – riore El Jul 09 '20 at 05:11
  • 1
    @TheMaster I also thought the same thread with your comment. I thought I tried to propose the thread using the OnChange event trigger. But in this case, when the sheet is inserted and deleted, the execution of script can be confirmed with the types of "REMOVE_GRID" and "INSERT_GRID". But, when the sheet name is changed, the type is "OTHER". In this case, the type is the same with when the custom function is refreshed. By this, the infinite loop of the trigger occurs. So I modified the script in the thread and I proposed the sample script for achieving OP's goal. – Tanaike Jul 09 '20 at 05:20
  • @riore El Thank you for replying. I proposed a sample script as an answer. Could you please confirm it? – Tanaike Jul 09 '20 at 05:20
  • 1
    @Tanaike Nice. I ran into the infiniteloop before. Didn't realize that this was the issue. onChange() is strange: gets triggered on custom function refresh, sheets api input etc. It's good in a way. – TheMaster Jul 09 '20 at 05:36
  • @Tanaike Oh no. I hope Google doesn't remove the feature altogether, now that you brought it to their attention. – TheMaster Jul 09 '20 at 07:31
  • @TheMaster Thank you for replying. I cannot understand about the meaning of your replying. If I misunderstand about this, I have to apologize. – Tanaike Jul 09 '20 at 07:38
  • @Tanaike [Script executions and API requests do not cause triggers to run.](https://developers.google.com/apps-script/guides/triggers/installable). ``onChange`` works clearly in violation of this rule. So, I hope Google doesn't "fix" this by "removing" the functionality altogether. Just a fear I have. – TheMaster Jul 09 '20 at 07:41

1 Answers1

3

I believe your situation and goal as follows.

  • You are using the function of sheetnames() as the custom function on Google Spreadsheet.
  • You have already confirmed that your function of sheetnames() works.
  • You want to refresh the custom function when the sheet is deleted, inserted, copied and the sheet name is changed.

In order to achieve above, I would like to propose the following method.

Usage:

1. Prepare script.

In this case, the sample script for refreshing the custom function of sheetnames() in the Spreadsheet is run by the OnChange event trigger. For this, please copy and paste the following sample script to the container-bound script of Spreadsheet, and save the script.

function onChange(e) {
  var lock = LockService.getDocumentLock();
  if (lock.tryLock(10000)) {
    try {
      const prop = PropertiesService.getScriptProperties();
      if ((e.changeType === "OTHER" || e.changeType === "REMOVE_GRID" || e.changeType === "INSERT_GRID") && !prop.getProperty("run")) {
        const formula = "=sheetnames";  // <--- Please set the function name of the custom function.
        const ss = e.source;
        const tempFormula = "=sampleFormula";
        ss.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
        ss.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
        prop.setProperty("run", "done");
      } else {
        prop.deleteProperty("run");
      }
    } catch(e) {
      throw new Error(e);
    } finally {
      lock.releaseLock();
    }
  }
}
  • In order to avoid the duplicate run of the script, LockService is used.
  • In order to avoid the infinite loop of the trigger, PropertiesService is used.

2. Install OnChange event trigger.

In order to execute the function of onChange, please install the OnChange event trigger to the function onChange. You can see the method for installing this at this official document.

3. Testing

In order to test above script, after you installed the function onChange as the installable OnChange event trigger, for example, please insert new sheet. By this, you can confirm the custom function sheetnames() is refreshed.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165