Question 334983 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How to get full range of onEdit(e)?

How to get the whole range of the onEdit(e) by Google Sheets script? I have tried the following script:

var sheet = spreadsheet.getActiveSheet();
function onEdit(e){
   var row = e.range.getRow();
   var column = e.range.getColumn();
   sheet.getRange('A1').setNote(range.getRow() + ',' + range.getColumn());
}

Well, it works if I edit only one cell. For example, if I edit cell B2, then it will set the note of cell A1 to be "2,2". But what if I edit multiple cells simultaneously? For example, suppose the values of range(‘A2:B3’) are all non-empty (say, all ‘abc’) and I copy range(‘A2:B3’) and paste it to range(‘A10:B11’). Then, the note of cell A1 will only show "10,1" (i.e., row and column of cell A10). How can I show the WHOLE RANGE (instead of merely the top left cell), for example, "10,1,2,2" or the rows and columns of ‘A10’ and ‘B11’? Thank you!

Tags:

2

Answers


  1. 0 
    var sheet = SpreadsheetApp.getActiveSheet();
function onEdit(e){
   var row = e.range.rowStart;
   var column = e.range.columnStart;
   var endRow = e.range.rowEnd;
   var colEnd = e.range.columnEnd;
   sheet.getRange('A1').setNote(row + ',' + column+','+endRow+','+colEnd);
}

    You can use these variables to get the whole edited range

    enter image description here

    Login or Signup to reply.
  2. 0

    This will put a note in every cell you edit as long as the range is contiguous.

    function onEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0") {
    //e.source.toast("gate1");
    for (let r = e.range.rowStart; r <= e.range.rowEnd; r++) {
      for (let c = e.range.columnStart; c <= e.range.columnEnd; c++) {
        sh.getRange(r, c).setNote(`row:${r},col:${c}`)
      }
    }
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search