JavaScript in Google Sheets: Scanning Rows and Columns for Employee Absences

Other Questions Google Sheets
1

Hey everyone! I’m a newbie to JavaScript but I’m trying to tackle a project at work. My coworker asked if I could create something that shows who’s off or working half-days. I’ve got it sending emails, but I’m stuck on how to read cells and rows in Google Sheets.

My spreadsheet has a summary tab and monthly tabs. The summary shows a quick view of holidays, half-days, and sick days for each person. The monthly tabs use codes like H for holiday, HD for half-day, and S for sick day.

What I want to do is check today’s date, look at the right month tab, find any H or HD entries, figure out whose row it is, and add that info to an email. I’ve got some code started, but I’m not sure how to actually scan the cells and get the right data.

Here’s a simplified version of what I’m working with:

function checkAbsences() {
  var today = new Date();
  var day = today.getDate();
  var month = today.getMonth() + 1;
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var monthTab = sheet.getSheetByName('Month' + month);
  
  var absentees = [];
  var halfDayers = [];
  
  // Need help here to scan rows and columns
  
  if (absentees.length > 0 || halfDayers.length > 0) {
    sendAbsenceEmail(absentees, halfDayers);
  }
}

function sendAbsenceEmail(absentees, halfDayers) {
  // Email sending logic here
}

Any tips on how to scan the right cells and build those lists? Thanks!

2

I’ve tackled a similar project before, and here’s what worked for me:

First, you’ll want to get the specific column for today’s date. Then, use getRange() to select that column along with the names column. Something like:

var dateColumn = day;
var range = monthTab.getRange(1, 1, monthTab.getLastRow(), dateColumn);
var values = range.getValues();

Now, loop through the values:

for (var i = 1; i < values.length; i++) {
var name = values[i][0];
var code = values[i][dateColumn - 1];
if (code === ‘H’) absentees.push(name);
else if (code === ‘HD’) halfDayers.push(name);
}

This should populate your lists with the correct names. Remember to adjust the ranges if your sheet layout is different. Good luck with your project!

3

I’ve actually implemented something similar for my team. Here’s what worked well:

Instead of scanning the entire sheet, you can optimize by focusing on the specific date column. Use getRange() to grab just that column plus the names:

var dateCol = day;
var dataRange = monthTab.getRange(1, 1, monthTab.getLastRow(), dateCol + 1);
var data = dataRange.getValues();

Then loop through, checking codes:

for (var i = 1; i < data.length; i++) {
  var name = data[i][0];
  var code = data[i][dateCol];
  if (code === 'H') absentees.push(name);
  if (code === 'HD') halfDayers.push(name);
}

This approach is faster and more efficient, especially for larger sheets. It only pulls the data you need. You might also want to add error handling and maybe a way to exclude weekends if that’s relevant for your use case.

4

hey noah, i’ve done somethin similar before. you’ll wanna use getRange() to grab the cells you need, then getValues() to get the data. loop through the values and check for your codes. smthing like:

var range = monthTab.getRange("A1:Z100");
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
  if (values[i][day-1] == 'H') {
    absentees.push(values[i][0]); // assuming names in first column
  }
}

hope that helps!