Good afternoon,
I'm trying to send email reminder from my google spread sheet. Delegates
book onto a course and they automatically populate the spreadsheet.
The column that the date is sorted by would be.....
05 Avon Rd Business Centre, Cannock Sat 14th, 21st Nov 0930 start06
Newcastle, Wed 14th, 21st Oct 0930 startand so on.......
I need the time driven formula to recognise that the date is within nine
days of the start date and then to send an email reminder. When the email
has been sent a further column (17) will have "mail sent" recorded. Then I
know its been sent and also the sheet will not send any further emails to
this address.
The Column with the date on as seen above is column B or 1, The column with
the email address is I or 8, and the column with the mail sent is R or 17
This has been working well but after a while will stop working for no
apparent reason.
I have included the script for the reminders that I have been using below.
Thanks in advance for your help and advice.
function Reminder2() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getActiveSheet();
var rn=1;
while (s.getRange(rn,2).getValue().length>0) {rn++;}
var rngD=s.getRange(1,1,rn,s.getLastColumn());
var data=rngD.getValues(); //changed the whole loop structure
var mns,mn,m,days,d=new Date(),coursedate=new
Date(),dlen=8.64e7,i,course,r,year,rflag;//8.64e7
//year=d.getFullYear();
var subject="Just a gentle reminder that you or colleague(s) from your
setting have a Paediatric First Aid course coming up in the next week or
so. ";
var recipient, body, tail="Please be aware that Blithfield Safety will
make a charge for non attendance so";
tail+=" please make sure that you familiarise yourself with the times and
dates of the course. ";
tail+="If you need help finding the venue then please contact Lichfield
Safety on 01283 888888 for directions and postcodes";
for (r in data) {
rflag=false;
recipient=data[r][8]; // changed 10 to 8 (it looks like the columns
have changed a bit!
if (recipient.indexOf("@")<1) {continue;}
course=data[r][1];
body=subject+"\nCourse Details\n"+course+"\n\nDelegate Name:
"+data[r][2]+"\n\n"+tail;
mns=course.match(/(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)/gi);
for (mn in mns) {
m=["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].indexOf(mns[mn]);
var i,ix1,ix2;
ix2=course.indexOf(mns[mn]);
ix1=ix2-8;
days=course.slice(ix1,ix2).match(/(\d+)/g);
for (i in days) {
if (rflag) {continue;}
coursedate.setMonth(m);
coursedate.setDate(days[i]);
coursedate.setFullYear(Number(course.match(/20\d{2}/)));
Logger.log(coursedate);
if ((coursedate-d)/dlen<9 && data[r][17]!="mail sent") {
GmailApp.sendEmail(recipient, subject, body);
Logger.log(recipient + '\n'+body);
rflag=true;
}
}
}
if (rflag) {data[r][17]="mail sent";}
}
rngD.setValues(data);
};
--
You received this message because you are subscribed to the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to google-spreadsheets-api+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
