javascripthtmlgoogle-apps-scriptgoogle-sheetsgoogle-apps-script-addon

How to update Google Sheet Add-on sidebar dropdown list when sheets are renamed?

I am creating a Google Sheet Add-on that compares 2 columns in a spreadsheet. The user can choose the sheets that the 2 columns are in from a dropdown list in the sidebar. When opening then sidebar, the dropdown list is populated using the following code in the sidebar html:

  <select id="sheet1">
    <? var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets(); ?>
    <? for(var i=0;i<sheets.length;i++) { ?>
    <option><?= sheets[i].getName()?></option>
    <? } ?>  
    };
  </select>

However, if the user renames any of the google sheets while the sidebar is open, the list does not get updated. I'm not sure how would be the best way to update the dropdown list. I'm open to the idea of putting a 'Update sheet names' button next to the dropdown list if that would be better than having to watch for every change to the spreadsheet to make it happen dynamically. I've tried many possible solutions today without success! All suggestions gratefully received.

Solution

  • Answer:

    There is no direct way that this can be done, though using an onChange() trigger you can reload the sidebar.

    More Information:

    As per the documentation:

    An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.

    When a sheet is renamed, an onChange() event is triggered with a changeType of OTHER in the event object. When this is detected by onChange()you can set your sidebar to reload.

    Code:

    function onChange(e) {
  if (e.changeType == "OTHER") {
    showSidebar();
  }  
}

function showSidebar() {
  var html = HtmlService.createTemplateFromFile('Page').evaluate()
      .setTitle('Sidebar title')
      .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

    Setting up an Installable Trigger:

    Following the Edit > Current project's triggers menu item, you will have a new page open in the G Suite Developer Hub. Click the + Add Trigger button in the bottom right and set up the trigger settings as follows:

    And press save.

    NB: Any changes to the Spreadsheet's structure, except for those in the following list, will also trigger the sidebar to reload:

    References: