Google Script -- Insert sheet with multiple ranges

Question

I have a google spreadsheet (link below) that covers a single month, then I'm inserting a sheet for the next month with only a few of the ranges as carry over data.

https://docs.google.com/spreadsheets/d/12zJnE1J5zbnArqMUeFfq2Kk5zvyqxXA0Tgp4Y7Efzes/edit?usp=sharing

What I would to do is insert multiple ranges to different ranges, so that namestat would be placed in C3:U4 and carryover would be placed in C17:C18, date (+7) in B5, etc.

function TDCopyV3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var sheetName = ss.getName();
  var namestat = ss.getRange("C3:U4").getValues();
  var carryover = ss.getRange("C17:U18").getValues();
  var date = ss.getRange("B13").getValue();
  var templateSheet = ss.getSheetByName('Template1');
  ss.insertSheet(sheetName +1, 0, {template: templateSheet}).getRange(3,3,namestat.length,namestat[0].length).setValues(namestat);
}

This is where I'm getting stuck, as I can't seem to find anything on inserting multiple partial ranges. I've attempted chaining another getRange and setData, but that only results in a error.

ss.insertSheet(sheetName +1, 0, {template: templateSheet}).getRange(3,3,namestat.length,namestat[0].length).setValues(namestat).getRange(3,25,carryover.length,carryover[0].length).setValues(carryover);

Ed Nelson Ed Nelson · Accepted Answer · 2016-02-12T22:56:07

Try this. You have to reference the inserted shee to set its values:

function TDCopyV3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("May 2014");
  var sheetName = ss.getName();
  var namestat = s.getRange("C3:U4").getValues();
  var carryover = s.getRange("C17:U18").getValues();
  var date = s.getRange("B13").getValue();
  var templateSheet = ss.getSheetByName('Template1');
  ss.insertSheet(sheetName +1, 0, {template: templateSheet})
  var shnew =ss.getSheetByName("Copy of HoursDemo1")
  var shnewVal=  shnew.getRange(3,3,namestat.length,namestat[0].length).getValues()
  shnew.getRange(3,3,namestat.length,namestat[0].length).setValues(namestat)
  shnew.getRange(17,3,carryover.length,19).setValues(carryover);
}

Google Script -- Insert sheet with multiple ranges

1 Answers