Google Sheets – How to Compare Two Range Objects

google sheets

I have two Range variables. How can I make sure that they belong to the same Sheet and reference the same set of cells?

For example:

var mySheet = // ...
function onEdit(e) {
  var range1 = e.range;
  var range2 = mySheet.getRange(123, 234);
  if (areEqual(range1, range2)) {
    // cell (123, 234) was edited,
    // let's do stuff
  }
}

What should I write instead of areEqual?

Best Answer

Instead of checking row, column, width and height, you can just compare the A1 notations (like A1:B2), in combination with something that identifies the sheet:

function areEqual(r1, r2) {
  return r1.getGridId() === r2.getGridId()
    && r1.getA1Notation() === r2.getA1Notation()
}

range.getGridId() uniquely identifies a sheet the range belongs to (within a spreadsheet).

See the documentation for the Range object, and the getA1Notation() and getGridId() in particular.

You can shorten it even further by just comparing range.getDataSourceUrl():

function areEqual(r1, r2) {
  return r1.getDataSourceUrl() === r2.getDataSourceUrl()
}

Related Solutions

Google-sheets – Change a working function from onOpen() to onEdit()

Yes, if you want a script to be triggered by updates to spreadsheet A, then it should be bound to that spreadsheet. It can still access the other spreadsheets by their URL or Id. For example, this function copies the cells F2:H6 of the present sheet to the cells A1:C5 of another one.

function copyStuff() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('MasterSheet1')
  var ss2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/...');
  var targetSheet = ss2.getSheetByName('Sheet1');
  targetSheet.getRange("A1:C5").setValues(sourceSheet.getRange("F2:H6").getValues());
}

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google-sheets – Change a working function from onOpen() to onEdit()

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Related Topic