1

I have a code that creates a copy of the master sheet and saves it in a particular folder.

The reason for this is so that the person accessing the sheet does not mess up the main sheet but rather edits their own copy.

My code only creates the copy, but am at a loss at the point where a user will need to open their saved copy and edit it. This should happen automatically when the master sheet is opened. I will appreciate help on this.

Here is my code:

function onOpen(e){
 var Book = SpreadsheetApp.openById(PCDF_sheet_id);
 var Tab = Book.getSheetByName(PCDF_tab_name);
 var PCDF_Data = Tab.getDataRange().getValues(); 
 var user = Session.getEffectiveUser().getUsername(); 
 var dest = DriveApp.getFolderById(Copy_Folder_id); 
 var name =  Book.getName()+ user + " Copy ";
 var newfile = DriveApp.getFileById(Book.getId()).makeCopy(name,dest);
 var newSpreadsheet = SpreadsheetApp.open(newfile);
 SpreadsheetApp.setActiveSpreadsheet(newSpreadsheet)
}
Just
  • 437
  • 5
  • 15
  • This is very related: https://stackoverflow.com/q/11315521/11225291 I think it is the only way to redirect to a page (in this case spreadsheet page). – Marios Mar 17 '21 at 13:10
  • @Mario, Thanks for responding Mario. Actually, I am able to redirect. My challenge is how to ensure on redirect it picks the copy created. – Just Mar 17 '21 at 13:38
  • how does exactly the code redirect? and what do you mean by redirection? `setActiveSpreadsheet` **does not** open the spreadsheet, it makes it active in the script. – Marios Mar 17 '21 at 14:10
  • There is no method getUsername() in Class User – Cooper Mar 17 '21 at 16:26
  • Have you tried using the `getUrl` method? Do you also have a redirect function, if so, how does it look like? @Just – ale13 Mar 18 '21 at 14:56

1 Answers1

1

The following will automatically open a new tab (or window depending on browser settings) with a url. You will just have to play around to make sure you are calling the right url. Replace SpreadsheetApp.setActiveSpreadsheet(newSpreadsheet) with this:

var url = newSpreadsheet.getUrl()
var html = "<script>window.open('" + url + "');google.script.host.close();</script>";
var userInterface = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(userInterface, "Opening copy");

I realize you say it should happen automatically. I don't think it's possible to do automatically. onOpen runs in a restricted mode which cannot create an HTML output which is the only way I know of to open a link. You will need to add a menu for them to use to start the process.

The only other possibility I can think of is using onOpen and writing the url to a cell. Then they could click the url to open the copied spreadsheet and you could programmatically delete the link.

Luke
  • 183
  • 9