Google Apps Script: Return row number of newly added row and pass that as a variable to another function

Question

I'm trying to create a script that will automatically move a range of cells down 1 row. The range that gets moved needs to be dependent on where in the sheet a new row was added. I've got the moving the range part figured out with:

function moveRange() {

}
var sheet = SpreadsheetApp.getActiveSheet()
 sheet.getRange("A1:E").moveTo(sheet.getRange("A2"));

I just need sheet.getrange() to be a set column but a variable row number and moveTo(sheet.getRange() to be that same column and the row right below it instead of static values. Any ideas?

Unknown Unknown · Accepted Answer · 2017-01-11T17:59:16

When your script determines row/column numbers algorithmically, it's better to use a different form of getRange, namely getRange(row, col, numRow, numCol). It specifies the row-col coordinates of the upper left corner of the range, and the size of the range.

For example, to move the cells A-E in row x one row down, use

function moveRange() {
  var x = 5;  // or something you compute, or get from the user 
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(x, 1, 1, 5).moveTo(sheet.getRange(x+1, 1, 1, 5));
}

Here, (x, 1, 1, 5) describes the range with upper left corner in row x, column 1 (A), which spans 1 row and 5 columns (A-E).

Google Apps Script: Return row number of newly added row and pass that as a variable to another function

1 Answers