Building a Fully Automated WFH Employee Health Reporting System with Slack

Building a Fully Automated WFH Employee Health Reporting System with Slack

Enhancing work efficiency by playing with Slack Workflow combined with Google Sheet with App Script

Photo by Stephen Phillips — Hostreviews.co.uk

Introduction

In response to full remote work, the company cares about the health of all members. Every day, employees need to report their health status, which is recorded and managed by People Operations.

Our Pre-Optimization Flow

1. [Automation] Slack Channel sends a reminder message about the health form at 10 AM every day (the only automated part before optimization)
2. Employees click the link to open the Google Form and fill out health questions
3. Data is stored back in Google Sheet response records
4. [Manual] People Operations compare the list near the end of the day to filter out employees who forgot to fill it out
5. [Manual] Send reminder messages in the Slack Channel & tag those who forgot to fill it out one by one

The above is our company’s health reporting tracking process. Each company may have different processes based on their scale and operation methods. This article uses it as an optimization example to learn Slack Workflow usage and basic App Script writing. Actual implementation should be case by case.

Issues

• Need to jump out of Slack context to use a browser to open the Google Form webpage to fill it out, which is especially inconvenient on mobile
• Google Form can only automatically include email information, not the name of the person filling it out or department information
• Daily manual comparison and manual tagging are very time-consuming

Solution

Having done quite a few small automation projects, this process has fixed data sources (employee list), simple conditions, and routine actions; it seemed very suitable for automation. Initially, it wasn’t done because I couldn’t find a good way to fill it out (actually, I couldn’t find an interesting research point); so it was left alone until I saw this post by Hai Zongli and realized that Slack Workflow not only can send scheduled messages but also has a form function:

Image from: Hai Zongli

This got me excited!!

If Slack Workflow Form combined with message automation can solve all the pain points mentioned above, the principle is feasible! So I started implementing it.

Post-Optimization Flow

First, let’s look at the optimized process and results.

1. [Automation] Slack Channel sends a daily reminder at 10 AM for everyone to fill out the health form.
2. Fill out health questions via Google Form or Slack Workflow Form.
3. Data is stored back in Google Sheet response records.
4. People Operations clicks the “Generate Unfilled List” button near the end of each workday.
5. [Automation] Use App Script to compare the employee list and the filled list to filter out the unfilled list.
6. [Automation] Click “Generate & Send Message” to automatically send unfilled reminders & automatically tag the individuals.
7. Done!

Effectiveness

(Personal Estimate)

• Each employee can save about 30 seconds daily on filling out the form.
• People Operations can save about 20 ~ 30 minutes daily on handling this task.

Operating Principle

Manage the Sheet by writing App Script.

1. Store all external input data in the Responses Sheet.
2. Write an App Script Function to distribute the data from Responses to each date’s Sheet according to the filling date. If not, create a new date Sheet. The Sheet name directly uses the date for easy identification and access.
3. Compare the current date’s Sheet with the employee list to generate the unfilled list Sheet data.
4. Read the unfilled list Sheet, compose the message, and send it to the specified Slack Channel.

• Integrate with Slack APP API to automatically read the specified Channel and import the employee list.
• Use Slack UID Tag <@UID> in the message content to tag the unfilled members.

Identity Verification

The identity verification information connecting Google Form and Slack is Email, so please ensure that all company colleagues use the company Email to fill out the Google Form, and also fill in the company Email in the Slack personal information section.

Getting Started

After discussing the issues, optimization methods, and results, let’s move on to the implementation phase; let’s complete this automation case step by step together.

The content is a bit lengthy, you can skip the sections you already understand, or directly create a copy from the completed result, and learn while modifying.

Completed result form: https://forms.gle/aqGDCELpAiMFFoyDA

Completed result Google Sheet:

Create a Health Report Google Form & Link Responses to Google Sheet

Steps omitted, please Google if you have any questions. Here, we assume you have already created & linked the health report form.

Remember to check “Collect emails” on the form:

Collect the email addresses of the respondents for future list comparison.

How to link responses to Google Sheet?

Switch to “Responses” at the top of the form and click the “Google Sheet Icon”.

Change the linked Sheet name:

It is recommended to change the linked Sheet name from Form Responses 1 to Responses for easier use.

Create a Slack Workflow Form Entry

After having the traditional Google Form entry, let’s add the Slack filling method.

In any Slack conversation window, find the “ below the input box “ “blue lightning ⚡️” and click on it.

In the menu under “Search shortcuts,” type “workflow” and select “Open Workflow Builder.”

Here, it will list the Workflows you have created or participated in. Click “Create” in the upper right corner to create a new Workflow.

Step one, enter the workflow name (for display in the Workflow Builder interface).

Workflow trigger method, select “Shortcut.”

Currently, there are 5 types of Slack workflow trigger points:

• Shortcut: Manually trigger the “blue lightning ⚡️” option, which will appear in the workflow menu. Click to start the workflow.
• New channel member: When a new member joins the Target Channel… (EX: Welcome message)
• Emoji reactions: When someone reacts to a message in the Target Channel with a specified emoji… (Maybe used for marking important messages as read by pressing XXX Emoji, to know who has read it?)
• Scheduled date & time: Schedule, at a specified time… (EX: Regular reminder messages)
• Webhook: External Webhook trigger, advanced feature, can integrate internal workflows with third-party or self-hosted APIs.

Here we choose “Shortcut” to create a manual trigger option.

Select which “Channel input box” this Workflow Shortcut should be added to and enter the “display name.”

*A workflow shortcut can only be added to one channel.

Shortcut created! Start creating workflow steps by clicking “Add Step.”

Select the “Send a form” Step.

Title: Enter the form title.

Add a question: Enter the first question’s title (you can label the question number in the title, e.g., 1., 2., 3…).

Choose a question type:

• Short answer: Single-line input box.
• Long answer: Multi-line input box.
• Select from a list: Single-choice list.
• Select a person: Choose a member from the same Workspace.
• Select a channel or DM: Choose a member from the same Workspace, Group DM, or Channel.

For “Select from a list”:

1. Add list item: Add an option.
2. Default selection: Choose the default option.
3. Make this required: Set this question as mandatory.

1. Add Question: Add more questions.
2. The right “↓” and “⬆” can adjust the order, “✎” can expand for editing.
3. You can choose whether to send the form responses back to the Channel or to someone.

You can also choose to send the response to…:

• Person who clicked…: The person who clicked this form (same as the person filling it out).
• Channel where workflow started: The Channel where this workflow was added.

After completing the form, click “Save” to save the step.

*Here we uncheck the option to return the form content because we want to customize the message content in later steps.

Integrate Slack workflow with Google Sheet

If you haven’t added the Google Sheet App to Slack yet, you can click here to install the APP.

Following the previous step, click “Add Step” to add a new step. We choose the “Add a spreadsheet row” step from Google Sheets for Workflow Builder.

1. First, complete the authorization of your Google account by clicking “Connect account”.
2. Select a spreadsheet: Choose the target response Google Sheet, please select the Google Sheet created by the initial Google Form.
3. Sheet: Same as above.
4. Column name: The first column to fill in the value, here we select Question 1.

Click “Insert Variable” in the lower right corner and select “Response to Question 1…”. After inserting, you can add other columns by clicking “Add Column” in the lower left corner. Repeat this process for Question 2, Question 3, etc.

For the email of the person filling out the form, you can select “Person who submitted form”.

Click on the inserted variable and select “Email” to automatically fill in the email of the person who filled out the form.

• Mention (default): Tag the user, raw data is <@User ID>
• Name: User name
• Email: User email

The Timestamp column is a bit tricky; we will supplement the setting method later. First, click “Save” to save, then go back to the top right corner of the page and click “Publish” to publish the Shortcut.

After seeing the success message, you can go back to the Slack Channel and give it a try.

At this point, clicking the lightning bolt will show the Workflow form you just created, which you can click to fill out and play with.

Left: Desktop / Right: Mobile

We can fill in the information and “Submit” to test if it works properly.

Success! But you can see that the Timestamp column is empty. Next, we will solve this problem.

Get submission time from Slack workflow

Slack workflow does not have a global variable for the current timestamp, at least not yet. I only found a wish post on Reddit.

Initially, I whimsically entered =NOW() in the Column Value, but this way the time for all records is always the current time, which is completely wrong.

Thanks to the Reddit post and the tricky method provided by a great netizen, you can create a clean Timestamp Sheet with one row of data and a column =NOW(). First, use Update to force the column to be the latest, then use Select to get the current Timestamp.

As shown in the structure above, click here to view the example.

• Row: Similar to the use of ID, set it directly to “1”. It will be used later when setting Select & Update to inform the data row.
• Timestamp: Set the value =NOW() to always display the current time.
• Value: Used to trigger the update time of the Timestamp field. The content is arbitrary; here, the email of the person filling it in is inserted. As long as it can trigger the update, it is fine.

You can right-click on the Sheet and select “Hide Sheet” to hide this Sheet, as it is not intended for external use.

Go back to Slack Workflow Builder to edit the workflow form you just created.

Click “Add Step” to add a new step:

Scroll down and select “Update a spreadsheet row”:

“Select a spreadsheet” to choose the Sheet you just created, and “Sheet” to select the newly created “Timestamp” Sheet.

“Choose a column to search” and select “Row”. Define a cell value to find and enter “1”.

“Update these columns” and “Column name” select “Value”. Click “Insert variable” -> “Person who submitted” -> select “Email”.

Click “Save” to complete! Now the timestamp update in the Sheet has been triggered. Next, we will read it out for use.

Go back to the editing page and click “Add Step” again to add a new step. This time, select “Select a spreadsheet row” to read the Timestamp.

The search part is the same as “Update a spreadsheet row”. Click “Save”.

After saving, go back to the step list page. You can drag and drop to change the order by moving the mouse over the steps.

Change the order to “Update a spreadsheet row” -> “Select a spreadsheet” -> “Add a spreadsheet row”.

This means: Update to trigger the timestamp update -> Read the Timestamp -> Use it when adding a new Row.

Click “Edit” to edit “Add a spreadsheet row”:

Scroll to the bottom and click “Add Column” in the lower left corner, then click “Insert a variable” in the lower right corner. Find the “Timestamp” variable in the “Select a spreadsheet” section and inject it.

Click “Save” to save the step and return to the list page. Click “Publish Change” in the upper right corner to publish the changes.

Now, test the workflow shortcut again to see if the timestamp is written correctly.

Success!

Adding a submission receipt to the Slack workflow form

Similar to the submission receipt in Google Form, the Slack workflow form can also have one.

On the step editing page, we can add another step by clicking “Add Step”.

This time, choose “Send a message”

Select “Send this message to” and choose “Person who submitted form”

Enter the message content in order, the question title, “Insert a variable” and select “Response to question XXX”. You can also insert “Timestamp” at the end. After saving the steps by clicking “Save”, click “Publish Changes”!

Additionally, you can use “Send a message” to send the filled results to a specific Channel or DM.

Success!

The setup of the Slack workflow form is roughly complete. You can freely combine and play with other features.

Google Sheet with App Script!

Next, we need to write an App Script to handle the filled data.

First, select “Tools” -> “Script editor” from the toolbar at the top of Google Sheet.

You can click the top left corner to give the project a name.

Now we can start writing App Script! App Script is designed based on Javascript, so you can directly use Javascript code with Google Sheet’s library.

Distribute the data of Responses to each date’s Sheet according to the filling date

function formatData() {
  var bufferSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses') // Name of the Sheet storing responses
  
  var rows = bufferSheet.getDataRange().getValues();
  var fields = [];
  var startDeleteIndex = -1;
  var deleteLength = 0;
  for(index in rows) {
    if (index == 0) {
      fields = rows[index];
      continue;
    }

    var sheetName = rows[index][0].toLocaleDateString("en-US"); // Convert Date to String, using US date format MM/DD/YYYY
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); // Get MM/DD/YYYY Sheet
    if (sheet == null) { // If not exist, create new
      sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName, bufferSheet.getIndex());
      sheet.appendRow(fields);
    }

    sheet.appendRow(rows[index]); // Add data to date Sheet
    if (startDeleteIndex == -1) {
      startDeleteIndex = +index + 1;
    }
    deleteLength += 1;
  }

  if (deleteLength > 0) {
    bufferSheet.deleteRows(startDeleteIndex, deleteLength); // After moving to the specified Sheet, remove data from Responses
  }
}

Paste the above code into the Code block and press “control” + “s” to save.

Next, we need to add a trigger button in the Sheet (can only be triggered manually, cannot be automatically triggered when data is written)

1. First, create a new Sheet and name it “Unfilled List”.
2. From the top toolbar, select “Insert” -> “Drawing”.

Use this interface to draw a button.

After “Save and Close”, you can adjust and move the button; click the top right “…” and select “Assign script”.

Enter the function name “formatData”.

You can click the added button to test the function.

If “Authorization Required” appears, click “Continue” to complete the verification.

During the authentication process, “Google hasn’t verified this app” will appear. This is normal because the App Script we wrote is not verified by Google, but that’s okay since it’s for personal use.

Click “Advanced” at the bottom left -> “Go to Health Report (Responses) (unsafe)”.

Click “Allow”.

While the App Script is running and shows “Running Script”, please do not press again to avoid repeated execution.

Only after the execution is successful can you run it again.

Success! The data is grouped by date.

Compare the current date’s Sheet with the employee list to generate data for the Unfilled List Sheet

Let’s add a piece of code:

// Compare the employee list Sheet & today's filled Sheet to generate the unfilled list
function generateUnfilledList() {
  var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Employee List') // Employee list Sheet name
  var unfilledListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Unfilled List') // Unfilled list Sheet name
  var today = new Date();
  var todayName = today.toLocaleDateString("en-US");

  var todayListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(todayName) // Get today's MM/DD/YYYY Sheet
  if (todayListSheet == null) {
    SpreadsheetApp.getUi().alert('Cannot find today\'s Sheet ' + todayName + ' or please run "Organize Filled Data" first');
    return;
  }

  var todayEmails = todayListSheet.getDataRange().getValues().map( x => x[1] ) // Get today's Sheet Email Address column data list (1 = Column B)
  // index start from 0, so 1 = Column B
  // output: Email Address,zhgchgli@gmail.com,alan@gamil.com,b@gmail.com...
  todayEmails.shift() // Remove the first data, the first is the column name "Email Address" which is meaningless
  // output: zhgchgli@gmail.com,alan@gamil.com,b@gmail.com...

  unfilledListSheet.clear() // Clear the unfilled list... prepare to refill data
  unfilledListSheet.appendRow([todayName + " Unfilled List"]) // The first row shows the Sheet title

  var rows = listSheet.getDataRange().getValues(); // Read the employee list Sheet
  for(index in rows) {
    if (index == 0) { // The first row is the header row, save it, so that the subsequent generated data can also add the first row header
      unfilledListSheet.appendRow(rows[index]);
      continue;
    }
    
    if (todayEmails.includes(rows[index][3])) { // If today's Sheet Email Address contains this employee's Email, it means it has been filled, continue to skip... (3 = Column D)
      continue;
    }

    unfilledListSheet.appendRow(rows[index]); // Write a row of data to the unfilled list Sheet
  }
}

After saving, follow the previous method to add code, then add a button and assign the script — “generateUnfilledList”.

Once completed, you can click to test:

Unfilled list generated successfully! If no content appears, please ensure:

• The employee list is filled in, or you can enter test data first.
• Complete the “Organize Filled Data” action first.

Read the Unfilled List Sheet, compile the message, and send it to the specified Slack Channel

First, we need to add the Incoming WebHooks App to the Slack Channel. We will use this medium to send messages.

1. Slack bottom left “Apps” -> “Add apps”
2. Search “incoming” in the search box on the right
3. Click “Incoming WebHooks” -> “Add”

Select the Channel where you want to send the unfilled message.

Note down the “Webhook URL” at the top.

Scroll down to set the name and avatar of the Bot when sending messages; remember to click “Save Settings” after making changes.

Back to our Google Sheet Script

Add another piece of code:

function postSlack() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
     'Are you sure you want to send the message?',
     'Send unfilled reminder message to Slack Channel',
      ui.ButtonSet.YES_NO);
  // To avoid accidental touches, ask for confirmation first

  if (result == ui.Button.YES) {
    var unfilledListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Unfilled List') // Unfilled List Sheet name
    var rows = unfilledListSheet.getDataRange().getValues();
    var persons = [];
    for(index in rows) {
      if (index == 0 || index == 1) { // Skip the title and column header rows
        continue;
      }
      
      var person = (rows[index][4] == "") ? (rows[index][2]) : ("<@"+rows[index][4]+">"); // Mark the target, use slack uid if available, otherwise just display the nickname; 2 = Column B / 4 = Column E
      if (person == "") { // Consider it as abnormal data if both are empty, ignore
        continue;
      }
      persons.push("• "+person+'\n') // Store the target in the array
    }

    if (persons.length <= 0) { // If no target needs to be notified, everyone has filled in, cancel the message sending
      return;
    }

    var preText = "*[Health Report Announcement:loudspeaker:]*\nThe company cares about everyone's health, please remember to fill in the daily health status report, thank you:wink:\n\nToday's unfilled health status report list\n\n" // Message opening content...
    var postText = "\n\nFilling in the health status report allows the company to understand the health status of teammates, please make sure to fill it in every day>< Thank you everyone:woman-bowing::skin-tone-2:" // Message closing content...
    var payload = {
      "text": preText+persons.join('')+postText,
      "attachments": [{
          "fallback": "You can put the Google Form filling link here",
          "actions": [
            {
                "name": "form_link",
                "text": "Go to Health Status Report",
                "type": "button",
                "style": "primary",
                "url": "You can put the Google Form filling link here"
            }
          ],
          "footer": ":rocket:Tip: Click the \":zap:️lightning\" below the input box -> \"Shortcut Name\" to fill in directly."
        }
      ]
    };
    var res = UrlFetchApp.fetch('Enter your slack incoming app Webhook URL here',{
      method             : 'post',
      contentType        : 'application/json',
      payload            : JSON.stringify(payload)
    })
  }
}

After saving, follow the previous method to add code, then add a button and assign the script — “postSlack”.

Once completed, you can click to test:

Success!!! (The display @U123456 did not successfully tag the person because the ID was randomly typed by me)

At this point, the main functions are all completed!

Note

Please note that the official recommendation is to use the new Slack APP API’s chat.postMessage to send messages. The simpler method of Incoming Webhook will be deprecated. I did not use it here for convenience. You can adjust to the new method along with the next chapter “Import Employee List,” which will require the Slack App API.

Import Employee List

Here we need to create a Slack APP.

1. Go to https://api.slack.com/apps

2. Click “Create New App” in the upper right corner

1. Choose “ From scratch “

1. Enter “ App Name “ and the Workspace you want to add

1. After successful creation, select “OAuth & Permissions” settings page from the left menu

1. Scroll down to the Scopes section

Add the following items in “Add an OAuth Scope”:

• channels:read
• users:read
• users:read.email
• If you want to use the APP to send messages, you can add chat.postMessage

1. Go back to the top and click “Install to workspace” or “Reinstall to workspace”

*If Scopes are added, you need to come back and reinstall.

1. After installation, get and copy the Bot User OAuth Token

2. Use the web version of Slack to open the Channel where you want to import the list

Get the URL from the browser:

https://app.slack.com/client/TXXXX/CXXXX

Where CXXXX is the Channel ID of this Channel, note this information.

10.

Go back to our Google Sheet Script

Add the following code:

function loadEmployeeList() {
  var formData = {
    'token': 'Bot User OAuth Token',
    'channel': 'Channel ID',
    'limit': 500
  };
  var options = {
    'method' : 'post',
    'payload' : formData
  };
  var response = UrlFetchApp.fetch('https://slack.com/api/conversations.members', options);
  var data = JSON.parse(response.getContentText());
  for (index in data["members"]) {
    var uid = data["members"][index];
    var formData = {
      'token': 'Bot User OAuth Token',
      'user': uid
    };
    var options = {
      'method' : 'post',
      'payload' : formData
    };
    var response = UrlFetchApp.fetch('https://slack.com/api/users.info', options);
    var user = JSON.parse(response.getContentText());

    var email = user["user"]["profile"]["email"];
    var real_name = user["user"]["profile"]["real_name_normalized"];
    var title = user["user"]["profile"]["title"];
    var row = [title, real_name, real_name, email, uid]; // Fill in according to Column

    var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Employee List'); // Employee list Sheet name
    listSheet.appendRow(row);
  }
}

But this time we don’t need to add the button again, because the import is only needed the first time; so just save and run directly.

First, press “control” + “s” to save, change the top dropdown menu to “loadEmployeeList”, and click “Run” to start importing the list into the Employee List Sheet.

Manually Add New Employee Data

If new employees join later, you can directly add a row in the Employee List Sheet and fill in the information. The Slack UID can be directly queried on Slack:

Click on the person whose UID you want to view, and click “View full profile”

Click “More” and select “Copy member ID” to get the UID. UXXXXX

DONE!

All the above steps are completed, and you can start automating the tracking of employees’ health status.

The completed file can be copied and modified from the following Google Sheet:

Supplement

• If you want to use Scheduled date & time to send form messages regularly, note that in this case, the form can only be filled out once, so it is not suitable for use here… (at least in the current version), so Scheduled reminder messages can still only use plain text + Google Form link.

• Currently, there is no way to link to Shortcut to open the Form
• Google Sheet App Script to prevent duplicate execution:

If you want to prevent accidental re-execution during execution, you can add at the beginning of the function:

if (PropertiesService.getScriptProperties().getProperty('FUNCTIONNAME') == 'true') {
  SpreadsheetApp.getUi().alert('Busy... Please try again later');
  return;
}
PropertiesService.getScriptProperties().setProperty('FUNCTIONNAME', 'true');

Add at the end of the function execution:

PropertiesService.getScriptProperties().setProperty('FUNCTIONNAME', 'true');

Replace FUNCTIONNAME with the target function name.

Use a global variable to control execution.

Applications Related to iOS Development

Can be used to connect CI/CD, using GUI to package the original ugly command operations, such as using Slack Bitrise APP, combining Slack Workflow form to trigger Build commands:

After submission, it will send a command to the private channel with the Bitrise APP, EX:

bitrise workflow:app_store|branch:develop|ENV[version]:4.32.0

This will trigger Bitrise to execute the CI/CD Flow.

Further Reading

• Using Python + Google Cloud Platform + Line Bot to Automate Routine Tasks
• Using Google Apps Script to Forward Gmail to Slack
• Building a More Real-time and Convenient Crash Tracking Tool with Crashlytics + Big Query
• Automatically Querying App Crash-Free Users Rate with Crashlytics + Google Analytics

If you have any questions or feedback, feel free to contact me.

If you have any automation-related optimization needs, you are also welcome to commission me. Thank you.

===

本文中文版本

===

This article was first published in Traditional Chinese on Medium ➡️ View Here