Reputation: 95
I have a G sheet that contains a transaction of our staff. And i'm trying to make a script that will sent a notification email to the employee that they had a pending on particular account no.
The Email address is automatically added when we choose the staff. (Employee had a multiple account)
Every account no. has own G sheet, we're just importing the pending per Gsheet of the account no.
----------------------------------------------------------------
ACCOUNT NO.|STAFF| PENDING | REMARKS | EMAIL |
----------|-------|------------|----------------|---------------|
1231242 | JOHN | PENCIL - 1 | 2ND | |
| | PAPER- 2 | FOLLOW UP |[email protected] |
---------|-------|------------|----------------|---------------|
| | | | N/A |
---------|-------|------------|----------------|---------------|
1231243 | TED | | 1ST FOLLOW UP | [email protected] |
---------|-------|------------|----------------|---------------|
| | TAPE- 1 | | |
1231244 | MARY | NOTEBOOK-16| TOP URGENT | [email protected]|
---------|-------|------------|----------------|---------------|
| | BAG - 1 | | |
12312467 | JOHN | BOARD-16 | URGEN T |[email protected] |
----------------------------------------------------------------
This is the code i'm using. When there is no email address on column Email it's not working
function CheckPending() {
// Fetch the Pendings
var PendingRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("C2:C");
var DailyPendings = PendingRange.getValue();
// Check totals Pending
if (DailyPendings ==! ""){
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("D2:D");
var emailAddress = emailRange.getValues();
// Send Alert Email.
var message = 'YOU HAVE ' + DailyPendings + ' PENDING, PLEASE CHECK '; // Second column
var subject = 'PENDING ALERT';
MailApp.sendEmail(emailAddress, subject, message);
}
}
Upvotes: 0
Views: 113
Reputation: 862
See commented code below:
function CheckPending() {
// Fetch the Pendings
// Best to get values for both columns at once for performance reasons, and then work on the resulting array
var PendingRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("C2:D");
var DailyPendings = PendingRange.getValues(); // Here you need getValueS() as you take values from a range
// Check totals Pending
// Since you consider multiple rows, you need to loop through all rows ;
// Note that it might be wiser in your case to use getDataRange() above and consider columns 3 and 4 of your array, as otherwise you'll include empty lines
for (var i = 0 ; i < DailyPendings.length ; i++){
if (DailyPendings[i][0] !== ""){ // Your operator here was in the wrong order
Logger.log(DailyPendings[i][0]);
// Fetch the email address
// Can be removed, not useful: var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getRange("D2:D");
var emailAddress = DailyPendings[i][1];
// Send Alert Email.
var message = 'YOU HAVE ' + DailyPendings[i][0] + ' PENDING, PLEASE CHECK '; // Second column
var subject = 'PENDING ALERT';
MailApp.sendEmail(emailAddress, subject, message);
}
}
}
Update:
function CheckPending() {
// Collect all data in a JS array
var DailyPendings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Week 2").getDataRange().getValues();
// Loop through each row, starting at row with index 1 (second row) since the first one contains headers
for (var i = 1 ; i < DailyPendings.length ; i++){
/*
Adjusted to your edited question:
** Column with index 0 is first column: account number
** Column with index 1 is second column: staff
** Column with index 2 is third column: pendings
** Column with index 3 is fourth column: remarks
** Column with index 4 is fifth column: email
*/
if (DailyPendings[i][2] !== ""){ // If pending isn't empty
var emailAddress = DailyPendings[i][4];
var account = DailyPendings[i][0];
// Send Alert Email.
var message = 'YOU HAVE ' + DailyPendings[i][2] + ' PENDING, PLEASE CHECK '; // Add the variable 'account' anywhere in your (adjusted) message
var subject = 'PENDING ALERT';
MailApp.sendEmail(emailAddress, subject, message);
}
}
}
Upvotes: 2