Publish Google Sheet addon and sheet template

Question

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

Perhaps you can use a modeless dialog to allow the use to select all of the cells to validate and you can capture them with get active range list — Cooper
Thanks! I believe that will be something advanced for my users, I need to automate most of sheet if not I will recieve lots of questions. — Belisario Peró
Your user can't make multiple selections with the control key? and the click a button? — Cooper

Alessandro Alessandro · Accepted Answer · 2020-06-08T15:04:31

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()

Publish Google Sheet addon and sheet template

1 Answers

Approach

Reference