Google sheets, scripts stop working after making a copy.

Question

I have a spreadsheet that I have built to be used as a template. This spreadsheet has a few scripts in it, the problem I am having is when you make a copy of the spreadsheet, all of my scripts stop working. I have to go into each script and manually authorize them again.

We would be making a copy of the Master spreadsheet for every single job that comes through my department. Roughly 20-30 copies of the master would be made each day, by multiple people.

Is there anyway to avoid this?

Please see sample code and spreadsheet below.

Thanks,

Tyler

https://docs.google.com/a/costco.com/spreadsheets/d/1vcmjVtS2mKwCGfboVFK14yNoksTJ7pq4vDcKIOpG2oU/edit?usp=sharing

function customDocEmail(){
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == "Version 1 ") {;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var triggerCell = sheet.getRange("C17").getValue().toString();
  var email = sheet.getRange("A17").getValue().toString();
  var EMAIL_SENT = new Date() ;

  var recipients = "[email protected]";
  var cellA1 = ss.getSheetByName("Version 1 ").getRange("A1").getValue().toString();
  var cellB2 = ss.getSheetByName("Version 1 ").getRange("B2").getValue().toString();
  var cellD1 = ss.getSheetByName("Version 1 ").getRange("D1").getValue().toString();


  }
  var subject = 'New customDoc ' + cellA1+ '-' +cellB2;
  var body = ' Hi Stephanie,' + '\n' + '\n' + 'This job ' + cellA1 + '-'+ cellB2+ ', is being created as a CustomDoc.. Please view the specs for this job. ' + '\n' + ss.getUrl() +' '+ '\n' + '\n' +'Thank you,' + '\n' + cellD1 +' ' ;

  if (triggerCell =="YES") 
 {MailApp.sendEmail(recipients, subject, body);
 sheet.getRange("C17").setValue("SENT");

  }
 }
function templateMagix() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Version 1 ");
  var trigger = sheet.getRange("B3").getValue().toString();
  var formNumber = sheet.getRange("A23").getValue().toString();
  var boom = sheet.getRange("C23").getValue().toString();

 if (boom =="BOOM") 

   sheet.getRange("B3").setValue(formNumber);

 }

Answer 1

The reason why you have to authorize your scripts time and again is because each copy that you create is treated as a New Document and so are the scripts attached to that document. And for any new document to be able to run scripts, it is necessary for the user to provide that document permission to run scripts. Being able to make copies and run scripts without having authorized it manually will be somewhat similar to providing a script unauthorized access to run. Which, if it were possible, could be seen as a serious security thread. Therefore, unfortunately so, it is not possible to run scripts without providing them permission to run.

Although, depending on how many scripts you have, your use case and what function(s) they are performing etc., I would suggest that if it is possible combine these functions into one script and call them where necessary. This way, you will have to only provide manual permission to the script once instead of having to do it n number of times (assuming you have n scripts) for each script to be granted permission to run.

Answer 2

I was able to figure out a work around for this and it works flawlessly for my application.

I put all onEdit scripts in the same "Project", then I made a button and assigned my timeStamp Function to it. This prompted the authorization for all of my scripts that required the Auth.

Next I ran into all of the "installed onEdits" not staying installed upon making a copy of the spreadsheet, so the script's still were not functional. A little more digging and research brought me to writing a simple but effective script, that I assigned to my newly created button. Which in turn install's all of my onEdit triggers as well as prompting the authorization and all script's now work as they were intended.

I hope someone dealing with the same issues can find some use in this.

            function authoRize(e) {

  Browser.msgBox("Great! Let's be friends!");

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("customDocEmail")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("templateMagix")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("GetData")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("GetPJData")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("hideSeek")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("hideSeekOP")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("hideSeekPJ")
   .forSpreadsheet(sheet)
   .onEdit()
   .create();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("HOME");
  var newDate = new Date() ;
  sheet.getRange("A5").setValue(newDate);


  goToSheet('Version 1 ');

 }