Google Sheets onEdit(e) not capturing multiple edits

0
votes

The onEdit(e) function in Google Sheets Script Editor only captures edits on single cells. Is there a way to make it capture edits on multiple cells, for example, if I copy information into 20 cells, can onEdit(e) recognise that 20 cells have been edited rather than just the first cell?

I've looked everywhere for a solution to this and I can't find any instance of someone asking this question. 

function recordChange(c){
  var range = c.range;
  var column = 8; //Column for change log
  var row = range.getRow();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var oldRow = range.getRow();
  var oldColumn = range.getColumn();

  sheet.getRange(oldRow, oldColumn).setBackground("orange");

  var changeCell = sheet.getRange(row, column)
  changeCell.setValue(new Date())

  var testCell = sheet.getRange(2, 2)
  var test = range.getValues();
  var newT = test.map(function(x){ return x});
  testCell.setValue(newT)

}

function recordAction(c){

  var range = c.range;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var row = range.getRow();

  for (var i = 1; i<9; i++){
    sheet.getRange(row, i).setBackground(null);
  }

}

//Main Function
function onEdit(e){

  var range = e.range;
  if (range.getRow() != 1){
    if (range.getColumn() != 9 && range.getColumn() != 8) {recordChange(e);} else {recordAction(e);}
  }
}

Editing a cell should add the date in a separate column and highlight the cell in orange. This works, however when I try editing multiple cells it just registers the first one, so it is pretty useless as a means to record edits on a sheet.

I'm assuming there was a way of capturing multiple edits in an array, however I can't find any obvious way of doing this.

2
google-apps-scriptgoogle-sheets
The on edit function is working a bit slow. Do you speak from copy and paste multi cell changes or drop down? In my experience there is a time buffer of 0.5 seconds after each on edit change. – PySeeker
Hello, did your issue get solved? Remember that if you want to mark your question as solved you should accept whatever answer provided a solution to your problem. If that's not the case and your issue is not solved, consider explaining why that's not the case so that this community can help you. – Iamblichus

2 Answers

0
votes

Basically your code is not doing what you expect because the ranges you are getting in lines like this one:

sheet.getRange(oldRow, oldColumn).setBackground("orange");

Are just one cell.

When using getRange, you should include the parameters numRows and numColumns if you want the range to include more than one row or column, respectively (see more information here).

Your function recordChange could be like:

function recordChange(c) {
  var range = c.range;
  var ss = c.source; // This returns the spreadsheet from which the event was triggered
  var sheet = ss.getSheets()[0];
  var column = 8; //Column for change log
  var firstRow = range.getRow();
  var numRows = range.getNumRows();
  range.setBackground("orange");
  var dateCells = sheet.getRange(firstRow, column, numRows);
  dateCells.setValue(new Date())
}

Please notice that you don't need to get a new range to change the background color of the edited cells (you have this exact range as property of the event object, as you can see in the code above).

When setting the dates of edition in column 8, you do need to use getRange. As you can see, you need to provide the number of rows the range will have (which corresponds to the number of rows of the edited range), and you get this number of rows by using getNumRows.

Regarding the function recordAction, if I understand you correctly, you want it to remove the background color in the first columns if the respective rows are edited. If that's the case, your function could be like this:

function recordAction(c) {
  var range = c.range;
  var ss = c.source;
  var sheet = ss.getSheets()[0];
  var row = range.getRow();
  var firstRow = range.getRow();
  var firstColumn = 1;
  var numRows = range.getNumRows();
  var numCols = 8;
  var editedRange = sheet.getRange(firstRow, firstColumn, numRows, numCols);
  editedRange.setBackground(null);
}

Here you also need to use getRange, because the range you want to modify is not the same as the one coming from the event object. To do that, you need to provide the appropriate parameters. Again, you can get the number of rows by using getNumRows. And if you want to remove the background for the first 8 columns, you already know both column parameters (1 and 8).

I hope this helps you.

0
votes

You need to use some of the methods within the e.range object.

I wrote a little example code here:

function onEdit(e) {

  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var changes = e.range.getValues();
  // +1 because of header row
  var lastRow = e.range.getLastRow() + 1;
  var date = new Date();
  var dateOfChange = date.toLocaleDateString() + " at " + date.toLocaleTimeString();
  var timeColumn = 4

  //sheet.getDataRange().getLastColumn()
  for (var i = 0; i < changes.length; i++) {
    var row = (lastRow - changes.length) + i;
    var timeCell = sheet.getRange(row, timeColumn);

    //this is quick and dirty but you will probably want to add more advanced data validation
    var validator = 0
    changes[i].forEach(function(x) {x.toString().length > 0 ? validator++ : null;})

    if (validator > 0 && e.range.getLastColumn() < timeColumn) {
      timeCell.setValue(dateOfChange);
    } else {
      timeCell.clear()
    }
  }
}

Just change the time column value to your own timestamp column integer, and make sure the column is after the last column you want to paste data into, else you'll need to write more sophisticated code.

Here's a picture of it working on a simple spreadsheet: Forgot to add any foos or bars