CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Johnny
Johnny

Reputation: 41

Is there a way to make copies of a spreadsheet that retain installable triggers?

I have a spreadsheet I made for work that I need to make multiple copies of and distribute to co-workers. My "master" spreadsheet has a bunch of protected columns, along with an Auto Sort java script function. Due to the protections in place, I cant utilize simple triggers as the user's permission levels wont allow the java script to work.

To get around this, I am using installable triggers that allow the JS to use my permission to function. The problem is, every time I make a copy, I have to go in and set up triggers which is a giant pain. Is there a way to make copies that also retain the installable triggers?

If not, is there a different way I can set up triggers to work in this situation?

Upvotes: 1

Views: 187

Answers (1)

Serge insas
Serge insas

Reputation: 46794

Copies don't include triggers, the solution is simply to create an "install" function that will create the triggers with the user authority while also asking for authorization. Users receiving the copy must run that function on first use, just ask them to do so with a popup message that would run in an "onOpen" function until the install function has been executed.

below is a code example of the whole process :

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Utilities")
    .addItem("install", 'install')
    .addToUi();
  if (keys.length === 0) {  // if no properties has been written
    Browser.msgBox('Please run the installation procedure from the menu');
  }
}

var keys = PropertiesService.getScriptProperties().getKeys(); // this is placed outside of the function to define keys as global

function install() {
  ScriptApp.newTrigger('yourfunctionName').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  PropertiesService.getScriptProperties().setProperty('key', 'installed correctly');
}

function yourfunctionName(e) {
  Browser.msgBox(JSON.stringify(e));
}

Upvotes: 1

Related Questions