again Im not very well skilled in google scripts however I have had a lot of support through here so any guidance would be appreciated.

I would like to be able to remind a recipient a process is due for an update by a certain date. Ideally would like it where a reminder is sent 1 month before then again 1 week before the process is due for review.

I would like to be able to send a generic email which the only variables would be the recipients name, email address, due date and the name of the process.

Any ideas on how I could do this or any tutorials that would work. I have attached the link to the google sheet which the emails should be triggered off

https://docs.google.com/spreadsheets/d/1Qw8WefbVkS-AQXi1CcZ0z2CL-P0oNSZYqeT40oVF6go/edit?usp=sharing

CodePudding user response：

I let you customize the text ...

function reminder() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var data = sh.getDataRange().getValues()
  var d = new Date().getTime();
  for (var i=1;i<data.length;i  ){
    if (data[i][4]<=new Date(d 7*24*60*60*1000) && data[i][4]>=new Date(d 5*24*60*60*1000) && data[i][6]!=''){
      MailApp.sendEmail({to:data[i][3],
        subject: 'reminder ... week', 
        htmlBody: 'Hello ' data[i][1] ' The process for ' data[i][0] ' is due to review on ' data[i][4] ' Please review the content and contact the process team before its due date if amendments are required'
      })
      sh.getRange(i 1,7).setValue('sent')
    }
    else if (data[i][4]<=new Date(d 30*24*60*60*1000)  && data[i][4]>=new Date(d 28*24*60*60*1000)  && data[i][5]!=''){
      MailApp.sendEmail({to:data[i][3],
        subject: 'reminer ... month', 
        htmlBody: 'Hello ' data[i][1] ' The process for ' data[i][0] ' is due to review on ' data[i][4] ' Please review the content and contact the process team before its due date if amendments are required'
      })
      sh.getRange(i 1,6).setValue('sent')
    }
  }
}

Then put a trigger on a daily basis