Google Script email send ignoring #N/A values in row
Essentially i'm also pulling email addresses from a separate sheet via a formula so that when new form entry is made, column B populates the proper email (name is included in the form). The problem I'm facing is that for the tool to be efficient I need to run the formula all the way down indefinitely so that when new entry comes it simply looks up the name and column B pulls the correct email address. However, the below code returns an error because it cannot handle #N/A where no email exists yet or even "" with IFERROR in the formula.
Ideally the code will stop if #N/A is encountered and only continues when there is an email in CurrentRow[1] has an email. The code works perfectly fine if empty rows are totally blank.
The current version here comes from what I've read and researched, including the post: Email only new rows in Google sheets. I've researched and lots of similar examples exist but having tried 40 or so variations i could not get mine to work.
function sendEmail()
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues)
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var KamEmail = CurrentRow[1];
//if row has been sent, then continue to next iteration
if (CurrentRow[1] == "#N/A") break;
else;
//set HTML template for information
message +=
"<p>Hi " + CurrentRow[1] + "</p>" +
"<p>Your constituent has submitted an Agenda blog pitch" + "</p>" +
"<p><b>Name of constituent: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Submission Snapshot: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Link to Submission: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Date of Submission: </b>" + CurrentRow[7] + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
//define who to send grants to
var SendTo = CurrentRow[1];
//set subject line
var Subject = "RE: Registrations";
//send the actual email
MailApp.sendEmail(
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
);
javascript google-apps-script
asked Nov 14 '18 at 16:02
user10652939user10652939
42
add a comment |
Essentially i'm also pulling email addresses from a separate sheet via a formula so that when new form entry is made, column B populates the proper email (name is included in the form). The problem I'm facing is that for the tool to be efficient I need to run the formula all the way down indefinitely so that when new entry comes it simply looks up the name and column B pulls the correct email address. However, the below code returns an error because it cannot handle #N/A where no email exists yet or even "" with IFERROR in the formula.
Ideally the code will stop if #N/A is encountered and only continues when there is an email in CurrentRow[1] has an email. The code works perfectly fine if empty rows are totally blank.
The current version here comes from what I've read and researched, including the post: Email only new rows in Google sheets. I've researched and lots of similar examples exist but having tried 40 or so variations i could not get mine to work.
function sendEmail()
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues)
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var KamEmail = CurrentRow[1];
//if row has been sent, then continue to next iteration
if (CurrentRow[1] == "#N/A") break;
else;
//set HTML template for information
message +=
"<p>Hi " + CurrentRow[1] + "</p>" +
"<p>Your constituent has submitted an Agenda blog pitch" + "</p>" +
"<p><b>Name of constituent: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Submission Snapshot: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Link to Submission: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Date of Submission: </b>" + CurrentRow[7] + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
//define who to send grants to
var SendTo = CurrentRow[1];
//set subject line
var Subject = "RE: Registrations";
//send the actual email
MailApp.sendEmail(
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
);
javascript google-apps-script
asked Nov 14 '18 at 16:02
user10652939user10652939
42
1
Remove the semi colon after else; and I would suggest you wrap what you want as the else block in curly braces else whateveryouwant . I'm not sure how much you want in it. And it looks like the curly braces for your for loop are missplaced after "Sent do KAM". Maybe not, I'm not sure.
– TheWizEd
Nov 14 '18 at 16:42
Thanks for your comment. I tried that but I still have the same issue. Hopefully others can help!
– user10652939
Nov 15 '18 at 8:00
add a comment |
Essentially i'm also pulling email addresses from a separate sheet via a formula so that when new form entry is made, column B populates the proper email (name is included in the form). The problem I'm facing is that for the tool to be efficient I need to run the formula all the way down indefinitely so that when new entry comes it simply looks up the name and column B pulls the correct email address. However, the below code returns an error because it cannot handle #N/A where no email exists yet or even "" with IFERROR in the formula.
Ideally the code will stop if #N/A is encountered and only continues when there is an email in CurrentRow[1] has an email. The code works perfectly fine if empty rows are totally blank.
The current version here comes from what I've read and researched, including the post: Email only new rows in Google sheets. I've researched and lots of similar examples exist but having tried 40 or so variations i could not get mine to work.
function sendEmail()
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues)
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var KamEmail = CurrentRow[1];
//if row has been sent, then continue to next iteration
if (CurrentRow[1] == "#N/A") break;
else;
//set HTML template for information
message +=
"<p>Hi " + CurrentRow[1] + "</p>" +
"<p>Your constituent has submitted an Agenda blog pitch" + "</p>" +
"<p><b>Name of constituent: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Submission Snapshot: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Link to Submission: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Date of Submission: </b>" + CurrentRow[7] + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
//define who to send grants to
var SendTo = CurrentRow[1];
//set subject line
var Subject = "RE: Registrations";
//send the actual email
MailApp.sendEmail(
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
);
javascript google-apps-script
asked Nov 14 '18 at 16:02
user10652939user10652939
42
Essentially i'm also pulling email addresses from a separate sheet via a formula so that when new form entry is made, column B populates the proper email (name is included in the form). The problem I'm facing is that for the tool to be efficient I need to run the formula all the way down indefinitely so that when new entry comes it simply looks up the name and column B pulls the correct email address. However, the below code returns an error because it cannot handle #N/A where no email exists yet or even "" with IFERROR in the formula.
Ideally the code will stop if #N/A is encountered and only continues when there is an email in CurrentRow[1] has an email. The code works perfectly fine if empty rows are totally blank.
The current version here comes from what I've read and researched, including the post: Email only new rows in Google sheets. I've researched and lots of similar examples exist but having tried 40 or so variations i could not get mine to work.
function sendEmail()
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues)
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var KamEmail = CurrentRow[1];
//if row has been sent, then continue to next iteration
if (CurrentRow[1] == "#N/A") break;
else;
//set HTML template for information
message +=
"<p>Hi " + CurrentRow[1] + "</p>" +
"<p>Your constituent has submitted an Agenda blog pitch" + "</p>" +
"<p><b>Name of constituent: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Submission Snapshot: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Link to Submission: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Date of Submission: </b>" + CurrentRow[7] + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
//define who to send grants to
var SendTo = CurrentRow[1];
//set subject line
var Subject = "RE: Registrations";
//send the actual email
MailApp.sendEmail(
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
);
function sendEmail()
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues)
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var KamEmail = CurrentRow[1];
//if row has been sent, then continue to next iteration
if (CurrentRow[1] == "#N/A") break;
else;
//set HTML template for information
message +=
"<p>Hi " + CurrentRow[1] + "</p>" +
"<p>Your constituent has submitted an Agenda blog pitch" + "</p>" +
"<p><b>Name of constituent: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Submission Snapshot: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Link to Submission: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Date of Submission: </b>" + CurrentRow[7] + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
//define who to send grants to
var SendTo = CurrentRow[1];
//set subject line
var Subject = "RE: Registrations";
//send the actual email
MailApp.sendEmail(
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
);
function sendEmail()
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
var message = "";
//iterate loop
for (i in AllValues)
//set current row
var CurrentRow = AllValues[i];
//define column to check if sent
var KamEmail = CurrentRow[1];
//if row has been sent, then continue to next iteration
if (CurrentRow[1] == "#N/A") break;
else;
//set HTML template for information
message +=
"<p>Hi " + CurrentRow[1] + "</p>" +
"<p>Your constituent has submitted an Agenda blog pitch" + "</p>" +
"<p><b>Name of constituent: </b>" + CurrentRow[2] + "</p>" +
"<p><b>Submission Snapshot: </b>" + CurrentRow[3] + "</p>" +
"<p><b>Link to Submission: </b>" + CurrentRow[4] + "</p>" +
"<p><b>Date of Submission: </b>" + CurrentRow[7] + "</p><br><br>";
//set the row to look at
var setRow = parseInt(i) + StartRow;
//mark row as "sent"
ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
//define who to send grants to
var SendTo = CurrentRow[1];
//set subject line
var Subject = "RE: Registrations";
//send the actual email
MailApp.sendEmail(
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
);
javascript google-apps-script
javascript google-apps-script
asked Nov 14 '18 at 16:02
user10652939user10652939
42
asked Nov 14 '18 at 16:02
user10652939user10652939
42
edited Nov 14 '18 at 16:21
user10652939
asked Nov 14 '18 at 16:02
user10652939user10652939
42
asked Nov 14 '18 at 16:02
user10652939user10652939
42
asked Nov 14 '18 at 16:02
user10652939user10652939
42
42
1
Remove the semi colon after else; and I would suggest you wrap what you want as the else block in curly braces else whateveryouwant . I'm not sure how much you want in it. And it looks like the curly braces for your for loop are missplaced after "Sent do KAM". Maybe not, I'm not sure.
– TheWizEd
Nov 14 '18 at 16:42
Thanks for your comment. I tried that but I still have the same issue. Hopefully others can help!
– user10652939
Nov 15 '18 at 8:00
add a comment |
1
Remove the semi colon after else; and I would suggest you wrap what you want as the else block in curly braces else whateveryouwant . I'm not sure how much you want in it. And it looks like the curly braces for your for loop are missplaced after "Sent do KAM". Maybe not, I'm not sure.
– TheWizEd
Nov 14 '18 at 16:42
Thanks for your comment. I tried that but I still have the same issue. Hopefully others can help!
– user10652939
Nov 15 '18 at 8:00
1
1
Remove the semi colon after else; and I would suggest you wrap what you want as the else block in curly braces else whateveryouwant . I'm not sure how much you want in it. And it looks like the curly braces for your for loop are missplaced after "Sent do KAM". Maybe not, I'm not sure.
– TheWizEd
Nov 14 '18 at 16:42
Remove the semi colon after else; and I would suggest you wrap what you want as the else block in curly braces else whateveryouwant . I'm not sure how much you want in it. And it looks like the curly braces for your for loop are missplaced after "Sent do KAM". Maybe not, I'm not sure.
– TheWizEd
Nov 14 '18 at 16:42
Thanks for your comment. I tried that but I still have the same issue. Hopefully others can help!
– user10652939
Nov 15 '18 at 8:00
Thanks for your comment. I tried that but I still have the same issue. Hopefully others can help!
– user10652939
Nov 15 '18 at 8:00
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
);
);
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%2f53304284%2fgoogle-script-email-send-ignoring-n-a-values-in-row%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
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.
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%2f53304284%2fgoogle-script-email-send-ignoring-n-a-values-in-row%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
1
Remove the semi colon after else; and I would suggest you wrap what you want as the else block in curly braces else whateveryouwant . I'm not sure how much you want in it. And it looks like the curly braces for your for loop are missplaced after "Sent do KAM". Maybe not, I'm not sure.
– TheWizEd
Nov 14 '18 at 16:42
Thanks for your comment. I tried that but I still have the same issue. Hopefully others can help!
– user10652939
Nov 15 '18 at 8:00