I made myself a code for Google Sheets that sends me an automatic email whenever a condition is met. I have managed to make this work, but only when I manually edit the sheet - I need it to trigger automatically when the sheet changes itself.

My Sheet manages my subscriptions for my clients, whenever the cell on the first column goes to the number 3 - that row is marked as "impending" - and ideally sends me the email. (The number 3 automatically appears whenever the expiry date of that item is under 2 weeks).

Please let me know if you think you can spot where I haven't set thing up properly :) Thanks!

In Google Apps Script I've tried setting the trigger (but can't get it to be automatic)

Choose which function to run
sendMailEdit

Which runs at deployment
Head

Select event source
From spreadsheet

Select event type
On edit (also tried On change)

And here's my code:

    function sendMailEdit(e){
  if (e.range.columnStart != 1 || e.value !=3) return;
  const rData = e.source.getSheetByName('SUBSCRIPTIONS').getRange(e.range.rowStart,1,1,14).getValues();

  let mis = rData[0][1];
  let dat = new Date(rData[0][2]).toLocaleDateString("en-US");
  let cos = rData[0][3];
  let aut = rData[0][4];
  let typ = rData[0][5];
  let des = rData[0][6];
  let pro = rData[0][7];
  let not = rData[0][8];
  let cnu = rData[0][9];
  let cna = rData[0][10];
  let sta = rData[0][13];
  let now = new Date().toLocaleString("en-US");

  
  let msg = ":: AUTOMATED EMAIL FROM - NP PROJECTS SHEET 2021 / SUBSCRIPIONS PAGE :: "   "\n"   "\n"   sta   " SUBSCRIPTION FOR: "   "\n"   dat   " ("   mis   " days missing)"    "\n"   "Cost: "   cos   "$"   "\n"   "Is autopay active: "   aut   "\n"   "\n"  "CLIENT: "   cna   " ("   cnu   ")"   "\n"   "\n"   "Product: "   typ   "\n"   "\n" "Descprition: "   des    "\n"   "\n"   "Provider: "   pro    "\n"   "\n"  "Notes: "   not    "\n"   "\n"  "\n"   "(This email was triggered at:"    now   ")";
  
  Logger.log(msg);
  GmailApp.sendEmail("[email protected]", "IMPENDING SUBSCRIPTION (automail)", msg)
}

CodePudding user response：

Why not do onEdit():

function onEdit(e){
  const col = e.range.getColumn()
  const sheetName = e.source.getActiveSheet().getName()

  // skip if not col 1 or "My Target Sheet" is the sheet name
  if( col != 1 || sheetName != "My Target Sheet") return

  // here you can get the data and send the Email
  // you may need to call a function sendEmail(){} as I am not sure whether you can trigger an email send in the onEdit(e) function

  
}

However, if the cell in your sheet contains a formula, then this will not work as nobody has actually edited the sheet, i.e. no event has occurred. If this is the case, then I suggest you create a time-trigger which runs daily (or hourly) and checks to see if any rows have a 3 in column a

function report3s(){
  const rows = SpreadsheetApp.getActive().getSheetByName("Sheet1").getDataRange().getValues()
   
   rows.forEach( row => {
     const col_a_value = row[0]
     if( col_a_value != 3 ) return
     
     // do something here
   })
}