Post a Daily Blocked-Tasks Summary from Google Sheets to Slack
How can I automatically post a daily summary of unfinished tasks with blockers from Google Sheets to Slack?
Connect an Apps Script to the spreadsheet, collect unfinished tasks with nonempty blocker cells, and send one message through a Slack incoming webhook. Add a daily trigger. If no tasks qualify, the script sends nothing.
A spreadsheet-bound Apps Script can scan task rows for unfinished work with blockers and send a daily message to Slack. When no rows meet the conditions, it stays quiet instead of posting an empty report.
How should the Tasks tab identify unfinished work with blockers?
A task counts as blocked and unfinished when its Blocker cell has text and its Status cell is anything other than Done, regardless of capitalization or extra spaces around the value.
Add these headers in A1:D1, with one task per row below them:
| Task | Owner | Status | Blocker |
|---|---|---|---|
| Draft onboarding FAQ | Avery Example | In progress | Waiting for policy wording |
| Audit support tags | Riley Placeholder | Done | Waiting for tag export |
| Draft async guide | Avery Example | In progress | |
| Refresh welcome email | Morgan Sample | Not started | Missing approved subject line |
These are example rows, not real tasks. Replace them with your own information before you schedule the report.
| Sheet value | Filter or display behavior |
|---|---|
Done, ignoring case and surrounding spaces |
Excluded |
Blank status, Completed, or Canceled |
Unfinished; included if blocked |
| Blocker containing only spaces | Excluded |
| Blank owner | Displayed as Unassigned |
| Blank task name | Displayed as (Untitled task) |
| Columns after D | Ignored |
A Status dropdown can help keep closed work consistently marked Done. Since the script reads displayed values, blocker text produced by a formula counts as well. If you want a separate view that shows only blocked work, see how to show only blocked tasks in a Google Sheets project tracker. This digest can also sit alongside other remote operations workflows.
How do I connect Google Sheets to Slack and schedule the report?
To connect the sheet and Slack, use a bound Apps Script project, save its settings in Script Properties, and create an incoming webhook for the destination. Slack’s incoming webhook documentation covers how messages go to the destination configured for a webhook.
- Create the webhook. Turn on Incoming Webhooks in your Slack app configuration, add a workspace webhook, choose the destination channel, and authorize it. An administrator may need to approve the setup. Copy the URL, but do not put it in the sheet or code.
- Open Apps Script. In the spreadsheet, select Extensions > Apps Script. The script opens the spreadsheet by ID, so its scheduled run does not depend on a browser tab being open.
- Set the time zone. In Project Settings, choose the reporting time zone, for example
America/New_York. If date-based formulas determine blockers, set the spreadsheet time zone to match. - Add Script Properties. Save
SPREADSHEET_ID, the identifier between/d/and/editin the spreadsheet address, andSLACK_WEBHOOK_URL, the complete webhook URL. Do not include surrounding quotes. The Apps Script Properties Service guide explains script properties. Keep the webhook URL secret. - Save the code. Replace the editor’s starter code with the complete script below. Leave the function names as they are.
- Authorize the handler. Select
postDailyBlockedTasksand click Run. Sign in with an account that has spreadsheet access, then grant spreadsheet and external-request permissions. If there are qualifying rows, this run sends an actual Slack message. - Install the schedule. Run
installDailyTriggeronce, then check thatpostDailyBlockedTasksappears under Triggers. If you change the project time zone, install the trigger again.
Anyone with edit access to the project can view Script Properties. Limit who can edit the project, and replace the webhook if it is exposed. The webhook sets the destination; the script does not choose a channel at run time. To send the report elsewhere, configure a suitable webhook and update the stored URL.
What does the Apps Script post, and how does it select rows?
The script reads the Tasks tab, selects unfinished rows with blockers, and sends a single dated message through the configured Slack webhook. It also checks the column headers, formats the report, verifies Slack’s response, and creates the daily trigger.
function postDailyBlockedTasks() {
const props = PropertiesService.getScriptProperties();
const spreadsheetId = props.getProperty('SPREADSHEET_ID');
if (!spreadsheetId) {
throw new Error('Set SPREADSHEET_ID in Script Properties.');
}
const spreadsheet = SpreadsheetApp.openById(spreadsheetId.trim());
const sheet = spreadsheet.getSheetByName('Tasks');
if (!sheet) {
throw new Error('Create a sheet tab named Tasks.');
}
const expectedHeaders = ['Task', 'Owner', 'Status', 'Blocker'];
const headers = sheet.getRange(1, 1, 1, 4)
.getDisplayValues()[0]
.map(value => value.trim());
if (headers.some((header, index) => header !== expectedHeaders[index])) {
throw new Error('Set A1:D1 to Task, Owner, Status, Blocker.');
}
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
console.log('No task rows; no Slack message sent.');
return;
}
const blocked = sheet.getRange(2, 1, lastRow - 1, 4)
.getDisplayValues()
.map(row => row.map(value => value.trim()))
.filter(row => row[3] !== '' && row[2].toLowerCase() !== 'done');
if (blocked.length === 0) {
console.log('No blocked unfinished tasks; no Slack message sent.');
return;
}
const webhookUrl = props.getProperty('SLACK_WEBHOOK_URL');
if (!webhookUrl || !webhookUrl.trim()) {
throw new Error('Set SLACK_WEBHOOK_URL in Script Properties.');
}
const date = Utilities.formatDate(
new Date(),
Session.getScriptTimeZone(),
'yyyy-MM-dd'
);
const lines = blocked.map(row =>
'- ' + escapeSlackText(row[0] || '(Untitled task)') +
' | Owner: ' + escapeSlackText(row[1] || 'Unassigned') +
' | Status: ' + escapeSlackText(row[2] || 'No status') +
' | Blocker: ' + escapeSlackText(row[3])
);
const text = 'Blocked tasks - ' + date +
' (' + blocked.length + ')\n' + lines.join('\n');
if (text.length > 35000) {
throw new Error('Digest exceeds 35000 characters; reduce its scope.');
}
const response = UrlFetchApp.fetch(webhookUrl.trim(), {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({
text: text,
unfurl_links: false,
unfurl_media: false
}),
muteHttpExceptions: true,
followRedirects: false
});
if (response.getResponseCode() !== 200 ||
response.getContentText().trim() !== 'ok') {
throw new Error(
'Slack rejected the digest. HTTP ' + response.getResponseCode() +
'. Check the webhook and destination.'
);
}
console.log('Posted ' + blocked.length + ' blocked tasks.');
}
function escapeSlackText(value) {
return String(value)
.replace(/\s+/g, ' ')
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>');
}
function installDailyTrigger() {
const handler = 'postDailyBlockedTasks';
ScriptApp.getProjectTriggers()
.filter(trigger => trigger.getHandlerFunction() === handler)
.forEach(trigger => ScriptApp.deleteTrigger(trigger));
ScriptApp.newTrigger(handler)
.timeBased()
.atHour(9)
.nearMinute(0)
.everyDays(1)
.inTimezone(Session.getScriptTimeZone())
.create();
}
Cell line breaks are changed to spaces. The script escapes angle brackets so copied Slack mention tokens cannot trigger mentions, and it turns off link previews. If a report tops 35,000 characters, the script throws an error rather than silently omitting tasks. All matching tasks go into one message, not separate messages for each row.
For the example rows, a run dated April 8, 2026, in the Apps Script project time zone sends this:
Blocked tasks - 2026-04-08 (2)
- Draft onboarding FAQ | Owner: Avery Example | Status: In progress | Blocker: Waiting for policy wording
- Refresh welcome email | Owner: Morgan Sample | Status: Not started | Blocker: Missing approved subject line
Audit support tags is excluded because its status is Done. Draft async guide has no blocker, so it is excluded too. Two of the four example rows pass the filter, which is why the message header shows 2. If both remaining blockers are cleared, the next run sends nothing. The script neither posts an all-clear message nor edits the spreadsheet. Any task that remains blocked will show up in each daily snapshot until its sheet values change.
Before applying the filter, the script trims cell values and checks status without regard to capitalization. Blank owner cells display as Unassigned; blank task names display as (Untitled task). Those labels appear only in the message and do not alter sheet values. The script also checks all four headers before reading task rows, catching renamed or shifted columns before a report is sent.
When does the daily trigger run, and what can cause duplicate reports?
The installer aims to run the report at about 9:00 a.m. in the project time zone, but Slack may receive the message at a different minute. The ClockTriggerBuilder reference describes the time-based trigger methods used in the installer.
nearMinute(0) allows a window of about 15 minutes on either side of the requested minute. The trigger also runs on weekends. To report only on weekdays, add a weekday check inside postDailyBlockedTasks and use the project’s reporting time zone.
When the trigger is reinstalled, the script replaces matching triggers belonging to the account that runs the installation. It cannot remove triggers created by another editor’s account. Have one person install it, and check the Triggers page if duplicate reports show up. A manual run also sends any qualifying tasks. Since the script does not save which date it has reported, running it manually near the scheduled time can produce a second digest. Each message uses the current sheet values, so unresolved blockers appear again.
If you need a reminder rather than a full blocker digest, compare this method with sending one daily task reminder from Google Sheets without repeat emails. A reminder and a digest serve different notification needs. Use the digest when people need the blocked-task details in Slack.
How do I fix missing Slack posts, rejected requests, or duplicate digests?
Check the execution message first, then inspect the Tasks tab, webhook settings, and trigger owner. The script stops if the tab is missing or its headers do not match. It skips posting when no rows qualify and reports an error if Slack returns an unexpected response.
Tab or header errors
Symptom: Create a sheet tab named Tasks or Set A1:D1 to Task, Owner, Status, Blocker.
Exact fix: Rename the tab to Tasks, then set A1 through D1 to Task, Owner, Status, and Blocker. The script trims spaces around headers but requires the capitalization to match. Run postDailyBlockedTasks again.
A blocked task produces no message
Symptom: A successful execution logs No blocked unfinished tasks.
Exact fix: Enter blocker text in column D, not in a comment or note. Check that column C is not Done, and verify that SPREADSHEET_ID refers to the intended spreadsheet.
Slack rejects the digest
Symptom: Slack rejected the digest with an HTTP status.
Exact fix: Check that the Slack app and destination are available. If someone revoked or disabled the webhook, create a replacement and update SLACK_WEBHOOK_URL in Script Properties. Leave the posting code as it is, and never put the secret in a log.
Two daily reports arrive
Symptom: Similar messages arrive near the scheduled time.
Exact fix: Check the Triggers page for every account that installed the script. Remove extra postDailyBlockedTasks triggers and leave one designated installer. Reinstalling the trigger does not remove triggers owned by another account.
When should I avoid this Google Sheets-to-Slack webhook digest?
Use a different approach for urgent alerts, delivery at an exact minute, interactive Slack updates, or a report too long for one message.
- Immediate or precisely scheduled alerts: Use an event-driven integration or a scheduler with explicit timing and retry controls.
- Reports exceeding 35,000 characters: Send a short aggregate notification and store the detailed report somewhere with access controls.
- Acknowledgment buttons or status edits: Use a Slack app with interactive handlers and authenticated write-back logic.
- Broader Slack membership than sheet access: Keep task details out of that channel. Choose a destination with suitable access restrictions.