Question

0

227

Views

Google Sheets Script - onEdit Function - Send Email After Two Conditions Are Met

First-time asker, long-time StackOverflow stalker. Here is the situation, I have the script below that sends me an email whenever I enter the correct values in columns 18 and 20, these values can go into any row in the target spreadsheet, and as long as they are in the correct column, an email will be sent. The problem is, I need an email to only be sent after I enter both of the correct values into both of the required cells. Right now, if either condition is met, it sends an email, I need to figure out how to get the script to require both conditions are met before sending the email.

Any ideas?

 function sendMailEdit(e){
        if ((e.range.columnStart != 18 || e.value != "Created") && (e.range.columnStart != 20 || 
    e.value != "Inputted")) return;
    const rData = 
    e.source.getSheetByName('Classified').getRange(e.range.rowStart,1,1,20).getValues();
       let fn = rData[0][5];
       let ln = rData[0][6];
       let ein = rData[0][7];
       let email = rData[0][17];
       let escape = rData[0][19];

    let msg = "Employee " + fn + " " + ln + " has been processed by HR, and can now be further 
    processed by IT";
    Logger.log(msg);
    GmailApp.sendEmail("mhawkins@dnusd.org", "HR Has Processed an Employee", msg)
    }

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-03T16:28:24.729Z

In your situation, how about the following modification?

From:

if ((e.range.columnStart != 18 || e.value != "Created") && (e.range.columnStart != 20 || e.value != "Inputted")) return;

To:

const range = e.range;
if (![18, 20].includes(range.columnStart)) return;
const [r, , t] = range.getSheet().getRange(range.rowStart, 18, 1, 3).getValues()[0];
if (r != "Created" || t != "Inputted") return;

By this modification, when the values of columns "R" and "T" are Created and Inputted, respectively, the script below the if statement is run.

Added:

About your following new question,

If I wanted to add a 3rd condition from row 8 would this look right to you @Tanaike ' const range = e.range; if (![8, 18, 20].includes(range.columnStart)) return; const [ m, , , , , , , , , , r, , t] = range.getSheet().getRange(range.rowStart, 8, 1, 11, 13).getValues()[0]; if (m > "0" || r != "Created" || t != "Inputted") return; '

I want to change the script to send an email after 3 conditions are met. I want to include the first 2 conditions you helped me with, but now add a 3rd condition. The 3rd condition I want is for the value in the 8th column to be greater than 0. Does that make sense?

Yes that is correct, I want to add column H > 0

yes that is correct, do you have any ideas how I could do that?

In this case, how about the following sample script instead of above one?

Sample script:

if (![8, 18, 20].includes(range.columnStart)) return;
const [h,,,,,,,,,,r,,t] = range.getSheet().getRange(range.rowStart, 8, 1, 13).getValues()[0];
if (h <= 0 || r != "Created" || t != "Inputted") return;

0

227