Reputation: 33
i work in insurance, and i have a Google Sheets for all my coworkes where i take note of all policies that we make with external collaborations. I need this: if today a policy expire, the code should send an email to my company addres. Now the code that i used is working, but it only work for one sheet, and send mail even if he finds black spaces. enter image description here
function alertSender() {
var today = new Date();
var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
for(n=1;n<values.length;++n){
var cell_date = values[n][5];
var expired = today > cell_date;
if (expired) {
MailApp.sendEmail(values[n][6], 'Mail automatica', 'Servizio scaduto per il sig. ' + values[n][0]);
Logger.log('Mail inviata all\'indirizzo ' + values[n][6] + ' del sig. ' + values[n][0]);
}
}
}
This is the code i used, where Date (5) is column F and mail (6) is column G.
https://docs.google.com/spreadsheets/d/1JXgpU5QkV3XlhXh6VMVy_sTeDkXr_Egr9lzhfyno8O4/edit?usp=sharing
Upvotes: 2
Views: 121
Reputation: 1
I must be missing something. I'm not getting emails. Also, does Google translate work on the script or will it break the script?
MailApp.sendEmail(values[n][6], 'Mail automatica', 'Servizio scaduto per il >sig. ' + values[n][0]); Logger.log('Mail inviata all'indirizzo ' + values[n][6] + ' del sig. >' + values[n][0]);
Upvotes: 0
Reputation: 15328
Try
function alertSender() {
var today = new Date();
SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
var values = sheet.getDataRange().getValues().filter(r => r[0] != '');
for (n = 1; n < values.length; ++n) {
var cell_date = values[n][5];
var expired = today > cell_date;
if (expired) {
MailApp.sendEmail(values[n][6], 'Mail automatica', 'Servizio scaduto per il sig. ' + values[n][0]);
Logger.log('Mail inviata all\'indirizzo ' + values[n][6] + ' del sig. ' + values[n][0]);
}
}
})
}
references
Upvotes: 1