Automating Alert Monitoring Duty Notifications with Slack and Google Apps Script

Overview

• We were sending system alert notifications to Slack and managing the team's on-call rotation in a Spreadsheet.
• However, everyone was busy and often ended up forgetting to check it.
• I used a Slack App + GAS to automatically notify the person on-duty according to the Spreadsheet rotation schedule.

Notification Format

• Notifications are sent every business day morning.
• Mentions the person on-duty for the day and the team.
• The wording is a bit dramatic to build some excitement.

Translation:

Monitoring: @DutyPerson stands as the sentinel today!!
CC: @Team
Salute our guardian {DutyPerson}!
The fate of {ProductName} is committed unto thy hands!

Architecture

The system consists of a Spreadsheet, a Slack App, and GAS.
The details and settings for each are provided below.

Spreadsheet

Create two sheets: "Roster" and "MentionID".

Roster:

• Date column
  • Set the date for the duty
• Name column
  • Set the person in charge
  • Set "-" to skip notifications on weekends and holidays

MentionID:

• Name column

  • The name of the person in charge
  • This must match the names in the Roster sheet

• Slack Mention ID column

  • The ID used for mentioning on Slack

• Group column

  • Indicates whether it is a group mention
  • This is necessary because the message format for group mentions differs from individual ones

• CC column

  • Whether you want to include them in the CC list

Slack app

1. Access Slack API: Apps and click "Create New App".
   

2. Select "From scratch", enter the app name, and select the target workspace.
   

3. Select "Incoming Webhooks" and turn the feature "On".
   

4. Click "Add New Webhook to Workspace" at the bottom and select the channel for notifications.
   

5. Copy the issued Webhook URL.

GAS

sendDutyReminder is the main function.
You can set a trigger for this to run at your preferred time.
Note that GAS can only set Cron triggers on an hourly basis, so you cannot specify an exact time (minutes).

/**
 * Configuration Constants
 */
const CONFIG = {
  // Paste the copied Webhook URL here
  WEBHOOK_URL: "https://hooks.slack.com/services/XXXXXXXXXXXXXX",
  SHEET_NAME: {
    ROSTER: "Roster",
    MENTION: "MentionID"
  },
};

/**
 * Main function: Triggered daily to remind the duty
 * (Note: Kept as a named function or assigned variable for GAS trigger recognition)
 */
const sendDutyReminder = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Get today's guardian name
  const targetUserName = getTodayUserName(ss.getSheetByName(CONFIG.SHEET_NAME.ROSTER));
  
  // Validation: No guardian found or filtered by specific name
  if (!targetUserName) {
    console.log("No guardian to notify today.");
    const warningText = `:warning: *Monitoring: Cannot find the sentinel today* :eyes: :eyes: :eyes:\n    Verify the Roster file and configure proper setting! \n    `
    postToSlack(warningText);
    return;
  }

  if (!targetUserName || targetUserName === '-') {
    console.log("No guardian to notify today.");
    return;
  }

  // 2. Retrieve mention IDs (Target and CC)
  const { targetUser, ccMentioned } = getMentionData(ss.getSheetByName(CONFIG.SHEET_NAME.MENTION), targetUserName);

  if (!targetUser) {
    console.error("Target user ID not found in MentionID sheet.");
    return;
  }

  // 3. Construct the epic message
  const messageText = buildAppointmentMessage(targetUser, ccMentioned);

  // 4. Dispatch to Slack
  postToSlack(messageText);
};

/**
 * Fetch today's guardian name from the roster sheet
 */
const getTodayUserName = (sheet) => {
  const data = sheet.getDataRange().getValues();
  const todayStr = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd");

  for (let i = 1; i < data.length; i++) {
    const dateInSheet = Utilities.formatDate(new Date(data[i][0]), "JST", "yyyy/MM/dd");
    if (dateInSheet === todayStr) {
      return data[i][1];
    }
  }
  return null;
};

/**
 * Gather Slack IDs and group info for mentions
 */
const getMentionData = (sheet, targetName) => {
  const data = sheet.getDataRange().getValues();
  let targetUser = null;
  const ccMentioned = [];

  for (let i = 1; i < data.length; i++) {
    const [name, slackId, isGroup, isCc] = data[i];
    const userInfo = { name, slackId, isGroup };

    if (name === targetName) {
      targetUser = userInfo;
    }
    if (isCc) {
      ccMentioned.push(userInfo);
    }
  }
  return { targetUser, ccMentioned };
};

/**
 * Build the message with a fantasy tone
 */
const buildAppointmentMessage = (target, ccList) => {
  const targetMention = `<@${target.slackId}>`;
  const ccMentions = ccList.map(cc => 
    cc.isGroup ? `<!subteam^${cc.slackId}>` : `<@${cc.slackId}>`
  ).join(" ");

  return [
    `*Monitoring: ${targetMention} stands as the sentinel today!!*`,
    `CC: ${ccMentions}`,
    "",
    `Salute our champion *${target.name}* !`,
    `Our destiny is committed unto thy hands!`
  ].join("\\n");
};

/**
 * Post request to Slack Webhook
 */
const postToSlack = (text) => {
  const payload = { "text": text };
  const options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };

  return UrlFetchApp.fetch(CONFIG.WEBHOOK_URL, options);
};

Trigger Settings:
Open "Apps Script" from the Spreadsheet.

Create and save a trigger for sendDutyReminder.

Future problem

• Notifications to prompt for roster updates when the schedule in the Spreadsheet is running low.
• Automatic tracking of public holidays.

etc...