How to duplicate a sheet on a user-selected target spreadsheet and copy data

Question

I’m very new to Google Apps Script, and I’m having trouble trying to accomplish my goal.

I have a Google Sheets Workbook that allows users to:

1. Select a name from a drop down (each name has a unique/individual google workbook URL associated with it)
2. Type in a desired spreadsheet name
3. Press a “Push Sheet” button

Once the user presses the button, I’m trying to accomplish the following things:

1. Duplicate the sheet 'Template - Do Not Modify’ that already exists on target workbook (the URL associated with the selected name)
2. Rename the duplicated sheet to the desired spreadsheet name
3. Copy the range A7:D150 from the sheet “Tracker” on the original workbook to the range A7:D150 newly created sheet on the target workbook

The original sheet is set up to have the user authorize the workbook connection prior to running the script.

Here's my code:

function cloneGoogleSheet() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var name = sheet.getRange("B10").getValue();
  var url = sheet.getRange("f5").getValue();    
  var tss = SpreadsheetApp.openByUrl(url);


  tss.setActiveSheet(tss.getSheetByName('Template - Do Not Modify'));
  tss.duplicateActiveSheet();

  var activesheet = tss.getActiveSheet();
  activesheet.setName(name);

}

My issues are:

1. It doesn't seem like utilizing ActiveSheets is a safe way to do all of this and that there's a better way.

2. When attempting to use the URL variable (the script runs fine with a hardcoded URL value), I get an invalid argument: URL error. The cell F5 updates to a new URL based on what name is selected from the drop down, using a lookup which references names with unique URLs:

   =lookup(B4, {P71,P72,P73,P74,P75,P76,P77}, {Q71,Q72,Q73,Q74,Q75,Q76,Q77} )

3. Given the fact that I'm using all these ActiveSheet variables, I'm not sure how to get back to my original sheet to copy the ranges.

I would very much appreciate someone showing me the correct way to do this. Thanks!

Answer 1

Have you tried using "getSheetByName"?

Answer 2

Try Armit Agarwal's tutorial on Duplicate a Sheet in Google Spreadsheets:

function cloneGoogleSheet() {

  var name = "labnol";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Template').copyTo(ss);

  /* Before cloning the sheet, delete any previous copy */
  var old = ss.getSheetByName(name);
  if (old) ss.deleteSheet(old); // or old.setName(new Name);

  SpreadsheetApp.flush(); // Utilities.sleep(2000);
  sheet.setName(company);

  /* Make the new sheet active */
  ss.setActiveSheet(sheet);

}