How can I create spreadsheet and open it Automatic

Question

I have a model of spreadsheet, and I want to automatically duplicate the sheet in this model to a new spreadsheet (not in the same spreadsheet). This new spreadsheet must be automatically opened and saved in my Google Drive. How can I do this?

I started with this code but it doesn't work

function CreateReports()
{    

    var ssNew = SpreadsheetApp.create(date);
    var url = ssNew.getUrl()
    var ss = SpreadsheetApp.openByUrl(url);

    Logger.log(ssNew.getUrl());

}

Answer 1

If you want to duplicate the entire spreadsheet, see the example in the documentation for Spreadsheet.copy().

This script will:

• Create a new spreadsheet, named by the current date.
• Copy the model sheet to the new spreadsheet
• Rename the copied model sheet (again, with current date)
• Return the new Sheet Object ("opens" it, if you will)

code

function copyModel() {
  // Get today's date as a string
  var date = (new Date()).toDateString();

  // Get our model sheet
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var model = source.getSheetByName("model");

  // Create a new spreadsheet on Drive, named by date
  var destination = SpreadsheetApp.create(date);

  // Copy and rename the model sheet into new spreadsheet
  var newSheet = model.copyTo(destination).setName(date);
  return newSheet;
}

Answer 2

If the script is running as the end user (which it does automatically from the Script Editor), the any create or copy of a spreadsheet ends up in the Drive without any additional steps.

Its not possible to automatically open a Spreadsheet or any window for that matter. The user will have to click on the URL after the create/copy is made.

So in order to the spreadsheet UI, you have to show the URL from getUrl in a popup or email it to the user and they can click in.