CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Julia Ousley
Julia Ousley

Reputation: 11

setNote Last Modified value

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
var range = e.range;
range.setNote('Last modified: ' + new Date()) 
}

This works, but I would like to add another line in the note. I would like to add the last value of the cell that was modified. example, if I change the cell value from .01 to .02. I would like the note to have the new date it was changed AND also the .01 to display in the note. So we have reference of what the value was before it was changed. I tried add this as another line....

range.setNote('Last Recorded Entry: '+ getValue())

but I must have something wrong. or need to combine it with the previous line?

Upvotes: 1

Views: 1186

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

This should do the trick

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
var range = e.range;
var oldValue = e.oldValue;
var noteText = 'Last modified: ' + new Date();
if (oldValue != null){
noteText += 'Last Recorded Entry: ' + oldValue;
}
range.setNote(noteText); 
}

Basically, get the old value of the edited cell using

var oldValues = e.oldValues

Note: Will only work if one cell is modified at a time. Then check to see if it is null => if no previous value was found then dont add that to the note

if (oldValue != null){
noteText += 'Last Recorded Entry: ' + oldValue;
}

Hope that helps

Upvotes: 2

Related Questions