I am trying to log the data, formulas and times of cell edits from one sheet into a logging sheet. The problem I am having is that it only seems to record changes made in the logging sheet. This has started happening since I added the row offset functionality

Below is the code for my onedit and getting the next available cell to record the information.

function onEdit(event)
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Exam");
  var activeRange = activeSheet.getActiveRange();
  var activeCell = activeRange.getA1Notation();
  var cellValue = activeRange.getValue();
  var formula = activeRange.getFormula();
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Logging"); 

  if(activeSheet.getName() != targetSheet.getName()) {

    //Set Values entered in Curret Sheet in Logging sheet for first row
    if(targetSheet.getRange("B4").getValue() == "") {
      targetSheet.getRange("D4").setValue((new Date().toTimeString()));
    else {
      //Set Values entered in Curret Sheet in Logging sheet for every other row
      var nextEmptyRow = getNextEmptyRow(); //Get next available row that we can log to.
      targetSheet.getRange("B" + nextEmptyRow).setValue(activeSheet.getName());
      targetSheet.getRange("C" + nextEmptyRow).setValue(activeCell);
      targetSheet.getRange("D" + nextEmptyRow).setValue((new Date().toTimeString()));
      targetSheet.getRange("F" + nextEmptyRow).setValue(cellValue);
      targetSheet.getRange("G" + nextEmptyRow).setValue(formula);
  //onCellExit(event,activeSheet,activeCell,targetSheet); //Record the values in the logging sheet that were entered in the cell

//Get next available row that we can log to.
function getNextEmptyRow() {
  var time = new Date().getTime();
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var ran = spr.getRange('B:B');
  var arr = [];
  for (var i= ran.getLastRow(); i>0; i--){
  return (Number(i+1));

The second question I have is how do I record the event that occurred in the cell such as value changed or delete range, or update value etc...

1 Answer 1


I think you can do all that, with the code below.


function onEdit(e) { 
  var sName = e.source.getActiveSheet().getSheetName();  
  if(sName !== "Logging") {
    var value;
    var mA1 = e.range.getA1Notation().split(":")[0];
    var time = new Date().toTimeString();    
    if(typeof(e.value) == 'string') {
      var aCell = e.source.getRange(mA1);
      value = aCell.getValue();
      var form = "'" + aCell.getFormula();
    } else {
      value = e.value;      
    var data = [sName, mA1, time, value, form];


The onEdit will only work, if the active sheet isn't named Logging. Next is to gather the wanted info and append it to the logger sheet. The range of the active cell needs to be a cell reference (A1). A formula, with other references, will mix up the A1Notation. Therefore the split(":")[0] will take the first active cell reference (if you add a = or +) as range. If the cell happens to have a formula, then the value is obtained through the e.source else through the e.value. The formula is also obtained through the e.source and stringified.


enter image description here


I've created an example file for you: Logger Sheet


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.