Google-sheets – Show the content of an edited cell in a message box above

google sheetsgoogle-apps-script

I have a little function for controlling the values of my script:

function onEdit(event) {
  var rowIndex = event.source.getActiveCell().getRowIndex();
  var columnIndex = event.source.getActiveCell().getColumnIndex();
  var status = event.source.getActiveCell().getValue();
  Browser.msgBox("Spalte: "+String(rowIndex) + " Zeile: " + String(columnIndex));
  Browser.msgBox("inhalt: "+String(status));
}

It is a on edit function which gives me the position of my edited cell and the content. (I have the variable "status" for the content or value of the active cell).

My question is: how do I have to change the function if I want to have the content of the previous cell in the same column?

  • I tried to navigate directly with: +String(H2)); (no result)
  • I tried to go with: +String(status)-1); (no result)

Is it necessary to have another variable first which gives me the value of the previous cell?

Best Answer

Use the offset method to refer to the previous cell: offset by -1 rows, 0 columns. An example is below, where I also check whether the active cell is in the top row (in which case there is no previous cell). I also made a couple of cosmetic changes to shorten the code. 

function onEdit(event) {
  var activeCell = event.source.getActiveCell();
  var rowIndex = activeCell.getRowIndex();
  var columnIndex = activeCell.getColumnIndex();
  var status = activeCell.getValue();
  Browser.msgBox("Spalte: " + rowIndex + " Zeile: " + columnIndex);
  Browser.msgBox("inhalt: " + status);
  if (rowIndex > 1) {
    var previousStatus = activeCell.offset(-1, 0).getValue();
    Browser.msgBox(previousStatus);
  }
}
Related Topic