google apps script - Find text in column and send email only one time when found -
i want make google script searches text (given in cell c2) in column , send e-mail (only once) addres given in cell c3 when text found.
column exists of text results importxml formula.
i have searched , searched cant find it, , im of coding newb started :( appreciated.
below example of how can done, used background color mark cells in column mail has been sent (to avoid multiple sending).
it sends mail spreadsheet user , address in c3 can of course adapt needs.
// test sendmailoncondition function sendmailoncondition(){ var sh = spreadsheetapp.getactivesheet(); var condition = sh.getrange("c2").getvalue(); var email = sh.getrange("c3").getvalue(); logger.log(condition); var valuesincolumna = sh.getrange("a1:a").getvalues(); var colorsincolumna = sh.getrange("a1:a").getbackgrounds(); (var n=0;n<valuesincolumna.length;n++){ logger.log(colorsincolumna[n][0]); logger.log(valuesincolumna[n][0]); if(valuesincolumna[n][0]==condition && colorsincolumna[n][0]=='#ffffff'){ colorsincolumna[n][0]="#ddd"; mailapp.sendemail(session.getactiveuser().getemail(),"test mail","row number "+number(n+1)+" has value "+condition);// remove line if don't need mailapp.sendemail(email,"test mail","row number "+number(n+1)+" has value "+condition); } } sh.getrange("a1:a").setbackgrounds(colorsincolumna);// update cells colors }
edit : safety , avoid sending many emails erroneously when cell c2 empty (which happened me while testing ;-) can add third condition 'if' statement : condition !=""
the line becomes :
if(condition !="" && valuesincolumna[n][0]==condition && colorsincolumna[n][0]=='#ffffff'){
Comments
Post a Comment