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(){
    "1GQtw8xjebZHSgNKvIYvGEal0V0UT4456854X6XTQA4ybQWfqc", //source ID
    "template!B84:Z95", //sourceRange

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

  • Welcome to Web Applications Stack Exchange. Commented Jul 24, 2022 at 7:29
  • It is unclear why you would want to do that with a script when a simple spreadsheet formula would seem to suffice. Please clarify. Commented Jul 24, 2022 at 7:30
  • Tab I want to copy range is template that user needs to fill. Once filled entries will be transferred to another tab which forms type of data base. Hope it clarifies
    – Dariusz
    Commented Jul 24, 2022 at 9:09
  • Hi doubleunary, would you able to help me further to understand what I'm doing wrong? I've answered to my own question with revised code but unfortunately is not working.
    – Dariusz
    Commented Jul 25, 2022 at 18:58
  • Please ask only one question per post. Commented Jul 25, 2022 at 21:26

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

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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.