In my engineering team, we hold daily standups, and to keep things engaging, we wanted to randomize the order of speakers each day. However, we quickly realized that while randomizing keeps things fresh, it also catches people off guard if they don't know when they will be called upon. To solve this, I built an automation using Google Apps Script that:

• Randomizes the standup order while grouping people from the same workstream together.
• Sends a Slack message every weekday morning at 9 AM with the order.
• Pulls the attendee list from a Google Spreadsheet, allowing easy updates.

This post walks through the entire setup, including code snippets, Slack integration, and lessons learned.

How the Automation Works

Attendee List in Google Sheets

The list of standup attendees is stored in a Google Sheet named “Attendees”. Each row contains:

• Active Column: Allows marking people as inactive (e.g. on vacation) to exclude them from the daily order.
• Workstream Column: Ensures that people from the same workstream appear together in the randomized order.

Google Apps Script Code

Step 1: Triggering the Script Daily

I set up a Google Apps Script time-based trigger to execute this script every day at 9 AM. However, my script contains a method called isWeekend() that checks if today is a weekend (Saturday or Sunday). If it is a weekend, the script exits without performing any further steps.

function isWeekend(date = new Date()) {
  return date.getDay() === 6 || date.getDay() === 0;
}

Step 2: Reading Data from Google Sheets

The script reads the attendee list and filters out inactive members:

function getTableDataFromSheet(sheetName, mapperFunc) {
  let sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  let rawData = sheet.getDataRange().getValues();
  return rawData
    .filter((dataRow, idx) => idx > 0)
    .map(dataRow => mapperFunc(dataRow));
}

function loadPeople(sheetName) {
  const allFolks = getTableDataFromSheet(sheetName, (dataRow) => {
    return {
      name: dataRow[0],
      active: dataRow[1] === "Y",
      group: dataRow[2]  // Workstream grouping
    };
  });
  return allFolks.filter(person => person.active);
}

Step 3: Randomizing the Order

The script first groups attendees by workstream, then shuffles each group, and finally shuffles the group order:

function processOrderingFor(sheetName) {
  const people = loadPeople(sheetName);
  const groupedPeople = groupByWorkstream(people);
  const randomizedGroups = randomize(Object.keys(groupedPeople));
  
  const finalPeople = randomizedGroups.flatMap(group => randomize(groupedPeople[group]));
  sendNotice(finalPeople.join(", "));
}

function groupByWorkstream(people) {
  return people.reduce((accumulator, currentValue) => {
    if (!accumulator[currentValue.group]) {
      accumulator[currentValue.group] = [];
    }
    accumulator[currentValue.group].push(currentValue.name);
    return accumulator;
  }, {});
}

function randomize(list) {
  return list
    .map(item => ({ sort: Math.random(), value: item }))
    .sort((a, b) => a.sort - b.sort)
    .map(item => item.value);
}

Step 4: Sending to Slack via Webhook

I use a Slack automation triggered by a webhook that listens for a POST request and posts a message in the standup channel. (See instructions below on how to set this up in Slack)

function sendNotice(messageBody) {
  let slackUrl = 'https://hooks.slack.com/triggers/YOUR-WEBHOOK-URL';
  let options = {
    method: "POST",
    contentType: 'application/json',
    payload: JSON.stringify({
      order: messageBody
    })
  };

  try {
    UrlFetchApp.fetch(slackUrl, options);
  } catch (ex) {
    Logger.log(ex);
  }
}

Setting Up the Slack Integration

I used Slack Automations to handle the webhook request. The purpose of this integration is to receive the message from the Google Apps Script and post it in a Slack channel that the team uses for standup updates. You can also refer to Slack's official documentation on working with automations and triggering automations via webhooks for more details.

Here’s how:

1. Go to Slack Workflow Builder and create a new workflow.

2. Select “Starts with a Webhook” as the trigger.

3. Configure the webhook to accept a JSON payload with an order field.

4. Add a step to post the order field into the team’s Slack channel.

---

Edge Cases and Lessons Learned

While this automation works well, I encountered some challenges. Sometimes, people go on vacation but forget to update their status in the spreadsheet, meaning they still appear in the order. Similarly, new team members are not included until I manually add them. Another issue is that there are no failure alerts—if the Slack message fails due to network issues, I have to manually check and investigate why it didn’t show up.

Future Improvements

To improve accuracy and reduce manual maintenance, I plan to integrate the script with Google Calendar instead of relying on a Google Sheet. This would ensure the invite list is always up-to-date. Additionally, I will add logging and alerts to notify me if the script fails, helping with debugging and reliability.