Adding additional criteria in if statement

Question

Struggling with these & feel like I've put together all the possible combinations, just not fully understanding.

I have this amazing script that records the timestamp when a checkbox turns true in the same row (just a different column).

I am trying to in essence, add another level to it, where I can set the status for a DIFFERENT column, and then record a timestamp for that as well.

In other words, this is the working script (one if):

    function onEdit(e) {
      const sheet = e.range.getSheet();
    
    
    
      if (sheet.getName() !== 'TEST SHEET'
        || sheet.getRange('P' + e.range.rowStart).getValue() !== 'In Progress') {
        return;
      }
      sheet.getRange('AK' + e.range.rowStart)
        .setValue(new Date())
        .setNumberFormat('MM/dd/yyyy');
}

I'm trying to make it something like:

function onEdit(e) {
  const sheet = e.range.getSheet();



  if (sheet.getName() !== 'TEST SHEET'
    || sheet.getRange('P' + e.range.rowStart).getValue() !== 'In Progress') {
    return;
  }
  sheet.getRange('AK' + e.range.rowStart)
    .setValue(new Date())
    .setNumberFormat('MM/dd/yyyy');

    
  if (sheet.getName() !== 'TEST SHEET'
    || sheet.getRange('P' + e.range.rowStart).getValue() !== 'Completed') {
    return;
  }
  sheet.getRange('AL' + e.range.rowStart)
    .setValue(new Date())
    .setNumberFormat('MM/dd/yyyy');
    
}

Doing some research, seems like nothing would work after "return".. I tried adding in the fields before, and did some testing moving everything around but still now luck.

Is there something simple I'm missing here?

Provide debugging information. See execution logs in script editor: It should show why. — TheMaster, Aug 23 '22 at 23:51

CertainPerformance · Accepted Answer · 2022-08-23T23:51:16.650

2

I'd add a parameter for each value that may change (the range to set, and the value to compare against), then call that function twice.

function tryInsertDate(e, rangeToSet, val) {
    const sheet = e.range.getSheet();
    if (
        sheet.getName() !== 'TEST SHEET'
        || sheet.getRange('P' + e.range.rowStart).getValue() !== val
    ) {
        return;
    }
    sheet.getRange(rangeToSet + e.range.rowStart)
        .setValue(new Date())
        .setNumberFormat('MM/dd/yyyy');
}
function onEdit(e) {
    tryInsertDate(e, 'AK', 'In Progress');
    tryInsertDate(e, 'AL', 'Completed');
}

If those are states, you could name the parameter state to be more precise.

edited Aug 23 '22 at 23:51

answered Aug 23 '22 at 22:17

CertainPerformance

356,069
52
309
320

`onEdit` is a triggered function: If a edit happens in a sheet, the function named `onEdit` is automatically called with the event object `e`. So, you should probably rename your refactored function to something else and enclose the two function calls in another function named ``onEdit`` – TheMaster Aug 23 '22 at 23:48
@TheMaster it just says "TypeError: Cannot read property 'range' of undefined" when I try to run, regarding your other comment. – Anthony Madle Aug 24 '22 at 00:02
@AnthonyMadle Did you pass the parameter along in the `onEdit` function? It sounds like you're doing something like `tryInsertDate()` instead of `tryInsertDate(e, 'AK', 'In Progress')` (or maybe you're not defining a parameter for `onEdit`) – CertainPerformance Aug 24 '22 at 00:03
1

@AnthonyMadle See https://stackoverflow.com/questions/16089041/how-ca You can't call onEdit directly. You need to make a edit. The updated script above should work. (But I think your script in question should also work) – TheMaster Aug 24 '22 at 00:04
2

I thought the problem with the script in the question was that `return` would terminate the whole function, instead of possibly going on to check whether the `AL` section needs a timestamp. Using separate functions would mean that `return` inside one wouldn't break the other. (Another option is to iterate over an array of ranges / values to check inside `onEdit`) – CertainPerformance Aug 24 '22 at 00:07
@TheMaster this is amazing - working like a charm. I don't fully understand how to fix mine - wish I could figure that out. Going to spend some more time on it, but nice to see how you were able to accomplish. Kudos!! – Anthony Madle Aug 24 '22 at 00:08
@CertainPerformance based on my reading, this is what I thought too - I just was having trouble tweaking (I'm very new to this). Makes much more sense though now, great input – Anthony Madle Aug 24 '22 at 00:09
@CertainPerformance There's no explanation for why you suggested those changes in the answer. You're right. I completely missed that the `if` condition referred to different strings. Your answer would benefit greatly, if you added that explanation explicitly in the answer itself. – TheMaster Aug 24 '22 at 07:31

Adding additional criteria in if statement

1 Answers1