Skip to content

Instantly share code, notes, and snippets.

@phillypb
Created December 14, 2018 21:20
Show Gist options
  • Save phillypb/bc2db786fbc7369491701d2379d6cc87 to your computer and use it in GitHub Desktop.
Save phillypb/bc2db786fbc7369491701d2379d6cc87 to your computer and use it in GitHub Desktop.
function getAssigneeDetails() {
// 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();
// 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');
// get current cell of 'Assignee' column row. Add 1 to row value 'i' otherwise is a row behind
var getCell = planDataRange.getCell([i+1], [7]);
// get cell background colour of 'Assignee' column row.
var getCellColour = getCell.getBackground();
// check Assignee cell colour is white
if (getCellColour == '#ffffff') {
Logger.log('Cell is white, will proceed');
// get corresponding 'Room number' for Assignee
var roomNumber = (planData[i][4]);
Logger.log(roomNumber);
} // end of 'if' for checking Assignee cell is white
else {Logger.log('Cell is not white, will not proceed')};
} // end of 'if' for checking Assignee cell not blank
else {Logger.log('Assignee cell is blank')};
} // end of 'i' loop through each Assignee ************************************************************
}// end of function
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Menu')
.addItem('Email Assignees', 'getAssigneeDetails') // label for menu item, name of function to run.
.addToUi();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment