こんにちは、MSPの田所です。
タスクの管理って悩ましいですよね。
どんなタスクがあって、期限はいつで、誰がどのタスクを完了したのか、していないのか。
人数が増えれば増えるほど管理の難易度は高くなっていきます。
そして
「タスク内容の変更って案内したっけ？」
「このタスクってもうリマインダーしたやつだっけ？」
と段々収拾がつかなくなってきます。
今回はそんな悩みを軽減するチェックリストのご紹介です。
Google スプレッドシート、Google Apps Script、Slack を使って進捗確認を格段に楽にします。
1. こんな機能
Google スプレッドシートにチェックリストを作って
チェックがついていない人には定期的に Slack でメンションしてお知らせ
2. スプレッドシートの作成
CheckList シートを作成します。
1行目にステータス、2行目にタスク、3行目に期限を設定します。
タスクにはリンクを埋め込んでも OK です。
A列には名前を記載します。
セルにチェックボックスを設定します。
Google スプレッドシートでは、チェックをつけると “TRUE”、チェックを外すと “FALSE” 扱いになります。
この “FALSE” を検知する仕組みを作ります。
SlackID シートを作成します。
A列に Slack Name、B列に Slack User ID を記載します。
Slack User ID は U 始まりのものです。
Slack のプロフィール画面の “メンバー ID をコピー” から取得できます。
3. Slack App の作成
slack api のサイトで Slack App を作成します。
Slack App を介して Slack と スプレッドシートが情報をやり取りできるようになります。
Create an app から作成可能です。
アプリ名と Slack ワークスペースを入力してアプリを作成します。
アプリが作成され、情報が表示されます。
App Home に移動します。
App Display Name を入力します。
App を正しく認証するために Bot Name が必須となります。
OAuth & Permissions に移動します。
Scopes までスクロールして Bot Token Scopes の “Add an OAuth Scope” から Scope を追加します。
以下の OAuth Scope を追加します。
これにより Slack App が Slack ワークスペースから情報を取得したり書き込みしたりできるようになります。
- channels:read
- chat:write
- incoming-webhook
- users:read
Incoming Webhooks に移動し、”Add New Webhook to Workspace” をクリックします。
Slack チャンネルを指定して、権限リクエストを許可します。
Webhook URL を取得します。
Google Apps Script から Slack にメッセージを投稿するためのエンドポイントとなります。
Webhook URL を取得した時点でアプリがインストールされた状態になります。
権限やチャンネル情報など、アプリに変更を加えた場合には再インストールが必要です。
4. Google Apps Script (GAS) の作成
スプレッドシートの拡張機能から Apps Script を開きます。
新規プロジェクトが作成されます。
プロジェクト名を記入してからコードを入力します。
コードは長いのでブログの最後に載せています。
メニューの “プロジェクトの設定” からスクリプトプロパティを追加します。
以下を入力します。
これらはコードを実行する時に使う環境変数となります。
- LINK_TO_CHECKLIST：スプレッドシートのリンク
- SHEET_NAME：チェックリストのシート名
- SLACK_ADMIN_USER_ID：管理者の Slack User ID
- SLACK_API_TOKEN：Slack App の Bot User OAuth Token
- SLACK_CHANNEL_ID：通知する Slack チャンネル ID
- SLACK_WEBHOOK_URL：Slack App の Webhook URL
コードを実行します。
初回実行時にはアクセス権限を許可します。
コードの実行結果を確認します。
上手くいかない場合はデバッグを活用しながらコードを修正します。
チェックリストの内容を元に、このように Slack に通知されます。
チェックがついていない人だけメンションするようになっています。
また全員チェックがついた完了タスクは通知されません。
なかなか便利ですね。
5. トリガーの設定
トリガーに移動し、トリガーを追加します。
例えば毎朝 9時頃に通知する設定にすれば、未完了タスクを放置する心配もありません。
トリガーが追加されました。
6. 便利機能
メンバーやタスクをどんどん追加していっても問題ありません。
また詳細は割愛しますが、SlackID シートに記載がなかった時の処理、タスクに改行が入っていた時の処理、エラーが起きた時の通知など、いくつか工夫ポイントがコードに込めてあったりします。
途中参加のおじいちゃんも大満足です。
おわりに
みなさま快適なタスク管理を！
おしまい
コード
主な処理内容
- CheckList でタスクごとにチェック状況を確認
- CheckList の名前が SlackID シートに記載されているか確認
- もし記載されていなければ、CheckList の名前を対象 Slack チャンネル内から抽出して SlackID シートを更新
- タスクが終わっていない人に Slack チャンネルでメンション通知
function main() {
try {
Logger.log("Starting main function...");
const untickedByTask = findUntickedByTask();
const allPersonsPresent = verifyMembersInSlackID(untickedByTask);
if (!allPersonsPresent) {
Logger.log('Not all persons are present in SlackID sheet. Triggering reload from Slack.');
populateSlackIdSheet();
}
if (untickedByTask.length > 0) {
sendSlackNotificationsByTask(untickedByTask);
} else {
Logger.log("All tasks are completed. Sending no news notification.");
sendNoTasksNotificationToSlack([]); // No unticked members, send default message
}
Logger.log("Main function completed.");
} catch (error) {
logError('Main function error: ' + error.message);
}
}
// Step1: Check for Unticked Tasks by Task (with title and link extraction and date formatting)
function findUntickedByTask() {
const SHEET_NAME = PropertiesService.getScriptProperties().getProperty('SHEET_NAME');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
const range = sheet.getDataRange();
const values = range.getValues();
const untickedByTask = [];
// Loop through the task columns (starting from B onwards)
for (let j = 1; j < values[0].length; j++) { // Start from column B
const taskTitleRichText = sheet.getRange(2, j + 1).getRichTextValue(); // Get RichTextValue for task title in row 2
const taskDueDateRaw = values[2][j]; // Task due date is in row 3
if (!taskTitleRichText) break; // Stop if we reach a blank cell in the header (row 2)
const taskTitle = taskTitleRichText.getText(); // Get the text (task title)
const taskLink = taskTitleRichText.getLinkUrl(); // Get the link URL
// Format due date if it is a valid date
let taskDueDate;
if (taskDueDateRaw instanceof Date) {
taskDueDate = Utilities.formatDate(taskDueDateRaw, Session.getScriptTimeZone(), "MM/dd");
} else {
taskDueDate = taskDueDateRaw; // In case the due date isn't a valid date (optional handling)
}
const untickedMembers = [];
// Loop through the members (rows, skip the first two, which are headers)
for (let i = 2; i < values.length; i++) {
const member = values[i][0]; // Column A contains the member name
if (values[i][j] === false) { // Only check if the value is explicitly false
untickedMembers.push(member);
}
}
// Add the task details and unticked members to the result
if (untickedMembers.length > 0) {
untickedByTask.push({
taskTitle: taskTitle,
taskDueDate: taskDueDate,
untickedMembers: untickedMembers,
taskLink: taskLink // Use existing link
});
}
}
Logger.log("Finished checking tasks. Unticked by task: " + JSON.stringify(untickedByTask));
return untickedByTask;
}
// Step2: Check if All Members Are Registered in SlackID Sheet (with partial matching)
function verifyMembersInSlackID(untickedByTask) {
Logger.log("Verifying members in SlackID sheet...");
const slackIdSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SlackID');
const lastRow = slackIdSheet.getLastRow();
// Ensure there are rows beyond the header
if (lastRow <= 1) {
Logger.log('SlackID sheet is empty or only contains headers.');
return false; // Assume not all persons are present
}
const slackIdData = slackIdSheet.getRange(2, 1, lastRow - 1, 2).getValues();
// Check each unticked member for each task
for (const task of untickedByTask) {
for (const member of task.untickedMembers) {
const normalizedMember = member.trim().toLowerCase();
let isMemberFound = false;
// Loop through SlackID data and check for partial matches
for (const row of slackIdData) {
const slackName = row[0] ? row[0].trim().toLowerCase() : '';
// Use partial matching (includes) to check if the member is in SlackID sheet
if (slackName.includes(normalizedMember)) {
isMemberFound = true;
break;
}
}
if (!isMemberFound) {
Logger.log(`Person ${member} not found in SlackID sheet.`);
return false; // If any member is not found, return false
}
}
}
Logger.log('All persons found in SlackID sheet.');
return true;
}
// Step3: Populate SlackID sheet if required
function populateSlackIdSheet() {
const slackApiToken = PropertiesService.getScriptProperties().getProperty('SLACK_API_TOKEN');
const slackChannelId = PropertiesService.getScriptProperties().getProperty('SLACK_CHANNEL_ID');
const membersResponse = UrlFetchApp.fetch(`https://slack.com/api/conversations.members?channel=${slackChannelId}`, {
method: 'get',
headers: {
'Authorization': `Bearer ${slackApiToken}`
}
});
const membersData = JSON.parse(membersResponse.getContentText());
if (!membersData.ok) {
Logger.log('Error fetching channel members: ' + membersData.error);
return;
}
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SlackID");
sheet.clear();
sheet.appendRow(['Slack Name', 'Slack User ID']);
for (const userId of membersData.members) {
const userInfoResponse = UrlFetchApp.fetch(`https://slack.com/api/users.info?user=${userId}`, {
method: 'get',
headers: {
'Authorization': `Bearer ${slackApiToken}`
}
});
const userInfoData = JSON.parse(userInfoResponse.getContentText());
if (userInfoData.ok) {
const realName = userInfoData.user.profile.real_name;
const slackUserId = userInfoData.user.id;
sheet.appendRow([realName, slackUserId]);
}
}
Logger.log('SlackID sheet has been populated.');
}
// Step4: Send Notification to Slack
function sendSlackNotificationsByTask(untickedByTask) {
Logger.log("Preparing Slack notifications by task...");
const linkToChecklist = PropertiesService.getScriptProperties().getProperty('LINK_TO_CHECKLIST');
let message = `<${linkToChecklist}|チェックリスト>の確認をお願いします:pray:\n\n--------------------------------------------------\n\n`;
untickedByTask.forEach(task => {
const taskMentions = task.untickedMembers.map(member => {
const slackIdOrName = getSlackIdForPerson(member); // Use the partial matching and fallback function
return slackIdOrName.startsWith('U') ? `<@${slackIdOrName}>` : `@${slackIdOrName}`; // If it starts with 'U', assume it's a Slack User ID
}).join(' ');
const taskWithLink = task.taskLink ? `<${task.taskLink}|${task.taskTitle.replace(/\n/g, ' ')}>` : task.taskTitle;
// Check if taskDueDate is blank or not
if (task.taskDueDate) {
message += `${taskWithLink}（期限：${task.taskDueDate}）\n${taskMentions}\n\n--------------------------------------------------\n\n`;
} else {
message += `${taskWithLink}（期限指定なし）\n${taskMentions}\n\n--------------------------------------------------\n\n`;
}
});
const slackWebhookUrl = PropertiesService.getScriptProperties().getProperty('SLACK_WEBHOOK_URL');
const payload = JSON.stringify({ text: message });
const options = {
method: 'post',
contentType: 'application/json',
payload: payload,
};
UrlFetchApp.fetch(slackWebhookUrl, options);
Logger.log("Slack notification sent: " + message);
}
// Helper: Check partial match and fallback of users for Step4
function getSlackIdForPerson(personName) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SlackID");
const lastRow = sheet.getLastRow();
// Ensure there are rows beyond the header
if (lastRow <= 1) {
Logger.log(`SlackID sheet is empty or only contains headers. Cannot find Slack ID for ${personName}.`);
return personName; // Fallback to name
}
const data = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
// Normalize the person name for case-insensitive comparison
const normalizedPersonName = personName.toLowerCase().trim();
for (const row of data) {
const slackName = row[0] ? row[0].toLowerCase().trim() : '';
// Use includes() for partial match
if (slackName.includes(normalizedPersonName)) {
return row[1]; // Return Slack User ID if a partial match is found
}
}
Logger.log(`Partial match Slack ID not found for ${personName}. Using name as fallback.`);
return personName; // Fallback to the original name if Slack ID is not found
}
// Send No news is good news!
function sendNoTasksNotificationToSlack() {
const slackWebhookUrl = PropertiesService.getScriptProperties().getProperty('SLACK_WEBHOOK_URL');
const slackMessage = {
text: "No news is good news! :tada:" // The message with no mention
};
const options = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(slackMessage)
};
const response = UrlFetchApp.fetch(slackWebhookUrl, options);
if (response.getResponseCode() !== 200) {
Logger.log('Failed to send "No news is good news" notification to Slack.');
} else {
Logger.log('"No news is good news" notification sent successfully to Slack.');
}
}
// Catch Error to Log
function logError(message) {
// Log the error message to the Apps Script logger
Logger.log(message);
// Optionally send the error to Slack
sendErrorNotificationToSlack(message);
}
// Send Error Notification to Slack
function sendErrorNotificationToSlack(errorMessage) {
const slackWebhookUrl = PropertiesService.getScriptProperties().getProperty('SLACK_WEBHOOK_URL');
const adminUserId = PropertiesService.getScriptProperties().getProperty('SLACK_ADMIN_USER_ID'); // Your Slack User ID (e.g., U1234567)
const slackMessage = {
text: `:warning: *Error Notification* :warning:\n<@${adminUserId}> エラー内容を確認してください:pleading_face:\n\`\`\`${errorMessage}\`\`\``
};
const options = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(slackMessage)
};
const response = UrlFetchApp.fetch(slackWebhookUrl, options);
if (response.getResponseCode() !== 200) {
Logger.log('Failed to send error notification to Slack.');
} else {
Logger.log('Error notification sent successfully to Slack.');
}
}
簡単30秒