Selecting specific range instead of active range in App Script

Question

I have written a script on Google App Script that will, at one point, get a range from one sheet, copy the values on that range and paste them to another sheet.

I have done so by doing this:

    var range = sh.getDataRange().offset(0, 0);  
  var data = range.getValues();
  var ts = SpreadsheetApp.openById('SHEETID').getSheetByName('SHEETNAME')
  ts.getRange(ts.getLastRow() + 1, 1, data.length, data[0].length).setValues(data)

I would like, however, to specify the exact range to copy and another exact range to paste the data into.

I would like the range to be copied/pasted to be from columns A to Z whilst still keeping the getLastRow() in the script.

How would I do that?

Thanks for your help.

By specifying those bounds when acquiring the respective Range variable. Try it first, read documentation for help and the required research, and include the code you come up with in your question. Hint: You'll use the numeric equivalent of col A and col Z — tehhowch

noogui noogui · Accepted Answer · 2018-03-27T12:00:13

You can use getRange(a1Notation) to fetch a range and its contents. I usually go for A1 notation. Sample:

// Get a range A1:D4 on sheet titled "Invoices"
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var range = ss.getRange("Invoices!A1:D4");
 // Get cell A1 on the first sheet
 var sheet = ss.getSheets()[0];
 var cell = sheet.getRange("A1");

There are other variations of getRange you can use. Choose what you're comfortable with.

For copying and pasting altogether, you can use the copyTo(destination)

  // The code below copies the first 5 columns over to the 6th column.
 var sheet = SpreadsheetApp.getActiveSheet();

Selecting specific range instead of active range in App Script

1 Answers