Convert formulas to results in two sheets within one spreadsheet

Question

I have two sheets in one spreadsheet. Both original sheets contain formulas. I need the results of each sheet copied over to new sheets as values. I wrote a small routine to do this, but I'm getting an error, and can't find the cause. The error is in the line var targetRange2 = targetSheet2.getRange("a1:h10");

I researched SO, and reviewed the code multiple times for typos, structural and coding errors.

function testCopyValuesAndFormat() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  ss.insertSheet("newSheet1");

  var sourceSheet1 = SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak").getSheetByName("first");
  var sourceRange1 = sourceSheet1.getRange("a1:h10");
  var targetSheet1 = SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak").getSheetByName("newSheet1");
  var targetRange1 = targetSheet1.getRange("a1:h10");

  targetRange1.setValues(sourceRange1.getValues());
  sourceRange1.copyTo(targetRange1, {formatOnly:true});

  ss.insertSheet("newSheet2");

  var sourceSheet2 = SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak").getSheetByName("second");
  var sourceRange2 = sourceSheet2.getRange("a1:h10");
  var targetSheet2 = SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak").getSheetByName("newSheet2");
  var targetRange2 = targetSheet2.getRange("a1:h10");

  targetRange2.setValues(sourceRange2.getValues());
  sourceRange2.copyTo(targetRange2, {formatOnly:true});

}

I expected to have 4 sheets in my spreadsheet - "first", "second", "newSheet1", and "newSheet2". That's not happening. The 2 new sheets ("newSheet1" and "newSheet2") are both created properly, and sheet contents are transferred properly from "first" to "newSheet 1", but the contents of the second sheet ("second") never transfer to "newSheet2".

I get an error at the line "var targetRange2 = targetSheet2.getRange("a1:h10");", saying "TypeError: Cannot call method "getRange" of null. (line 20, file "Code").

Cooper Cooper · Accepted Answer · 2019-09-01T16:15:33

newSheet2 is in SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak") is that the same as ss

Perhaps this might work.

function testCopyValuesAndFormat() {
  var ss1=SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak");
  ss1.insertSheet("newSheet1");
  var sourceSheet1 = ss1.getSheetByName("first");
  var sourceRange1 = sourceSheet1.getRange("a1:h10");
  var targetSheet1 = ss1.getSheetByName("newSheet1");
  var targetRange1 = targetSheet1.getRange("a1:h10");
  targetRange1.setValues(sourceRange1.getValues());
  sourceRange1.copyTo(targetRange1, {formatOnly:true});
  var ss2=SpreadsheetApp.openById("10TzfBrSdQA_IPtLNvgpOHU-UaInTmM8xUpfBjYybDak");
  ss2.insertSheet("newSheet2");
  var sourceSheet2 = ss2.getSheetByName("second");
  var sourceRange2 = sourceSheet2.getRange("a1:h10");
  var targetSheet2 = ss2.getSheetByName("newSheet2");
  var targetRange2 = targetSheet2.getRange("a1:h10");
  targetRange2.setValues(sourceRange2.getValues());
  sourceRange2.copyTo(targetRange2, {formatOnly:true});
}

Convert formulas to results in two sheets within one spreadsheet

1 Answers