The code loops through the Assignee column taking one set of initials at a time, to then create another loop to go through the Initials column of the Email Address tab. If it finds a match then the corresponding value from column 2 (the actual email address) is defined as a variable. Eventually this function will lead into sending automated email invites to people that have an associated email address.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function lookupEmailAddress() { | |
// get 'Plan' sheet | |
var planSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Plan'); | |
// get 'Plan' sheet data range | |
var planDataRange = planSheet.getDataRange(); | |
// get 'Plan' sheet data values | |
var planData = planDataRange.getValues(); | |
// get number of last row of data in 'Plan' sheet | |
var planLastRow = planSheet.getLastRow(); | |
// get 'Email Address' sheet | |
var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email Address'); | |
// get 'Email Address' sheet data | |
var emailData = emailSheet.getDataRange().getValues(); | |
// get number of last row of data | |
var emailLastRow = emailSheet.getLastRow(); | |
// loop through each Assignee from 'Plan' sheet to get relevant details (skipping row headers) ********** | |
for (i=1; i<planLastRow; i++) { | |
// check Assignee cell is not blank before proceeding | |
// i = row, 6 = column - as is 2-D array | |
if (planData[i][6]) { | |
Logger.log('Assignee cell is not blank'); | |
// loop through each Assignee from 'Plan' with each Initials in 'Email Address' to find match ****** | |
for (j=0; j<emailLastRow; j++) { | |
if (planData[i][6] == emailData[j][0]) { | |
Logger.log(planData[i][6] + ' matches ' + emailData[j][0]); | |
// if match found then get corresponding email address from column 2 | |
var assigneeEAddress = emailData[j][1]; | |
Logger.log('Email address is: ' + assigneeEAddress); | |
} // end of 'if match' loop ********************************************************************** | |
} // end of 'j' loop ******************************************************************************* | |
} // end of 'if' for checking Assignee cell not blank | |
else {Logger.log('Assignee cell is blank')}; | |
} // end of 'i' loop through each Assignee ************************************************************** | |
} | |
function onOpen() { | |
SpreadsheetApp.getUi() | |
.createMenu('Menu') | |
.addItem('Email Assignees', 'lookupEmailAddress') // label for menu item, name of function to run. | |
.addToUi(); | |
} |
No comments:
Post a Comment