0

With a script I duplicate the active file and save him in a particular folder on google Drive. Then, I try to open the new file. With the following code the new file is well created but nothing happen after... 

function copy(){

  nom=Browser.inputBox("Nom du magasin ?")
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var destFolder = DriveApp.getFoldersByName("Tests").next();
  DriveApp.getFileById(sheet.getId()).makeCopy(nom, destFolder);
  SpreadsheetApp.open(destFolder.getFilesByName(nom).next());
}

Do you have any idea to get my new file opened instead of the first one ? Thanks a lot !

Hubert Leclerc
  • 3
  • 2

4 Answers4

0

Answer:

You code is correct, aside from the need to assign your opened file to a variable. SpreadsheetApp.open() doesn't actually open the file in the browser, it opens it as a File object for processing in Apps Script.

More Information:

The documentation for the SpreadsheetApp.open() method explains this with the return type of the method:

Return Spreadsheet — the spreadsheet

This is a Spreadsheet object that can be used and read by SpreadsheetApp for in-script processing.

You need to assign the file to a variable, for example:

var file = SpreadsheetApp.open(destFolder.getFilesByName(nom).next());

From here you can edit the sheet the way you need to with methods such as file.getSheets() or file.setCurrentCell().

References:

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
  • Thanks for your answer ! Is there any solution to open it **in the Browser** ? – Hubert Leclerc Mar 27 '20 at 13:30
  • There isn't, unfortunately. Apps Script is cloud-based and so doesn't run on your local machine; it can't tell your browser to open a link because it doesn't have access to your browser. – Rafa Guillermo Mar 27 '20 at 15:28
0

If the original Sheet is open in the browser, then you can get the URL of the new spreadsheet, build an html link, put it into a dialog box, open the dialog box, have code run when the dialog box opens that clicks the link, and then closes the dialog box. So, it's not an easy process, but it's possible.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
0

It's not possible to have the Google Script open the sheet in your browser since a javascript can't open a tab without some interaction from the user.

You can get the ID of the new sheet and use the HTML Service to display a link that will open the sheet in a new tab.

function makeAcopy() {

    var nom = 'New sheet test'
        var sheet = SpreadsheetApp.openById('--- --- --- ')
        var destFolder = DriveApp.getFoldersByName("--- --- ---").next();
    var fileID = DriveApp.getFileById(sheet.getId()).makeCopy(nom, destFolder).getId();

    var htmlOutput = HtmlService
        .createHtmlOutput('<a href="https://docs.google.com/spreadsheets/d/' + fileID + '/" target="_blank">' + nom + '</a>')
        .setWidth(350) //optional
        .setHeight(50); //optional
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Click to open sheet');

}
James D
  • 3,102
  • 1
  • 11
  • 21
0

Thanks a lot James D ! I found also a "full option" code here :

My full code below :

function openUrl( url ){
  var html = HtmlService.createHtmlOutput('<html><script>'
  +'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
  +'var a = document.createElement("a"); a.href="'+url+'"; a.target="_blank";'
  +'if(document.createEvent){'
  +'  var event=document.createEvent("MouseEvents");'
  +'  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'                          
  +'  event.initEvent("click",true,true); a.dispatchEvent(event);'
  +'}else{ a.click() }'
  +'close();'
  +'</script>'
  // Offer URL as clickable link in case above code fails.
  +'<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="'+url+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
  +'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
  +'</html>')
  .setWidth( 90 ).setHeight( 1 );
  SpreadsheetApp.getUi().showModalDialog( html, "Opening ..." );
}

function creationMatriceMag(){

  nom=Browser.inputBox("Nom du magasin ?")
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var destFolder = DriveApp.getFoldersByName("Tests").next();
  DriveApp.getFileById(sheet.getId()).makeCopy(nom, destFolder);
  var newFile=SpreadsheetApp.open(destFolder.getFilesByName(nom).next());
  idNewFile=newFile.getId();
  openUrl('https://docs.google.com/spreadsheets/d/'+idNewFile+'/');


}
Hubert Leclerc
  • 3
  • 2