Hey everyone! I’m pretty new to JavaScript but have experience with VB, VBA, and C#. My coworker wants me to build something that checks who’s taking time off today and sends notifications.
I have a spreadsheet that tracks employee time off using codes like V for vacation, BD for bank holiday, PD for partial day, IL for illness, etc. Each month has its own tab with dates as columns and employee names as rows.
I can send emails fine, but I’m stuck on how to read the spreadsheet data. I need to get today’s date, find the right month tab, locate the correct column for today, then check each row for the time off codes and grab the employee names.
Here’s what I have so far:
// Date variables
var currentDate = new Date();
var dayNum = currentDate.getDate();
var monthNum = currentDate.getMonth()+1;
// Email setup
var shouldSend = false;
var recipient = "";
var emailTitle = "Daily Time Off Report";
var headerText = "Hello team,\n\nEmployees with partial days today:\n\n";
var partialList;
var middleText = "\n\nEmployees on vacation today:\n\n";
var vacationList;
var footerText = "\n\nBest regards,\nTime Off System";
var completeMessage;
// Spreadsheet variables
var workbook = SpreadsheetApp.getActiveSpreadsheet();
workbook.setActiveSheet(workbook.getSheets()[0]);
function executeMain() {
getPartialDayEmployees();
getVacationEmployees();
deliverEmail();
}
function getPartialDayEmployees() {
// Find employees with partial days
}
function getVacationEmployees() {
// Find employees on vacation
}
function deliverEmail() {
if (shouldSend == true){
completeMessage = headerText + partialList + middleText + vacationList + footerText;
MailApp.sendEmail(recipient, emailTitle, completeMessage);
}
}
How do I actually read the cell values and match them with employee names? I only want to send the email if someone is actually out today.
Google Sheets data seems tricky at first, but it’s actually pretty straightforward. Grab your month sheet with workbook.getSheetByName('January') or whatever you’re calling it. Read everything at once using getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues() - this gets you a complete 2D array. Your first row has dates, so loop through to find today’s column. Then walk through each row - index 0 is the employee name, your target column has the time off code. I made the mistake of reading individual cells at first - super slow. Watch out for date formats though. JavaScript dates and spreadsheet dates don’t always match up, so use Utilities.formatDate() to keep them consistent. Also check if the month sheet actually exists first, or your script just dies quietly.
Quick tip - your sheet structure matters here. Use getDataRange() on the active sheet to grab everything at once instead of reading cell by cell. Much faster. Then use indexOf() to find today’s date in the header row and map employee names from the first column. Handle empty cells or your script crashes when someone hasn’t updated their status.
Been there with Google Sheets automation. Manual works but gets messy fast with multiple sheets and date formats.
Skip Google Apps Script entirely. You need a proper automation platform that handles spreadsheet reading and email sending without manual cell mapping.
Set up a workflow that connects to your sheet, reads the current month tab, finds today’s date column, scans employee rows for your codes (V, BD, PD, IL), and compiles lists. Runs daily and only sends emails when it finds employees with time off.
No array indexing, sheet switching logic, or date matching errors. Configure data mapping once and you’re done.
I use this for similar employee notifications. Way cleaner than maintaining Apps Script functions that break whenever someone reorganizes the spreadsheet.
Check out Latenode: https://latenode.com
I’ve done similar Google Sheets automation before. Use getRange() to target cells and getValues() to grab data. Switch to the right month sheet with workbook.getSheetByName() using the month name or number. Find your date column by looping through the header row until you hit today’s date. Got the column number? Pull all employee data with getRange(startRow, columnNumber, numberOfRows, 1).getValues() - this gives you a 2D array where each element is a row. Loop through and check for codes like ‘PD’ or ‘V’. Grab employee names from column A using the same row numbers. Watch out - today’s date might not exist in the sheet yet. Also trim whitespace from cell values since spreadsheet data loves extra spaces that’ll break your comparisons.