trying to copy template sheet into newly created sheet via apps script-CodePudding

I have a script where if a condition is met it dynamically creates a new google sheet via the parameters I want. Once the file is created I want to copy a template sheet into the newly created sheet. When the script runs it creates the sheet file with the proper name. The problem is that it will not copy my template in. Can someone please review and see what I am missing? Is it possible that the new sheet URL is not getting passed on?

    function createNewSheet(e){
var rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,15).getValues();
var jbt1 = rData[0][0];
var name = jbt1;
var crNew = SpreadsheetApp.create("Job Traveler-" name);
var ssNew = SpreadsheetApp.openByUrl(crNew.getUrl());


crNew;


importRange(
  "1X4iOzc_shcOR8UM7OEM4F1-eR62YZmwKWyyEuu59Pf4",
  "Job!A1:D",ssNew,
  "Sheet1!A1"
);
}

function importRange(sourceId,sourceRange,destinationID,destinationRangeStart){

  const sourceSS = SpreadsheetApp.openById(sourceId);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVals = sourceRng.getValues();

  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destinationSS.getSheetByName(destStartRange.getSheet().getName());

  destSheet.clear();

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVals.length,
    sourceVals[0].length
  );

  destRange.setValues(sourceVals);



}

Best

CodePudding user response：

Try this:

function importRange(sourceId, sourceRange, destinationID, destinationRangeStart) {
  const sss = SpreadsheetApp.openById(sourceId);
  const vs = sss.getRange(sourceRange).getValues();
  const dss = SpreadsheetApp.openById(destinationID);
  const dsh = dss.getRange(destinationRangeStart).getSheet();
  dsh.clear();
  dsh.getRange(destStartRange.getRow(), destStartRange.getColumn(), vs.length, vs[0].length).setValues(vs);
}

function createNewSheet(e) {
  const sh = e.range.getSheet();
  var name = sh.getRange(e.range.rowStart, 1).getValue();
  var crNew = SpreadsheetApp.create("Job Traveler-"   name);
  var ssNew = SpreadsheetApp.openByUrl(crNew.getUrl()).getId();
  importRange("1X4iOzc_shcOR8UM7OEM4F1-eR62YZmwKWyyEuu59Pf4","Job!A1:D", ssNew,"Sheet1!A1");
}

CodePudding user response：

Here is the solution. Please note this does not copy formatting.

function createNewSheet(e) {
  const sh = e.range.getSheet();
  var name = sh.getRange(e.range.rowStart, 1).getValue();
  var crNew = SpreadsheetApp.create("Job Traveler-"   name);
  var ssNew = SpreadsheetApp.openByUrl(crNew.getUrl()).getId();
  importRange("1X4iOzc_shcOR8UM7OEM4F1-eR62YZmwKWyyEuu59Pf4","Job!A1:D", ssNew,"Sheet1!A1");
}



function importRange(sourceId,sourceRange,destinationID,destinationRangeStart){

  const sourceSS = SpreadsheetApp.openById(sourceId);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVals = sourceRng.getValues();

  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destinationSS.getSheetByName(destStartRange.getSheet().getName());

  destSheet.clear();

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVals.length,
    sourceVals[0].length
  );

  destRange.setValues(sourceVals);

.getId(); is what was missing. Thank you for your time.