Sending email when a date count down is triggered
1
I am trying to set up a sheet with varying email address and
dates. when a date (due) counts down to 20 say, I'd like to send the
owner of the issue an automatic email. All info in in the same row
just varying columns. I post my semi-functional script here, I can get the correct line item to pull based on date, I just cant get the script to pull the associated email with the date. Or I get a ton of emails that i dont want. Sheet
Any help would be much appreciated!!
function checkReminder() {
// get the spreadsheet object
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// set the first sheet as active
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
// fetch this sheet
var sheet = spreadsheet.getActiveSheet();
// Number of rows to process
var numRows = sheet.getLastRow()-1;
// figure out what the last row is
var lastRow = sheet.getLastRow();
// the rows are indexed starting at 1, and the first row
// is the headers, so start with row 2
var startRow = 2;
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 2 ,lastRow,
sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)
// grab column 20 (the 'days left' column) changed numrow to last r
row
get last row change
var range = sheet.getRange(startRow,20,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();
// Now, grab the reminder name column
range = sheet.getRange(2, 16, lastRow-startRow+1, 1);
var reminder_name_values = range.getValues();
var warning_count = 0;
var msg = "";
//msg = msg + "Trial Reminder Trial: "+reminder_name+" is due in
"+days_left+" days.n"
//for (i in data) {
// var row = data[i];
// First column
// var emailAddress = row[13];
// Recipe column (Priority HIGH)
// var message = row[14];
// var subject = "Reminder CAPA ";
//subject = subject + reminder_name;
// Loop over the days left values
for (var k = 0; k <= numRows-1; k++) {
var days_left = days_left_values[k][0];
if(days_left == 20) {
// if it's exactly 20, do something with the data.
var reminder_name = reminder_name_values[k][0];
msg = msg + "Reminder CAPA: "+reminder_name+" is due in
"+days_left+" days.n";
warning_count++;
}
if(warning_count) {
//MailApp.sendEmail(emailAddress, subject, message);
Logger.log(msg);
}
}}
google-apps-script
asked Nov 25 '18 at 21:17
John John
72
add a comment |
1
I am trying to set up a sheet with varying email address and
dates. when a date (due) counts down to 20 say, I'd like to send the
owner of the issue an automatic email. All info in in the same row
just varying columns. I post my semi-functional script here, I can get the correct line item to pull based on date, I just cant get the script to pull the associated email with the date. Or I get a ton of emails that i dont want. Sheet
Any help would be much appreciated!!
function checkReminder() {
// get the spreadsheet object
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// set the first sheet as active
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
// fetch this sheet
var sheet = spreadsheet.getActiveSheet();
// Number of rows to process
var numRows = sheet.getLastRow()-1;
// figure out what the last row is
var lastRow = sheet.getLastRow();
// the rows are indexed starting at 1, and the first row
// is the headers, so start with row 2
var startRow = 2;
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 2 ,lastRow,
sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)
// grab column 20 (the 'days left' column) changed numrow to last r
row
get last row change
var range = sheet.getRange(startRow,20,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();
// Now, grab the reminder name column
range = sheet.getRange(2, 16, lastRow-startRow+1, 1);
var reminder_name_values = range.getValues();
var warning_count = 0;
var msg = "";
//msg = msg + "Trial Reminder Trial: "+reminder_name+" is due in
"+days_left+" days.n"
//for (i in data) {
// var row = data[i];
// First column
// var emailAddress = row[13];
// Recipe column (Priority HIGH)
// var message = row[14];
// var subject = "Reminder CAPA ";
//subject = subject + reminder_name;
// Loop over the days left values
for (var k = 0; k <= numRows-1; k++) {
var days_left = days_left_values[k][0];
if(days_left == 20) {
// if it's exactly 20, do something with the data.
var reminder_name = reminder_name_values[k][0];
msg = msg + "Reminder CAPA: "+reminder_name+" is due in
"+days_left+" days.n";
warning_count++;
}
if(warning_count) {
//MailApp.sendEmail(emailAddress, subject, message);
Logger.log(msg);
}
}}
google-apps-script
asked Nov 25 '18 at 21:17
John John
72
In order to understand correctly about your situation, can you provide a sample spreadsheet? Of course, please remove your personal information. It will help users think of about your issue.
– Tanaike
Nov 25 '18 at 22:50
I have attached the sheet i am working with.
– John
Nov 26 '18 at 14:03
Thank you for your response. Although in my environment, I couldn't see the shared spreadsheet, other users might be able to see it.
– Tanaike
Nov 26 '18 at 22:35
I have updated the sheets share profile to 'anyone with the link'. I appreciate any help in this project!
– John
Nov 28 '18 at 15:20
add a comment |
1
1
1
I am trying to set up a sheet with varying email address and
dates. when a date (due) counts down to 20 say, I'd like to send the
owner of the issue an automatic email. All info in in the same row
just varying columns. I post my semi-functional script here, I can get the correct line item to pull based on date, I just cant get the script to pull the associated email with the date. Or I get a ton of emails that i dont want. Sheet
Any help would be much appreciated!!
function checkReminder() {
// get the spreadsheet object
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// set the first sheet as active
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
// fetch this sheet
var sheet = spreadsheet.getActiveSheet();
// Number of rows to process
var numRows = sheet.getLastRow()-1;
// figure out what the last row is
var lastRow = sheet.getLastRow();
// the rows are indexed starting at 1, and the first row
// is the headers, so start with row 2
var startRow = 2;
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 2 ,lastRow,
sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)
// grab column 20 (the 'days left' column) changed numrow to last r
row
get last row change
var range = sheet.getRange(startRow,20,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();
// Now, grab the reminder name column
range = sheet.getRange(2, 16, lastRow-startRow+1, 1);
var reminder_name_values = range.getValues();
var warning_count = 0;
var msg = "";
//msg = msg + "Trial Reminder Trial: "+reminder_name+" is due in
"+days_left+" days.n"
//for (i in data) {
// var row = data[i];
// First column
// var emailAddress = row[13];
// Recipe column (Priority HIGH)
// var message = row[14];
// var subject = "Reminder CAPA ";
//subject = subject + reminder_name;
// Loop over the days left values
for (var k = 0; k <= numRows-1; k++) {
var days_left = days_left_values[k][0];
if(days_left == 20) {
// if it's exactly 20, do something with the data.
var reminder_name = reminder_name_values[k][0];
msg = msg + "Reminder CAPA: "+reminder_name+" is due in
"+days_left+" days.n";
warning_count++;
}
if(warning_count) {
//MailApp.sendEmail(emailAddress, subject, message);
Logger.log(msg);
}
}}
google-apps-script
asked Nov 25 '18 at 21:17
John John
72
I am trying to set up a sheet with varying email address and
dates. when a date (due) counts down to 20 say, I'd like to send the
owner of the issue an automatic email. All info in in the same row
just varying columns. I post my semi-functional script here, I can get the correct line item to pull based on date, I just cant get the script to pull the associated email with the date. Or I get a ton of emails that i dont want. Sheet
Any help would be much appreciated!!
function checkReminder() {
// get the spreadsheet object
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// set the first sheet as active
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
// fetch this sheet
var sheet = spreadsheet.getActiveSheet();
// Number of rows to process
var numRows = sheet.getLastRow()-1;
// figure out what the last row is
var lastRow = sheet.getLastRow();
// the rows are indexed starting at 1, and the first row
// is the headers, so start with row 2
var startRow = 2;
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 2 ,lastRow,
sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)
// grab column 20 (the 'days left' column) changed numrow to last r
row
get last row change
var range = sheet.getRange(startRow,20,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();
// Now, grab the reminder name column
range = sheet.getRange(2, 16, lastRow-startRow+1, 1);
var reminder_name_values = range.getValues();
var warning_count = 0;
var msg = "";
//msg = msg + "Trial Reminder Trial: "+reminder_name+" is due in
"+days_left+" days.n"
//for (i in data) {
// var row = data[i];
// First column
// var emailAddress = row[13];
// Recipe column (Priority HIGH)
// var message = row[14];
// var subject = "Reminder CAPA ";
//subject = subject + reminder_name;
// Loop over the days left values
for (var k = 0; k <= numRows-1; k++) {
var days_left = days_left_values[k][0];
if(days_left == 20) {
// if it's exactly 20, do something with the data.
var reminder_name = reminder_name_values[k][0];
msg = msg + "Reminder CAPA: "+reminder_name+" is due in
"+days_left+" days.n";
warning_count++;
}
if(warning_count) {
//MailApp.sendEmail(emailAddress, subject, message);
Logger.log(msg);
}
}}
google-apps-script
google-apps-script
asked Nov 25 '18 at 21:17
John John
72
asked Nov 25 '18 at 21:17
John John
72
edited Nov 26 '18 at 14:11
John
asked Nov 25 '18 at 21:17
John John
72
asked Nov 25 '18 at 21:17
John John
72
asked Nov 25 '18 at 21:17
John John
72
72
In order to understand correctly about your situation, can you provide a sample spreadsheet? Of course, please remove your personal information. It will help users think of about your issue.
– Tanaike
Nov 25 '18 at 22:50
I have attached the sheet i am working with.
– John
Nov 26 '18 at 14:03
Thank you for your response. Although in my environment, I couldn't see the shared spreadsheet, other users might be able to see it.
– Tanaike
Nov 26 '18 at 22:35
I have updated the sheets share profile to 'anyone with the link'. I appreciate any help in this project!
– John
Nov 28 '18 at 15:20
add a comment |
In order to understand correctly about your situation, can you provide a sample spreadsheet? Of course, please remove your personal information. It will help users think of about your issue.
– Tanaike
Nov 25 '18 at 22:50
I have attached the sheet i am working with.
– John
Nov 26 '18 at 14:03
Thank you for your response. Although in my environment, I couldn't see the shared spreadsheet, other users might be able to see it.
– Tanaike
Nov 26 '18 at 22:35
I have updated the sheets share profile to 'anyone with the link'. I appreciate any help in this project!
– John
Nov 28 '18 at 15:20
In order to understand correctly about your situation, can you provide a sample spreadsheet? Of course, please remove your personal information. It will help users think of about your issue.
– Tanaike
Nov 25 '18 at 22:50
In order to understand correctly about your situation, can you provide a sample spreadsheet? Of course, please remove your personal information. It will help users think of about your issue.
– Tanaike
Nov 25 '18 at 22:50
I have attached the sheet i am working with.
– John
Nov 26 '18 at 14:03
I have attached the sheet i am working with.
– John
Nov 26 '18 at 14:03
Thank you for your response. Although in my environment, I couldn't see the shared spreadsheet, other users might be able to see it.
– Tanaike
Nov 26 '18 at 22:35
Thank you for your response. Although in my environment, I couldn't see the shared spreadsheet, other users might be able to see it.
– Tanaike
Nov 26 '18 at 22:35
I have updated the sheets share profile to 'anyone with the link'. I appreciate any help in this project!
– John
Nov 28 '18 at 15:20
I have updated the sheets share profile to 'anyone with the link'. I appreciate any help in this project!
– John
Nov 28 '18 at 15:20
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472068%2fsending-email-when-a-date-count-down-is-triggered%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472068%2fsending-email-when-a-date-count-down-is-triggered%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
In order to understand correctly about your situation, can you provide a sample spreadsheet? Of course, please remove your personal information. It will help users think of about your issue.
– Tanaike
Nov 25 '18 at 22:50
I have attached the sheet i am working with.
– John
Nov 26 '18 at 14:03
Thank you for your response. Although in my environment, I couldn't see the shared spreadsheet, other users might be able to see it.
– Tanaike
Nov 26 '18 at 22:35
I have updated the sheets share profile to 'anyone with the link'. I appreciate any help in this project!
– John
Nov 28 '18 at 15:20