google-apps-scriptgoogle-sheetschrome-web-storegoogle-apps-script-addon

Publish Google Sheet addon and sheet template


I have developed a google sheet script which creates a custom menu. That menu item validates data in sheet but is based on a Google Sheet template which defined where user must enter each cell value for validation.

I want to publish the menu script as an addon. So, if user installs the addond but it's not using the google sheet template, data will not be validated as expected because he doesn't knows whre to enter each cell value.

Is there any chance to add the addon download the sheet template or any other workaround?

I published the Google Sheet to the web but that option removes menu bar, so user can't use the addon.

Which is the best approach for this kind of requirement?

Regards


Solution

  • Approach

    A possible workaround would be making the template public and copy it in the active Spreadsheet when running the AddOn.

    Here is an example:

    function onOpen(e) {
      SpreadsheetApp.getUi().createAddonMenu()
          .addItem('Insert Template', 'copyTemplate')
          .addItem('Your validation function', 'validationFunction')
          .addToUi();
    }
    
    function copyTemplate() {
      let ss = SpreadsheetApp.getActiveSpreadsheet();
      let template = SpreadsheetApp.openById('template_id').getSheets()[0];
      let newss = template.copyTo(ss);
      newss.activate();
    }
    

    Reference

    Sheet .copyTo()

    .createAddonMenu()