0

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);
};
5
  • Welcome to Web Applications Stack Exchange. Jul 24 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. Jul 24 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
    Jul 24 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
    Jul 25 at 18:58
  • Please ask only one question per post. Jul 25 at 21:26

1 Answer 1

0

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)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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