The goal of this function is to copy a new google sheet from the source spreadsheet into a source target spreadsheet. The funcion insertNewSheet () is a minimal reproducible example. In this example for semplicity it run only if there is no the same sheet in target spreadsheet. The problem arises when coping the cell values from source sheet to target sheet: in fact I receive always the same error: "TypeError: Cannot read property 'getRange' of null". I checked the range (Logging log) and really the range is not null. So I am struggling with this problem for many hours and I have not the solution.
function insertNewSheet() {
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = spreadSheet.getSheetByName('Query_Bando');
var nameOfCell= spreadSheet.getSheetByName('Query_Bando').getRange('A2').getValue();
var sourceValues=sourceSheet.getRange("A13").getDataRegion().getValues();
var cellDataRegion=sourceSheet.getRange("A13").getDataRegion();
var lastRowSource= cellDataRegion.getLastRow();
var lastColumnSource= cellDataRegion.getLastColumn();
var tss=SpreadsheetApp.openById("1bh563zHW0WMIcalWP2t3zG1_sK319JuMZwPsWhucKAs");
var ts=tss.getSheetByName(nameOfCell);
//get A1 notation identifying the range
var A1Range = cellDataRegion.getA1Notation();
var SRange = sourceSheet.getRange(A1Range);
tss.insertSheet(nameOfCell);
var SData = SRange.getValues();
//Logger.log(sourceValues);
Logger.log(lastRowSource);
Logger.log(lastColumnSource);
//Logger.log(SRange);
Logger.log(SData);
Logger.log(A1Range);
ts.getRange(A1Range).activate().setValues(SData);
}