The 30-line Apps Script that ends 'is my kid in school today?'

Every Indian school I've worked with has the same broken loop: the class teacher marks attendance in a register, the office stitches it into a spreadsheet later, and a parent calls at noon asking whether their child reached school. By the time someone checks the register, the parent's already anxious.

You can close this loop in an afternoon. Here's the system I keep deploying.

What it does

Every morning at 9:30, a Google Apps Script reads today's attendance from a sheet, groups absent students by class, and fires one WhatsApp message per parent through the WhatsApp Cloud API. Total cost: roughly ₹0.50 per parent per month, well inside even a small school's budget.

The sheet shape

A single tab named Today with four columns: Student ID, Class, Parent Phone, Status. The class teacher (or an attendance app) flips the Status to Absent if the child didn't show up. That's the only data the script needs.

The script

function sendAbsenceAlerts() {
  const sheet = SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName("Today");
  const rows = sheet.getDataRange().getValues();
  const header = rows.shift();
  const i = (k) => header.indexOf(k);
 
  const today = Utilities.formatDate(
      new Date(), Session.getScriptTimeZone(), "d MMM");
 
  rows
    .filter((r) => r[i("Status")] === "Absent")
    .forEach((r) => {
      sendWhatsApp(r[i("Parent Phone")], {
        student: r[i("Student ID")],
        date: today,
      });
    });
}
 
function sendWhatsApp(to, vars) {
  const url = "https://graph.facebook.com/v20.0/" +
              PropertiesService.getScriptProperties()
                  .getProperty("PHONE_NUMBER_ID") +
              "/messages";
  UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    headers: {
      Authorization: "Bearer " + PropertiesService
          .getScriptProperties().getProperty("WA_TOKEN"),
    },
    payload: JSON.stringify({
      messaging_product: "whatsapp",
      to: String(to),
      type: "template",
      template: {
        name: "absent_today",
        language: { code: "en" },
        components: [{
          type: "body",
          parameters: [
            { type: "text", text: vars.student },
            { type: "text", text: vars.date },
          ],
        }],
      },
    }),
  });
}

Wiring it up

1. Approve the template in WhatsApp Manager — call it absent_today and include two {{ }} placeholders for student name and date.
2. Set two Script Properties in the Apps Script editor: WA_TOKEN and PHONE_NUMBER_ID. Both come from your Meta developer dashboard.
3. Add a time-based trigger for sendAbsenceAlerts at 09:30 every weekday.
4. Done.

Two real numbers from production

• The K-12 school I shipped this for went from a handful of parents calling every morning to a single one — and now it's mostly to thank the office.
• Cost in the first month: ₹312 in WhatsApp template fees. Hours saved at the front desk: about 18.

This is the easiest automation you'll deploy this quarter. Steal it.