Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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("B4").setValue(activeSheet.getName());
      targetSheet.getRange("C4").setValue(activeCell);
      targetSheet.getRange("D4").setValue((new Date().toTimeString()));
      targetSheet.getRange("F4").setValue(cellValue);
      targetSheet.getRange("G4").setValue(formula);
    }
    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--){
    if(ran.getCell(i,1).getValue()){
      break;
    }
  }
  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...

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

Code

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];
    e.source.getSheetByName("Logging").appendRow(data);
  }
}

Explained

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.

Screenshot

enter image description here

Example

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

share|improve this answer
1  
Thanks for this. Brilliant and simplified. Cheers. –  user2757786 Mar 16 at 18:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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