Google Sheets: Clone current sheet document to a new document ("Make a copy") from script

Question

Is it possible to make a script that will, so to say, "save as" the current sheet to a new name, making a copy, that can then be immediately opened in a new tab? The programmatic equivalent of File->Make a copy.

Answer 1

You can definitely generate a copy, store it in a defined folder, and display a link that when clicked will open in a new window.

function copyWorkbook() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var name = spreadsheet.getName();
  var spreadsheetId = spreadsheet.getId();
  var todaysDate = new Date().toLocaleString();
  var destFolder = DriveApp.getFolderById("destination_folder_sheet_ID"); 
  var copyFile = DriveApp.getFileById(spreadsheetId).makeCopy(name + ' ' + todaysDate, destFolder); 
  var copyId = copyFile.getId();
  var ui = SpreadsheetApp.getUi();
  var url = `<a href="https://docs.google.com/spreadsheets/d/${copyId}/" target="_blank">${name}</a>`;
  var html = HtmlService.createHtmlOutput(url);
  SpreadsheetApp.getUi().showModalDialog(html, "testing");
}