0

I have this script that works perfectly for any edit on the spreadsheet page:

function onEdit(e) {
   if (!e.value) return;
  e.source.getActiveSheet()
       .getRange('F29')
       .setValue(new Date());
}

However, when I try to modify it so that it only works with edits made to cell C29 (which is actually C29:E29 merged; I don't know if that matters), it won't update when there are edits made to that cell:

function onEdit(e) {
   if (!e.value) return;
  e.source.getCell('CONTENTS','C29:E29')
       .getRange('F29')
       .setValue(new Date());
}

I've tried it with both C29 & C29:E29 for getCell; neither works, so there's obviously something wrong and I'm out of ideas for how to make it work correctly. I'm sure there's an easy answer though...

@Rubén - this is the change I tried:

function onEdit(e) {
   if (!e.value) return;
  e.range.getA1Notation() === 'C29'
       .getRange('F29')
       .setValue(new Date());
}

I also tried this just in case it was an issue with the merged cells:

function onEdit(e) {
   if (!e.value) return;
  e.range.getA1Notation() === 'C29:E29'
       .getRange('F29')
       .setValue(new Date());
}

Sheet link

  • It looks that you missed "You could the above as the condition of if, switch among other JavaScript control statements." – Rubén Jul 24 '18 at 16:50
  • I thought it was the condition as it's the next line following my " if (!e.value) return; " line. Should " e.range.getA1Notation() === 'C29' " be inside the brackets of that if statement then instead of a line below it? If that's not it, I don't know what it should be or how to fully implement your suggestion. – Tamarissa Jul 24 '18 at 17:18
1

It's very likely that you are trying to use getCell as it was the same as the CELL built-in spreadsheet function, but it isn't, actually the Class Spreadsheet hasn't a getCell method.

To make a function that only works when C29 is edited use the following condition or something equivalent:

e.range.getA1Notation() === 'C29'

You could use the above as the condition of if...else, switch among other JavaScript control flow statements.

  • Tha would definitely explain why getCell wouldn't work, thanks :). I did try replacing that line with the one you suggested, but that doesn't work either. No changes are made to the 'timestamp' cell when I edit C29. – Tamarissa Jul 23 '18 at 16:50
  • Sorry, thought that was clear... – Tamarissa Jul 24 '18 at 16:34
  • function onEdit(e) { if (!e.value) return; e.range.getA1Notation() === 'C29' .getRange('F29') .setValue(new Date()); } – Tamarissa Jul 24 '18 at 16:35
  • Done... added a link to a tester file as well. – Tamarissa Jul 24 '18 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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