Slack Workflow｜打造全自动 WFH 员工健康回报系统，结合 Google Sheets 与 App Script 提升效率

Slack 打造全自动 WFH 员工健康状况回报系统

玩转 Slack Workflow 搭配 Google Sheet with App Script 增加工作效率

Photo by Stephen Phillips — Hostreviews.co.uk

前言

因应全面居家工作，公司关心所有成员的健康，每日均需回报身体有无状况并由 People Operations 统一纪录管理。

我们的 优化前 的 Flow

1. [自动化] Slack Channel 每日早上 10 点定时发送提醒大家健康表单的讯息(优化前唯一自动化的地方)

2. 员工点击连结打开 Google Form 填写健康问题

3. 资料存回 Google Sheet 回应纪录

4. [人工] People Operations 于每日接近下班时比对名单筛出忘记填写的员工

5. [人工] 于 Slack Channel 发送填写提醒讯息 & 一个一个 tag 忘记填写的人

以上是敝司的健康回报追踪流程，每间公司依照规模及运作方式一定有所不同，本文仅以此做为优化范例，学习 Slack Workflow 使用、基本 App Script 撰写，实际还是要 by case 实作。

问题点

• 需跳出 Slack Context 使用浏览器打开 Google Form 网页才能填写，尤其在手机上更不方便

• Google Form 仅能自动带入 Email 讯息，无法自动加上填写人名称、部门资讯

• 每日人工比对、人工 tag 非常花费人力时间

解决方案

做过蛮多自动化的小东西，这个流程资料源固定(员工名单)、条件单纯、动作很例行；一看就觉得很适合自动化，一开始没做是因为找不到好的填写方式(实际是找不到有趣可研究的点)；所以也就放著没管，直到看到 海总理的这则 PO 文 才发现 Slack Workflow 不只是可以做定时传讯息，还有 Form 表单的功能：

图片取自： 海总理

这下手就开始痒了啊！！

如果能搭配 Slack Workflow From 加上传讯息的自动化，岂不是能解决上面提到的 所有痛点 ，原理可行！于是开始著手实作。

优化后 的 Flow

首先上一下优化后的流程及结果。

1. [自动化] Slack Channel 每日早上 10 点定时发送提醒大家健康表单的讯息

2. 从 Google Form 或 Slack Workflow Form 填写健康问题

3. 资料均存回 Google Sheet 回应纪录

4. People Operations 于每日接近下班时点击「产生未填写名单」按钮

5. [自动化] 使用 App Script 比对员工名单、填写名单筛出未填写名单

6. [自动化] 点击「产生＆发送讯息」自动发送未填写提醒＆自动 tag 对象

7. 收工！

成效

(个人预估)

• 填写时间每位员工每日能减少约 30 秒

• People Operations 处理这件事每日能减少约 20 ~ 30 分钟

运作原理

透过撰写 App Script 来管理 Sheet。

1. 将外部输入的资料都存放在 Responses Sheet

2. 撰写 App Script Function 将 Responses 的资料依照填写日期分发到各日期的 Sheet，若无则建立新的日期 Sheet，Sheet 名称直接使用日期，方便辨识取用

3. 取得当前日期的 Sheet 与员工名单比对，产生未填写名单 Sheet 的资料

4. 读取未填写名单 Sheet 组合出讯息并发送到指定 Slack Channel

• 串接 Slack APP API 可自动读取指定 Channel 汇入员工名单

• 讯息内容使用 Slack UID Tag <@UID> 就能标记未填写的成员。

身份识别

串起 Google From 与 Slack 的身份识别资讯是 Email，所以请确保公司同仁都是使用公司 Email 填写 Google Form、Slack 个人资讯部分也都有填写公司 Email。

开始动手做

问题、优化方式、成果讲完后，接下来来到实作环节；让我们一起一步步完成这个自动化 Case。

篇幅有点长，可依照略过自己已了解的区块，或直接从完成结果建立副本，边看边改边学。

完成结果表单： https://forms.gle/aqGDCELpAiMFFoyDA

完成结果 Google Sheet：

建立健康回报 Google Form 表单 & 连结回复到 Google Sheet

步骤省略，有问题请直接 Google，这边假定你已经建立＆连结好了健康回报表单。

表单要记得勾选「Collect emails」：

收集填写者的 Email 以利之后比对名单用。

怎么连结回复到 Google Sheet？

于表单的上方切换到「回复」点击「Google Sheet Icon」即可。

更改连结的 Sheet 名称：

这边建议将连结的 Sheet 名称由 Form Responses 1 改为 Responses 方便使用。

建立 Slack Workflow Form 填写入口

传统的 Google From 填写入口有了之后，我们先来新增 Slack 填写方式。

于 Slack 任意对话视窗中找到「 输入匡 下方 」的「蓝色闪电⚡️」点击下去

在选单底下「Search shortcuts」中输入「workflow」选择「Open Workflow Builder」

这边会列出你建立的或参与的 Workflow，点选右上角「Create」建立新 Workflow

第一步，输入 workflow 名称（Workflow Builder 介面显示用）

Workflow 触发方式，选择「Shortcut」

目前一共有 5 种 slack workflow 触发时间点：

• Shortcut：手动触发「蓝色闪电⚡️」选项，会出现在 workflow 选单中，点击即可开始 workflow。

• New channel member：当 Target Channel 有新成员加入时…. (EX: 欢迎讯息)

• Emoji reactions：当有人对 Target Channel 中的讯息按下指定表情符号时…(或许可拿来做重要讯息已读请按 XXX Emoji，以此知道谁已读了？)

• Scheduled date & time：排程，指定时间到时…(EX: 定时发提醒回报讯息)

• Webhook：外部 Webhook 触发，进阶功能，可与第三方或自己架 API 串起内部工作流程。

这边我们选择「Shortcut」建立手动触发选项。

选择这个 Workflow Shortcut 要加入在「哪一个 Channel 输入匡」之下及输入「要显示的名称」

*一个 workflow shortcut 仅能加入在一个 channel 中

Shortcut 建立完成！开始建立 workflow 步骤，点击「Add Step」加入步骤

选择「Send a form」Step

Title ：输入表单标题

Add a question ：输入第一个问题的题目（可自行在标题标注问题编号 ex: 1.,2.,3….）

Choose a question type ：

• Short answer：单行输入匡

• Long answer：多行输入匡

• Select from a list：单选列表

• Select a person：选择一位同个 Workspace 内的成员

• Select a channel or DM：选择一位同个 Workspace 内的成员 或 Group DM 或 Channel

「Select from a list」为例：

1. Add list item：可新增一个选项

2. Default selection：选择预设选项

3. Make theis required：将此问题设为必填

1. Add Question：可新增更多问题

2. 右方「↓」「⬆」可调整顺序、「✎」可展开编辑

3. 可选择是否要将表单填写内容回传至 Channel 或 某人

也可以选择传送回复到…：

• Person who clicked ….：点击这个表单的人（形同填写的人）

• Channel where workflow started：这个 workflow 添加到的 Channel

表单完成后点击「Save」储存步骤。

*这边我们取消勾选将表单填写内容回传，因为想要在后面步骤自行客制化讯息内容。

将 Slack workflow from 与 Google Sheet 串接

如果还没有将 Google Sheet App 加入到 Slack 可先 点此安装 APP 。

继上一步后，点击「Add Step」加入新步骤，我们选择 Google Sheets for Workflow Builder 的「Add a spreadsheet row」步骤。

1. 首先要完成 Google 帐号的授权，点击「Connect account」

2. Select a spreadsheet：选择目标回应的 Google Sheet，请选择一开始建立的 Google Form 之 Google Sheet

3. Sheet：同上

4. Column name：第一个欲填入值的 Column，这边先选问题ㄧ

点击右下角「Insert Variable」选择「Response to 问题一…」，插入之后可由左下角「Add Column」加入其他栏位，以此类推完成问题二、问题三….

填写人的 Email，可选择「Person who submitted form」

在点击插入的变数，选择「Email」即可自动带入填写人的 Email。

• Mention (default)：tag 该 User，Raw data 是 <@User ID>

• Name：User 名称

• Email：User Email

Timestamp 栏位比较 tricky 等下再补充设定方法，先点「Save」储存后回到页面右上角按「Publish」发布 Shortcut。

看到发布成功讯息后，可以回到 Slack Channel 试试看。

这时候点闪电之后会出现刚刚建立的 Workflow form，可以点来填写玩玩。

左：电脑 / 右：手机版

我们可以填写资讯「Submit」测试看看是否正常。

成功！但可以看到 Timestamp 栏位为空，下一步我们来解决这个问题。

Slack workflow from 取得填写时间

Slack workflow 没有 current timestamp 的 global variable 可用，至少目前还没有，只找到一篇 reddit 上的许愿文章 。

一开始异想天开在 Column Value 输入 =NOW() 但这样所有纪录的时间永远是当前时间，完全错误。

同样拜 reddit 那篇文章 大神网友提供的 tricky 方法，可以建一个干净的 Timestamp Sheet 里面放一个列资料、栏位 =NOW() 先用 Update 迫使栏位变为最新，在 Select 得到当前 Timestamp。

如上图结构，点此 查看范例 。

• Row： 类似 ID 的用处，直接设「1」，之后设定 Select & Update 会要用到，告知资料列。

• Timestamp：设定值 =NOW() 让他永远显示当前时间

• Value：用以触发 Timestamp 栏位更新时间，内容随意，这边是把填写人的 Email 塞进来放，反正只要能触发更新就好。

可在 Sheet 上按右键「Hide Sheet」隐藏此 Sheet，因为没有要让外部使用。

回到 Slack Workflow Builder 编辑刚刚 建立的 workflow form。

点击「Add Step」新增步骤：

往下滑选择「Update a spreadsheet row」

「Select a spreadsheet」选择刚刚的 Sheet，「Sheet」选择新建立的「Timestamp」Sheet。

「Choose a column to search」选择「Row」，Define a cell value to find 输入「1」。

「Update these columns」「Column name」选择「Value」、「Value」点选「Insert variable」->「Person who submitted」->「选择 Email」。

点「Save」完成！现在已经完成触发 Sheet 中的 timestamp 更新了，再来是读取出来用。

回到编辑页后再点一次「Add Step」加入新步骤，这次选「Select a spreadsheet row」我们要读取 Timestamp 出来。

Search 部分同「Update a spreadsheet row」，按「Save」。

Save 完回到步骤列表页，我们可以把滑鼠移到步骤上用拖曳更改顺序。

将顺序改「Update a spreadsheet row」->「Select a spreadsheet」->「Add a spreadsheet row」。

意即：Update 触发 timestamp 更新 -> 读取 Timestamp -> 在新增 Row 时拿来用。

在「Add a spreadsheet row」点「Edit」编辑：

拉到最下面按左下角「Add Column」在点右下角「Insert a variable」，找到「Select a spreadsheet」Section 中的「Timestamp」变数，注入进去。

按「Save」储存步骤后回到列表页，右上角点「Publish Change」发布更改。

这时候我们再测试一次 workflow shortcut 看看 timestamp 有没有正常写入。

成功！

Slack workflow form 增加填写回执

同 Google Form 填写回执，Slack workflow form 也可以。

在编辑步骤页我们可以再加入一个步骤，点击「Add Step」。

这次选择「Send a message」

「Send this message to」选择「Person who submitted form」

讯息内容依序输入题目名称、「Insert a variable」选择「Response to 题目 XXX」，也可在最后插入「Timestamp」，按「Save」储存步骤后再按「Publish Changes」即可！

另外也可使用「Send a message」将填写结果传送到特定 Channel 或 DM。

成功！

Slack workflow form 的设定大概到此结束，其他玩法可以自由搭配发挥。

Google Sheet with App Script!

接下我们需要撰写 App Script 来处理填写资料。

首先在 Google Sheet 上方工具栏选择「Tools」->「Script editor」

可以点击左上角给专案一个名称。

现在我们可以开始撰写 App Script！App Script 是基于 Javascript 设计，所以可以直接使用 Javascript 程式码用法搭配 Google Sheet 的 lib。

将 Responses 的资料依照填写日期分发到各日期的 Sheet

function formatData() {
  var bufferSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses') // 储存回复的 Sheet 名称
  
  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"); // 将 Date 转换成 String，使用美国日期格式 MM/DD/YYYY
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); // 取得 MM/DD/YYYY Sheet
    if (sheet == null) { // 若无则新增
      sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName, bufferSheet.getIndex());
      sheet.appendRow(fileds);
    }

    sheet.appendRow(rows[index]); // 将资料新增至日期 Sheet
    if (startDeleteIndex == -1) {
      startDeleteIndex = +index + 1;
    }
    deleteLength += 1;
  }

  if (deleteLength > 0) {
    bufferSheet.deleteRows(startDeleteIndex, deleteLength); // 搬移到指定 Sheet 后，移除 Responses 里的资料
  }
}

在 Code 区块中贴上以上程式码，并按「control」+「s」储存。

再来我们要在 Sheet 中新增触发按钮（ 只能手动按按钮触发，无法在资料写入时做自动分 ）

1. 首先在建立一个新的 Sheet，取名「未填写名单」

2. 上方工具列选择「Insert」->「Drawing」

使用此介面，拉出一个按钮。

「Save and Close」后可调整、移动按钮；点击右上角「…」选择「Assign script」

输入「formatData」function 名称。

可点击加入的按钮试试功能

若出现 「Authorization Required」则点选「Continue」完成验证

在身份验证的过程中会出现「Google hasn’t verified this app」这是正常的，因为我们写的 App Script 没有经过 Google 验证，不过没关系这是写给自己用的。

可点选左下角「Advanced」->「Go to Health Report (Responses) (unsafe)」

点击「Allow」

App Script 执行中会显示「Running Script」这时候请勿再按，避免重复执行。

显示执行成功后，才能再次执行。

成功！将填写资料依照日期分组。

取得当前日期的 Sheet 与员工名单比对，产生未填写名单 Sheet 的资料

我们再加入一段 Code：

// 与员工名单 Sheet ＆ 本日填写 Sheet 比对，产出未填写名单
function generateUnfilledList() {
  var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('员工名单') // 员工名单 Sheet 名称
  var unfilledListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('未填写名单') // 未填写名单 Sheet 名称
  var today = new Date();
  var todayName = today.toLocaleDateString("en-US");

  var todayListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(todayName) // 取得本日 MM/DD/YYYY Sheet
  if (todayListSheet == null) {
    SpreadsheetApp.getUi().alert('找不到'+todayName+'本日的 Sheet 或请先执行「整理填写资料」');
    return;
  }

  var todayEmails = todayListSheet.getDataRange().getValues().map( x => x[1] ) // 取得本日 Sheet Email Address 栏位资料列表 (1 = Column B)
  // index start from 0, so 1 = Column B
  // output: Email Address,[email protected],[email protected],[email protected]...
  todayEmails.shift() // 移除第一个资料，第一个是栏位名称「Email Address」无意义
  // output: [email protected],[email protected],[email protected]...

  unfilledListSheet.clear() // 清除未填写名单...准备重新填入资料
  unfilledListSheet.appendRow([todayName+" 未填写名单"]) // 第一行显示 Sheet 标题

  var rows = listSheet.getDataRange().getValues(); // 读取员工名单 Sheet
  for(index in rows) {
    if (index == 0) { // 第一列是标题栏位列，存下来，让后续产生资料也可补上第一列标题
      unfilledListSheet.appendRow(rows[index]);
      continue;
    }
    
    if (todayEmails.includes(rows[index][3])) { // 如果本日 Sheet Email Address 中有此员工的 Email 则代表有填写，continue 略过... (3 = Column D)
      continue;
    }

    unfilledListSheet.appendRow(rows[index]); // 写入一行资料到未填写名单 Sheet
  }
}

一样储存后，照前面加入 Code 的方法，再加入一个按钮并 Assign script — 「generateUnfilledList」。

完成后可点击测试：

未填写名单产生成功！如果没有出现内容请先确定：

• 员工名单已填写，或可先输入测试资料

• 要先完成「整理填写资料」动作

读取未填写名单 Sheet 组合出讯息并发送到指定 Slack Channel

首先我们要加入 Incoming WebHooks App 到 Slack Channel，我们会透过此媒介来传送讯息。

1. Slack 左下角「Apps」->「Add apps」

2. 右边搜寻匡搜寻「incoming」

3. 点击「Incoming WebHooks」->「Add」

选择未填写讯息想要传到的 Channel。

记下最上方的「Webhook URL」

往下滑可设定传送讯息时，传送 Bot 显示的名称及大头贴；改完记得按「Save Settings」。

回到我们的 Google Sheet Script

再加入一段 Code：

function postSlack() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
     '您确定要发送讯息?',
     '发送未填写提醒讯息到 Slack Channel',
      ui.ButtonSet.YES_NO);
  // 避免误触，先询问确认

  if (result == ui.Button.YES) {
    var unfilledListSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('未填写名单') // 未填写名单 Sheet 名称
    var rows = unfilledListSheet.getDataRange().getValues();
    var persons = [];
    for(index in rows) {
      if (index == 0 \\|\\| index == 1) { // 略过标题、栏位标题那两行
        continue;
      }
      
      var person = (rows[index][4] == "") ? (rows[index][2]) : ("<@"+rows[index][4]+">"); // 标记对象，如果有 slack uid 优先使用，没有则单纯显示暱称；2 = Column B / 4 = Column E
      if (person == "") { // 都没视为异常资料，忽略
        continue;
      }
      persons.push("• "+person+'\n') // 将对象存入阵列
    }

    if (persons.length <= 0) { // 无对象需要被标记通知时，大家都有填，取消讯息送出
      return;
    }

    var preText = "*[健康回报表公告:loudspeaker:]*\n公司关心各位的身体健康，烦请以下队友记得每日填写健康状况回报，谢谢:wink:\n\n今日未填健康状况回报名单\n\n" // 讯息开头内容...
    var postText = "\n\n填写健康状况回报能让公司了解队友们的身体状况，烦请队友们每日都要确实填写唷>< 谢谢大家:woman-bowing::skin-tone-2:" // 讯息结尾内容...
    var payload = {
      "text": preText+persons.join('')+postText,
      "attachments": [{
          "fallback": "这边可放 Google Form 填写连结",
          "actions": [
            {
                "name": "form_link",
                "text": "前往健康状况回报",
                "type": "button",
                "style": "primary",
                "url": "这边可放 Google Form 填写连结"
            }
          ],
          "footer": ":rocket:小提示：点击输入匡下方的「:zap:️闪电」->「Shortcut Name」，即可直接填写。"
        }
      ]
    };
    var res = UrlFetchApp.fetch('这边输入你 slack incoming app 的 Webhook URL',{
      method             : 'post',
      contentType        : 'application/json',
      payload            : JSON.stringify(payload)
    })
  }
}

一样储存后，照前面加入 Code 的方法，再加入一个按钮并 Assign script — 「postSlack」。

完成后可点击测试：

成功！！！(显示 ＠U123456 没成功标记人是因为 ID 是我乱打的)

到此主要的功能都已完成！

备注

请注意官方建议使用新的 Slack APP API 的 chat.postMessage 来传送讯息，Incoming Webhook 简便的这个方式之后会弃用，这边偷懒没有使用，可搭配下一章「汇入员工名单」会需要 Slack App API 一起调整成新方法。

汇入员工名单

这边会需要我们创建一个 Slack APP。

1.前往 https://api.slack.com/apps

1. 点击右上角「Create New App」

1. 选择「 From scratch 」

1. 输入「 App Name 」跟 你想要加入的 Workspace

1. 建立成功后，在左边选单选择「OAuth & Permissions」设定页

1. 往下滑到 Scopes 区块

依次「Add an OAuth Scope」以下项目：

• channels:read

• users:read

• users:read.email

• 如果想改用 APP 发讯息可在此加入 chat.postMessage

1. 回到最上面点击「Install to workspace」or「Reinstall to workspace」

*如果 Scopes 有新增，也要回来这点重新安装。

1. 安装完成，取得复制 Bot User OAuth Token

2. 使用网页版 Slack 打开想要汇入名单的 Channel

从浏览器取得网址：

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

其中 CXXXX 就是这个 Channel 的 Channel ID，记下此讯息。

10.

回到我们的 Google Sheet Script

再加入一段 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]; // 依照 Column 填入

    var listSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('员工名单'); // 员工名单 Sheet 名称
    listSheet.appendRow(row);
  }
}

但这次我们不需要再加入按钮，因为汇入仅第一次需要；所以只需存挡后直接执行即可。

首先按「control」+「s」存档，上方下拉选单改选择「loadEmployeeList」，点击「Run」就会开始汇入名单到员工名单 Sheet。

手动新增新员工资料

尔后如果有新员工加入，可直接在员工名单 Sheet 新增一列，填入资讯，Slack UID 可在 Slack 上直接查询：

点击要查看 UID 的对象，点击「View full profile」

点击「More」选择「Copy member ID」即是 UID。 UXXXXX

DONE！

以上所有步骤都已完成，可以开始自动化的追纵员工的健康状况。

完成档如下，可直接从以下 Google Sheet 建立副本修改后使用：

补充

• 如果想要用 Scheduled date & time 定时发送 form 讯息，要注意这情况下的 form 只能被填一次，所以不适合在这边使用…（至少目前版本还是这样），所以 Scheduled 填写提醒讯息依然只能用纯文字＋Google Form 连结。

• 目前没有办法用超连结连到 Shortcut 打开 Form

• Google Sheet App Script 防止重复执行：

如果要防止不小心在执行中又再次按到导致重复执行，可在 function 一开始加上：

if (PropertiesService.getScriptProperties().getProperty('FUNCTIONNAME') == 'true') {
  SpreadsheetApp.getUi().alert('忙碌中...请稍后再试');
  return;
}
PropertiesService.getScriptProperties().setProperty('FUNCTIONNAME', 'true');

Function 执行结束时加上：

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

FUNCTIONNAME 取代为目标 Function 名称。

用一个 Global 变数管制执行。

与 iOS 开发相关的应用

可用来串 CI/CD，用 GUI 包装原本丑丑的指令操作，例如搭配 Slack Bitrise APP，用 Slack Workflow form 组合启动 Build 命令：

送出之后会发送指令到有 Bitrise APP 的 private channel，EX:

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

就能触发 Bitrise 执行 CI/CD Flow。

延伸阅读

• 使用 Python+Google Cloud Platform+Line Bot 自动执行例行琐事

• 运用 Google Apps Script 转发 Gmail 信件到 Slack

• Crashlytics + Big Query 打造更即时便利的 Crash 追踪工具

• Crashlytics + Google Analytics 自动查询 App Crash-Free Users Rate

有自动化相关优化需求也欢迎 发案给我 ，谢谢。