Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-custom-function

How to get auto refresh google spread sheet custom cell fuction from google app script [ custom fuction refresh ]

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();});
}
Solution

  • 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: