Copy spreadsheet on trigger - onOpen

Question

I am trying to create such process: You open a spreadsheet and on trigger onOpen i want to copy that spreadsheet to users drive. I do not know why code below is not doing that, i tried version with

  spread = SpreadsheetApp.getActive()

but is was not also not working

function onOpen(e){
 var spread = e.source
 var dest = DriveApp.getRootFolder();  
 var name = "NEW NEW spreadsheet test"
 var newfile = DriveApp.getFileById(spread.getId()).makeCopy(name,dest);
 Browser.msgBox('File will appear on your Drive in a moment');
 SpreadsheetApp.setActiveSpreadsheet(newfile)
}

Please i am looking to suggestions what i am missing or misinterpreting. Thank You!

onOpen(e) has limited auth-mode. Not sure you can access drive and create a spreadsheet with this authorisation. — JSmith
Code does not execute for viewers -only editors. Consider using a webapp to allow sign in and copy of the document. — tehhowch
Or you can simply share the link with /copy at the end rather than /edit. This way, You don't need full drive permission of every user and you respect their right to "refuse" to make a copy. — TheMaster

JSmith JSmith · Accepted Answer · 2018-10-22T13:24:36

try:

function onOpenTrigger(){
 var spread = SpreadsheetApp.getActiveSpreadsheet();
 var dest = DriveApp.getRootFolder();  
 var name = "NEW_NEW_spreadsheet_test"
 var newfile = DriveApp.getFileById(spread.getId()).makeCopy(name,dest);
 var newSpreadsheet = SpreadsheetApp.open(newfile);
 Browser.msgBox('File will appear on your Drive in a moment');
 SpreadsheetApp.setActiveSpreadsheet(newSpreadsheet)
}

I've made it work using this steps:

rename onOpen by another name, here onTriggerOpen
manually create a trigger that runs on opening of the document
first execute the function from the google App IDE to allow actions for user

onOpen is set to a LIMITED authorisation so not sure if it will work for other users that haven't executed onOpenTriggerfrom the IDE.

Also you are passing a File object instead of a spreadsheet object inside setActiveSpreadsheet(spreadsheet) so one solution is to use SpreadsheetApp.open(file) first

REFERENCES

Open()

setActiveSpreadsheet()

Triggers

Copy spreadsheet on trigger - onOpen

1 Answers