Copy variable Range (based off cell value) to another tab

Question

I'm struggling to set variable range to be copied into another tab in the same sheet. Fixed table range is defined as "Sprzedaż template!B84:Z95". Actually I would like to have that range going from B84 to Z84, when Z84 adds incremental number based off P16 cell value in a sheet (that changes). So for example P16 = 5, then range would be B84:Z88, if P16=12, then range would be B84:Z95 and so on. How do I do that?

Below is script with fixed range value:

function Import(){
  importRange(
    "1GQtw8xjebZHSgNKvIYvGEal0V0UT4456854X6XTQA4ybQWfqc", //source ID
    "template!B84:Z95", //sourceRange
    "1GQtw8xjebZHSgNKvIYvGffdsaEal0V0UTyX6XTQA4ybQWfqc",//destinationID
    "DataBase!B2"//destinationRangeStart
    );
};

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());

  const destRange = destSheet.getRange(
    destStartRange.getRow(), // Start row
    destStartRange.getColumn(), // Start column
    sourceVals.lenght, // Row lenght 
    sourceVals[0].lenght // Col width
    );


    destRange.setValues(sourceVals);
};

Answer 1

To do that with a script, use Range.offset(), like this:

  const sourceSs = SpreadsheetApp.getActive();
  const numRows = sourceSs.getRange('template!P16').getValue();
  const sourceRange = sourceSs.getRange('template!B84:Z84')
    .offset(0, 0, numRows);
  const targetSs = SpreadsheetApp.openByID('...');
  const targetRange = targetSs.getRange('Database!B2:Z2')
    .offset(0, 0, numRows);
  targetRange.setValues(sourceRange.getValues());

If you are doing this within one spreadsheet file, you can do the same with a spreadsheet formula, without requiring a script. Use offset() in cell Database!B2, like this:

=offset(template!B84:Z84, 0, 0, P16)