Google Apps Script to Open copy of Google Sheet in new window

Question

I am trying to boost my Google knowledge by getting into scripting, and I have researched high and low for a solution to my situation to no avail.

I have a Google Sheet that is serving as a template. When the original file is opened, I want to launch a dialog to enter a student ID#. This number will pass into a specified cell in the template, which results in some auto-populated cells in the sheet (using spreadsheet functions). Once the cells have been populated, I want to make a copy of the file with a specific naming structure and store it in a specified folder in Google Drive. Lastly, I want that new file to open in a new window for further editing.

So far, I can get the dialog box to pop up for the ID#, I can pass that number to the sheet, and I can make a copy of the template and rename it accordingly.

What I CANNOT do is get the new file to open automatically in a new tab. I also need the new file to not run the "onOpen" script.

Here is what I have so far for each of these endeavors.

The code below creates a dialog box to enter a Student ID and passes it to the sheet to a specified cell:

    function BuildUI() {
       var app = UiApp.createApplication();
       app.setTitle('Make a Copy - Please enter the Student ID# below:');
       var panel = app.createVerticalPanel();
       var textBox = app.createTextBox();
       textBox.setName('stuId').setId('StudentID');
       var button = app.createButton('Submit');
       panel.add(textBox);
       panel.add(button);
       var clickHandler = app.createServerClickHandler('responses');
       button.addClickHandler(clickHandler);
       clickHandler.addCallbackElement(panel);
       app.add(panel);
       var doc = SpreadsheetApp.getActive();
       doc.show(app);
    }

    function responses(e){
       var app = UiApp.getActiveApplication();
       var textBoxValue = e.parameter.stuId;
       var sheet = SpreadsheetApp.getActiveSheet();
       var studentID = sheet.getRange('Y4').setValue(textBoxValue);
       var dateToday = sheet.getRange('C5').setValue(new Date());
       return app.close();
    }

The above code works for what I need it to do. I only include it for reference (and suggestions for cleaning it up ;)

This next code block makes a copy of the file with the new values, renames it, and saves it to a designated folder in Google Drive. Again..this works for me, but I include it for reference and suggestions:

    function makeCopy(){ 
       var sheet = SpreadsheetApp.getActiveSpreadsheet();
       var firstName = sheet.getRange('O4').getDisplayValue();
       var lastName = sheet.getRange('E4').getDisplayValue();
       var date = sheet.getRange('C5').getDisplayValue();
       var stuId = sheet.getRange('Y4');
       var grade = sheet.getRange('AH4').getDisplayValue();
       var fileName = (lastName+ ", " +firstName+ " - " +stuId+ " - Grade " +grade+ " - " +date);
       var destFolder = DriveApp.getFolderById('folderID'); 

       destFolder.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.VIEW);

       DriveApp.getFileById(sheet.getId()).makeCopy(fileName, destFolder);

      }

Lastly, my onOpen function strings this all together (plus one other function) to run as soon as the template is opened:

    function onOpen(){
       clearCells(); //Clear all content left behind by last editor
       BuildUI();
       makeCopy();
     }

Any help in getting this workflow dialed in is greatly appreciated (most of this I have accomplished already, I just include it for you understanding of my overall needs):

User Interface Dialog Box to capture Student ID (done, but could use suggestions for cleaning up)
Pass ID to cell Y4 and current date to cell C5 (done...just included for understanding of overall need)
Make a copy of the original file and rename it to "Last Name, First Name - StuID - Grade - Date and store in specified Folder in Google Drive (done...just included for understanding of overall need)
NEED -- Open newly created file in a new tab in the browser without running the attached scripts (ideally, the scripts will not be included in the copy if possible)
Bonus points for any help in recreating the UI using the HtmlService in Google scripts since the UI Service is deprecated and functionality may go away the moment I figure all this out.

Thanks in advance for any help on this tall order.

ui service is not going away. you cant automatically open the new spreadsheet, you must instead create an anchor and ask the user to click it. as for the other questions, focus on a single question per post. — Zig Mandel
the UiApp that is referenced in @Adam's question is deprecated. — JSDBroughton

ScottGutman ScottGutman · Accepted Answer · 2018-04-15T11:30:03

You could capture the google file object in the makeCopy() with

gFile = DriveApp.getFileById(sheet.getId()).makeCopy(fileName, destFolder);
return gFile;

then add this function to open a new sheet:

function openNewSheet(gFile) {
  var spreadsheetId = gFile.getId();
  var url = "https://docs.google.com/spreadsheets/d/"+spreadsheetId;
  //SpreadsheetApp.getUi().alert('url is ' + url);
  var html = "<script>window.open('" + url + "');google.script.host.close();</script>";
  var userInterface = HtmlService.createHtmlOutput(html);
   SpreadsheetApp.getUi().showModalDialog(userInterface, "Open Sheet");
  }

Finally you can add openNewSheet() to the end of onOpen()

Some credit goes to this video: https://www.youtube.com/watch?v=2y7Y5hwmPc4

Google Apps Script to Open copy of Google Sheet in new window

1 Answers