Google Script onedit(e) event restricted to a specific sheet and specific range

user_8652673 0
Viewed 6861

I have scoured and found mostly what I am looking for. I have made a few modifications to what I found and it works... Mostly. BUT when I make an edit in a different sheet in column C it runs the onedit() in the DATA sheet. I do not want that and I can not get it to stop.

I only want it to monitor 'Data' sheet. for ranges C3:C5000 or C3:C+lastrow which would be ideal. There should be no more than 5000 rows.

function onEdit(e) {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data_sheet = ss.getSheetByName("Data");
  var editRange = ss.getActiveRange();
  var editRow = editRange.getRow();
  var editCol = editRange.getColumn();
  var range = data_sheet.getRange("C3:C5000");
  var rangeRowStart = range.getRow();
  var rangeRowEnd = rangeRowStart + range.getHeight();
  var rangeColStart = range.getColumn();
  var rangeColEnd = rangeColStart + range.getWidth();
  if (editRow >= rangeRowStart && editRow <= rangeRowEnd 
      && editCol >= rangeColStart && editCol <= rangeColEnd)
  {
    RefreshImport2();
   }
}
google-apps-script google-sheets
1 Answers

You can retrieve several information for editing by using the event object e of onEdit(e). The detail information for the event object is here. So how about this sample script? In this sample,

When you edit cell on Spreadsheet, if the sheet name, column and row are Data, C and 3 <= row <= 5000, respectively, RefreshImport2() is run.

Sample script :

function onEdit(e) {
  if (
    e.source.getSheetName() == "Data" &&
    e.range.columnStart == 3 &&
    e.range.columnEnd == 3 &&
    e.range.rowStart >= 3 &&
    e.range.rowEnd <= 5000
  ) {
    RefreshImport2();
  }
}

If I misunderstand your question, I'm sorry.

Edit :

You can do it by adding one more if statement. At 1st if statement, it is used when the sheet name is Data. At 2nd if statement, it is used when the sheet name is Data Entry. Each if statement is not interfered with each other.

function onEdit(e) {
  if (
    e.source.getSheetName() == "Data" &&
    e.range.columnStart == 3 &&
    e.range.columnEnd == 3 &&
    e.range.rowStart >= 3 &&
    e.range.rowEnd <= 5000
  ) {
    RefreshImport2();
  }

  if (e.source.getSheetName() == "Data Entry") {
    // do something
  }
}

Is my understanding correct?

user_71086530
Related