Slack Workflow｜Automate WFH Employee Health Reporting with Google Sheets & App Script

点击这里查看本文章简体中文版本。

點擊這裡查看本文章正體中文版本。

This post was translated with AI assistance — let me know if anything sounds off!

---

Slack Builds a Fully Automated WFH Employee Health Status Reporting System

Master Slack Workflow with Google Sheets and App Script to Boost Work Efficiency

Photo by Stephen Phillips — Hostreviews.co.uk

Introduction

In response to full remote work, the company cares about the health of all members. Everyone must report their health status daily, which is centrally recorded and managed by People Operations.

Our Flow Before Optimization

1. [Automation] Slack Channel sends a daily reminder message at 10 AM to prompt everyone to fill out the health form (the only automated part before optimization)

2. Employees click the link to open the Google Form and fill out health questions

3. Save Data Back to Google Sheet Response Records

4. [Manual] People Operations compares the list near the end of each workday to identify employees who forgot to fill in the form.

5. [Manual] Send reminder messages in Slack Channel & tag each person who forgot to fill in

The above is our company’s health report tracking process. Each company varies based on size and operations. This article serves only as an optimization example to learn Slack Workflow usage and basic App Script writing. Actual implementation should be done on a case-by-case basis.

Issues

• You need to exit the Slack context and open the Google Form webpage in a browser to fill it out, which is especially inconvenient on mobile devices.

• Google Form can only automatically include email addresses and cannot automatically add the respondent’s name or department information.

• Daily manual comparison and manual tagging are very time-consuming and labor-intensive.

Solution

I have done quite a few small automation tasks. This process has a fixed data source (employee list), simple conditions, and routine actions; it clearly suits automation. Initially, I didn’t do it because I couldn’t find a good way to fill it out (actually, I couldn’t find an interesting point to research). So I just left it alone until I saw this post by 海總理, which made me realize that Slack Workflow can not only send scheduled messages but also has a Form feature:

Image source: 海總理

Now my hands are itching to start!!

If combined with Slack Workflow From and message automation, wouldn’t it solve all the pain points mentioned above? The concept is feasible! So, I started working on the implementation.

Optimized Flow

First, let’s review the optimized process and results.

1. [Automation] Send Daily Reminder Message for Health Form in Slack Channel at 10 AM

2. Fill out health questions from Google Form or Slack Workflow Form

3. Data is saved back to 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 Employee Lists and Filter Out Unfilled Entries

6. [Automation] Click “Generate & Send Message” to Automatically Send Reminder for Unfilled Fields & Auto Tag Recipients

7. Work done!

Results

(Personal Estimate)

• Filling in time can save about 30 seconds per employee per day.

• People Operations manages this task, saving about 20 to 30 minutes daily.

How It Works

Managing Sheets by writing App Script.

1. Store all external input data in the Responses Sheet

2. Write an App Script function to distribute the Responses data into Sheets by submission date. If a Sheet for the date doesn’t exist, create a new one. Name the Sheet using the date for easy identification and access.

3. Compare the current date’s sheet with the employee list to generate data for the sheet of those who have not filled in.

4. Read the Unfilled List Sheet, compose messages, and send them to the designated Slack channel

• Integrate Slack APP API to automatically read and import the employee list from a specified channel

• Use Slack UID tag <@UID> in message content to mention members who have not completed their information

Identification

The identity information linking Google Form and Slack is Email, so please ensure that employees use their company email to fill out the Google Form and also have their company email entered in their Slack profile.

Getting Started Hands-On

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

The article is a bit long; you can skip sections you already understand or create a copy from the final result to review, edit, and learn along the way.

Submission form: https://forms.gle/aqGDCELpAiMFFoyDA

Completed Result Google Sheet:

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

Steps are omitted. If you have questions, please Google them directly. Here, it is assumed you have already created and linked the health report form.

Remember to check “Collect emails” in the form:

Collect the respondent’s email for future list comparison.

How to Link Replies to Google Sheet?

Switch to the “Responses” tab above the form and click the “Google Sheet Icon.”

Change the Sheet Name in the Link:

It is recommended to rename the linked Sheet from “Form Responses 1” to “Responses” for easier use.

Create Slack Workflow Form Entry Point

After setting up the traditional Google Form entry, let’s add the Slack submission method first.

In any Slack chat window, find the blue lightning bolt ⚡️ located below the input box and click it.

In the menu below, enter “workflow” in the “Search shortcuts” and select “Open Workflow Builder”

Here you will find the Workflows you created or participated in. Click “Create” at the top right to create a new Workflow.

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

Workflow trigger method: select “Shortcut”

There are currently 5 types of Slack workflow trigger times:

• Shortcut: Manually trigger the “Blue Lightning ⚡️” option in the workflow menu. Click it 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 specific emoji… (Maybe this can be used to mark important messages as read by asking users to press a certain emoji, so you know who has read it?)

• Scheduled date & time: Schedule and trigger at the specified time… (EX: Timed reminder message)

• Webhook: External webhook trigger, an advanced feature that allows integration with third-party or custom APIs to connect internal workflows.

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

Choose which Channel input box to add this Workflow Shortcut under and enter the display name.

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

Shortcut created! Start building the workflow steps by clicking “Add Step” to add a step.

Choose the “Send a form” Step

Title: Input Form Title

Add a question: Enter the title of the first question (you may mark 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 within the same Workspace

• Select a channel or DM: Choose a member within the same Workspace, a Group DM, or a Channel

For example, “Select from a list”:

1. Add list item: You can add an option

2. Default selection: Choose the default option

3. Make this required: Set this question as mandatory

1. Add Question: You can add more questions

2. The “↓” and “⬆” on the right can adjust the order, and the “✎” can expand for editing.

3. You can choose whether to send the form submission content back to a Channel or a specific person.

You can also choose to send the reply to…:

• Person who clicked ….：Person who clicked this form (equivalent to the person who filled 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 submission content because we want to customize the message content in later steps.

Connecting Slack workflow from 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 select the “Add a spreadsheet row” step from Google Sheets for Workflow Builder.

1. First, complete the Google account authorization by clicking “Connect account”

2. Select a spreadsheet: Choose the target Google Sheet response, please select the Google Sheet created from the original Google Form.

3. Sheet: Same as above

4. Column name: The first column to be filled, here we first select Question One

Click the “Insert Variable” at the bottom right and select “Response to Question 1…”. After inserting, you can add other columns from the bottom left “Add Column”. Repeat this process to complete Question 2, Question 3, and so on.

Enter the person’s Email, optionally select “Person who submitted form”

Click the inserted variable and select “Email” to automatically fill in the submitter’s email.

• Mention (default): tag the User, Raw data is <@User ID>

• Name: User Name

• Email: User Email

The Timestamp field is a bit tricky. I’ll add the setup method later. First, click “Save” to save, then go to the top right 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 display the Workflow form you just created, which you can click to fill out and try.

Left: Computer / Right: Mobile Version

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

Success! However, the Timestamp column is empty. Next, let’s fix this issue.

Slack workflow from Obtaining Fill Time

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

At first, I whimsically entered =NOW() in the Column Value, but this made all records show the current time, which was completely wrong.

Thanks to the expert user from that Reddit post, a tricky method can be used to create a clean Timestamp Sheet with a row of data and a column containing =NOW(). First, use Update to force the field to refresh, then use Select to get the current Timestamp.

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

• Row: Similar to an ID, set it directly as “1”. It will be used later for Select & Update operations to specify the data row.

• Timestamp: Set the value =NOW() to always display the current time

• Value: Used to trigger the Timestamp field update time. The content is arbitrary; here, the submitter’s email is inserted. As long as it triggers the update, it works.

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

Return 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” and choose the previously used Sheet, then under “Sheet,” select the newly created “Timestamp” Sheet.

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

“Update these columns”
Select “Column name” as “Value”, then click “Insert variable” -> “Person who submitted” -> “Select Email”.

Click “Save” to finish! The timestamp update in the trigger Sheet is now complete. Next, we will read it out for use.

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

The Search section is the same as “Update a spreadsheet row,” click “Save.”

After saving and returning to the step list page, we can move the mouse over a step and drag it to change the order.

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

That is: Update triggers timestamp update -> Read timestamp -> Use it when adding a new row.

In “Add a spreadsheet row,” click “Edit” to modify:

Scroll to the bottom and click “Add Column” at the lower left. Then click “Insert a variable” at the lower right, find the “Timestamp” variable under the “Select a spreadsheet” section, and inject it.

After clicking “Save” to save the steps, return to the list page and click “Publish Change” at the top right to publish the changes.

At this point, let’s test the workflow shortcut again to see if the timestamp is written correctly.

Success!

Add Submission Receipt to Slack Workflow Form

Just like Google Form submission receipts, Slack workflow forms can also do this.

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

This time, choose “Send a message”

“Send this message to” select “Person who submitted form”

Enter the message content in order: the question title, select “Insert a variable” and choose “Response to question XXX,” and optionally add “Timestamp” at the end. Click “Save” to save the step, then click “Publish Changes”!

You can also use “Send a message” to send the filled results to a specific Channel or DM.

Success!

The setup of the Slack workflow form ends here. You can freely combine and experiment with other features.

Google Sheet with App Script!

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

First, in the Google Sheet top toolbar, select “Tools” -> “Script editor”

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

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

Distribute Responses Data to Sheets by Submission Date

function formatData() {
  var bufferSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses') // Sheet name storing responses
  
  var rows = bufferSheet.getDataRange().getValues();
  var fileds = [];
  var startDeleteIndex = -1;
  var deleteLength = 0;
  for(index in rows) {
    if (index == 0) {
      fileds = 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) { // Create new if not exists
      sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName, bufferSheet.getIndex());
      sheet.appendRow(fileds);
    }

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

  if (deleteLength > 0) {
    bufferSheet.deleteRows(startDeleteIndex, deleteLength); // Remove data from Responses after moving to target Sheets
  }
}

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 by pressing the button, automatic triggering when data is written is not possible).

1. First, create a new sheet and name it “Unfilled List”

2. In the top toolbar, select “Insert” -> “Drawing”

Use this interface to create 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”.

Clickable button to try the feature

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

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

You can click the bottom left “Advanced” -> “Go to Health Report (Responses) (unsafe)”

Click “Allow”

When the App Script shows “Running Script,” please do not press again to avoid duplicate execution.

Only after showing successful execution can it be run again.

Success! Group the entered data by date.

Get the current date’s sheet and compare it with the employee list to generate data for the sheet of those who have not filled in the form

Let’s add another piece of code:

// Compare the Employee List Sheet with Today's Filled Sheet, generate a list of those who haven't filled
function generateUnfilledList() {
  var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('員工名單') // Employee List Sheet name
  var unfilledListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('未填寫名單') // 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 the sheet for '+todayName+' or please run "Organize Filled Data" first');
    return;
  }

  var todayEmails = todayListSheet.getDataRange().getValues().map( x => x[1] ) // Get the Email Address column data list from today's Sheet (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, which is the header "Email Address" and meaningless
  // output: zhgchgli@gmail.com,alan@gamil.com,b@gmail.com...

  unfilledListSheet.clear() // Clear the Unfilled List... preparing to refill data
  unfilledListSheet.appendRow([todayName+" Unfilled List"]) // Show sheet title on the first row

  var rows = listSheet.getDataRange().getValues(); // Read Employee List Sheet
  for(index in rows) {
    if (index == 0) { // The first row is the header row, save it to add headers to the new data
      unfilledListSheet.appendRow(rows[index]);
      continue;
    }
    
    if (todayEmails.includes(rows[index][3])) { // If this employee's email is in today's Sheet Email Address, it means filled, continue to skip... (3 = Column D)
      continue;
    }

    unfilledListSheet.appendRow(rows[index]); // Write one row of data to Unfilled List Sheet
  }
}

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

You can click to test after completion:

The list was generated successfully! If no content appears, please first check:

• The employee list has been completed, or you can enter test data first.

• You need to finish the “Organize and Fill in Data” step first.

Read the Unfilled List Sheet, Compose Messages, and Send to the Specified Slack Channel

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

1. Slack bottom left “Apps” -> “Add apps”

2. Search for “incoming” in the search box on the right.

3. Click “Incoming WebHooks” -> “Add”

Select the Channel where you want to send the message if not filled in.

Note down the “Webhook URL” at the top.

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

Back to our Google Sheet Script

Add another Code block:

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

  if (result == ui.Button.YES) {
    var unfilledListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('未填寫名單') // Sheet name for unfilled list
    var rows = unfilledListSheet.getDataRange().getValues();
    var persons = [];
    for(index in rows) {
      if (index == 0 \|\| index == 1) { // Skip the header and column title rows
        continue;
      }
      
      var person = (rows[index][4] == "") ? (rows[index][2]) : ("<@"+rows[index][4]+">"); // Tag target: use Slack UID if available, otherwise show nickname; 2 = Column B / 4 = Column E
      if (person == "") { // Consider empty as invalid data, ignore
        continue;
      }
      persons.push("• "+person+'\n') // Add target to array
    }

    if (persons.length <= 0) { // If no one needs to be tagged, everyone has filled, cancel sending
      return;
    }

    var preText = "*[Health Report Announcement:loudspeaker:]*\nThe company cares about everyone's health. Please remind the following teammates to fill out the daily health status report. Thank you :wink:\n\nToday's list of unfilled health reports\n\n" // Message opening content...
    var postText = "\n\nFilling out the health status report helps the company understand teammates' health conditions. Please make sure to fill it out every day >< Thank you all :woman-bowing::skin-tone-2:" // Message closing content...
    var payload = {
      "text": preText+persons.join('')+postText,
      "attachments": [{
          "fallback": "Google Form link can be placed here",
          "actions": [
            {
                "name": "form_link",
                "text": "Go to Health Status Report",
                "type": "button",
                "style": "primary",
                "url": "Google Form link can be placed here"
            }
          ],
          "footer": ":rocket:Tip: Click the “:zap:️Lightning” below the input box -> “Shortcut Name” to fill out 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 of adding code, then add a button and assign the script — “postSlack”.

After completion, you can click to test:

Success!!! (The mention @U123456 did not work because the ID was made up)

The main features have been completed!

Note

Please note that the official recommendation is to use the new Slack APP API’s chat.postMessage to send messages. The simpler Incoming Webhook method will be deprecated in the future. This guide uses the simpler method for convenience but will need to be updated to the new Slack App API method in the next chapter, “Import Employee List.”

Import Employee List

Here, we need to create a Slack APP.

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

2. Click “Create New App” at the top right corner

1. Choose “From scratch”

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

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

1. Scroll down to the Scopes section

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

• channels:read

• users:read

• users:read.email

• If you want to switch to sending messages via an app, you can add chat.postMessage here.

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

If Scopes are added, you must come back here and reinstall.

1. After installation, obtain 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 URL from browser:

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

The CXXXX is the Channel ID for this Channel. Note this message.

10.

Back to our Google Sheet Script

Add another Code block:

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 according to Columns

    var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('員工名單'); // Employee List Sheet name
    listSheet.appendRow(row);
  }
}

But this time, we don’t need to add a button because import is only required once; so just save and run directly.

First, press “control” + “s” to save. In the dropdown menu above, select “loadEmployeeList,” then 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 found directly on Slack:

Click the object whose UID you want to see, then click “View full profile”

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

DONE！

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

The completed file is available; you can create a copy from the following Google Sheet and modify it as needed:

Supplement

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

• Currently, there is no way to use a hyperlink to open a Form via Shortcut.

• Google Sheet App Script Prevent Duplicate Execution:

If you want to prevent accidentally pressing again during execution and causing duplicate runs, you can add the following 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 the following at the end of the function execution:

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

Replace FUNCTIONNAME with the target function name.

Control execution with a Global variable.

Applications Related to iOS Development

Can be used to integrate CI/CD, wrapping the original ugly command operations with a GUI. For example, combined with the Slack Bitrise APP, use Slack Workflow form to trigger Build commands:

After sending, the command will be sent to the private channel with the Bitrise APP, e.g.:

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

This can trigger Bitrise to execute the CI/CD flow.

Further Reading

• Automate Routine Tasks with Python + Google Cloud Platform + Line Bot

• Using Google Apps Script to Forward Gmail Messages to Slack

• Crashlytics + Big Query Build a More Real-Time and Convenient Crash Tracking Tool

• Crashlytics + Google Analytics Automatic Query for App Crash-Free Users Rate

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

If you have automation-related optimization needs, feel free to contact me. Thank you.

---

This post was originally published on Medium (View original post), and automatically converted and synced by ZMediumToMarkdown.

Improve this page on Github.

Follow Me on Medium 1K+ Followers