How to make a Google sheet template with a script where at each instance creation the user do not need to authorize the script access rights?

Question

I have a google sheets with a menu a calling a script. The script is saved separately as Library with a saved version. This Sheet is saved on a shared drive as Google domain template. The users create about 10 sheets a week and have each time to authorize the script.

Which solutions are available to avoid this recurring process. Add-on doesn't seem to be the right solution as the script has to work only for this Spreadsheet

function onEdit(e) {
  const rangeName="A2";
  var ass=SpreadsheetApp.getActiveSpreadsheet();
  var as=ass.getActiveSheet();
  var adresse=as.getActiveRange().getA1Notation();

  Logger.log(JSON.stringify(e));
  if (adresse===rangeName) {
    SpreadsheetApp.getActiveSpreadsheet().rename(as.getRange(rangeName).getValue());
    ass.toast("DONE!");
  }

I think your best option is deploying the script as an add-on. Could you share a copy of the script you're working on? — Iamblichus
@cooper it renames automatically the spreadsheet with the contents of 3 different cells with a onEdit trigger — Mikah
I'm sorry, I'm not sure I understand your situation. What's the point of having an onEdit trigger used as a library that is called (if I understand you correctly) via a UI menu? How can this even work? Why can't you just have the onEdit in the script bound to your spreadsheet? The script wouldn't need authorization at all in this case (also, you could use the onEdit event object instead of the getActive methods. — Iamblichus

Iamblichus Iamblichus · Accepted Answer · 2020-05-08T14:11:40

You have a script which should work for all copies made from a certain template (multiple users make copies).
You want to avoid users having to re-authorize the script for each copy of the template they create.

If that's correct, I think your best option is to deploy the script as an add-on, and installing the add-on for all users in the domain. The workflow would be the following:

Create a standalone Apps Script project and associate it with a standard GCP project.
In the beginning of your function, look for a value in a certain cell, whose purpose is to identify the file as a copy of the template. For example, something like this:

function yourFunction() {
  var templateIdentifier = SpreadsheetApp.getActive().getSheetByName("Settings").getRange("A1").getValue();
  if (templateIdentifier = "12345") { // Check that file is copy of template "12345"
    // Execute the desired script actions
  }
}

The template and all its copies should have this value, so you should probably protect this cell from edition in the original template (all copies will keep this protection). Personally, I'd also placing this cell in a hidden sheet so that users cannot see it.
Test that the add-on is working correctly, and publish it. following [this guide].
Once the add-on is published, install it for all users in the domain, following this.

How to make a Google sheet template with a script where at each instance creation the user do not need to authorize the script access rights?

1 Answers

Reference: