Post a Daily Blocked-Tasks Summary from Google Sheets to Slack

How should the Tasks tab identify unfinished work with blockers?

The Google Sheets blocker digest includes rows on the Tasks tab whose Blocker cell contains text and whose Status is not Done.

Put these headers in A1:D1, followed by one task per row:

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

Replace these fictional examples before scheduling.

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 helps keep closed work labeled Done. Formula-generated blocker text counts because the script reads displayed values.

How do I connect Google Sheets to Slack and schedule the report?

Connect Google Sheets to Slack through a spreadsheet-bound Apps Script project, Script Properties, and an incoming webhook configured for the intended Slack destination.

1. Create the webhook. Enable Incoming Webhooks in your Slack app configuration, add a workspace webhook, select the destination channel, and authorize it. Administrator approval may be required. Copy the URL without placing it in cells or code.
2. Open Apps Script. In the spreadsheet, choose Extensions > Apps Script. The code uses an explicit spreadsheet ID, so scheduled executions need no open browser tab.
3. Set the time zone. In Project Settings, choose the reporting time zone, such as America/New_York. Align the spreadsheet time zone if date-based formulas determine blockers.
4. Add Script Properties. Save SPREADSHEET_ID, using the identifier between /d/ and /edit in the spreadsheet address, and SLACK_WEBHOOK_URL, using the complete webhook URL. Omit surrounding quotes.
5. Save the code. Replace the editor’s starter code with the complete script below. Keep function names unchanged.
6. Authorize the handler. Select postDailyBlockedTasks and click Run. Grant spreadsheet and external-request permissions with an account that can access the spreadsheet. Eligible rows cause a real Slack message.
7. Install the schedule. Run installDailyTrigger once and confirm postDailyBlockedTasks appears under Triggers. Reinstall after changing the project time zone.

Treat the webhook as a password: project editors can access Script Properties. Restrict editing access and replace exposed webhooks.

What Apps Script posts the daily blocked-task digest?

The following Apps Script validates the Tasks tab, filters unfinished blocked rows, posts one Slack webhook message, and provides a daily-trigger installer.

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, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;');
}

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 become spaces. Escaping angle brackets prevents copied Slack mention tokens from becoming mentions, and link previews are disabled. Reports exceeding 35,000 characters fail rather than silently omit tasks.

What Slack message should the sample task rows produce?

The sample Google Sheets rows produce a two-task Slack digest for an execution dated April 8, 2026, in the Apps Script project time zone.

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. Clearing both remaining blockers makes the next execution send nothing. The script neither posts an all-clear message nor modifies the spreadsheet.

Why does postDailyBlockedTasks send only unfinished blocked tasks to Slack?

postDailyBlockedTasks sends only unfinished blocked tasks because its filter() requires nonempty blocker text and a normalized status other than done.

First, PropertiesService.getScriptProperties() supplies the spreadsheet ID, and SpreadsheetApp.openById() selects the source explicitly rather than relying on an active browser session. The script locates Tasks and checks its four headers before interpreting column positions.

Next, getDisplayValues() reads task rows as displayed strings, including formula results, rather than underlying typed values. Nested map() calls trim cells, then filter() applies both conditions together. toLowerCase() makes the status comparison case-insensitive. The sample therefore retains the FAQ and welcome-email tasks.

For retained rows, the script retrieves the webhook and uses Utilities.formatDate() with the project time zone. Another map() builds readable lines with fallback labels. escapeSlackText() flattens whitespace and encodes special Slack characters rather than inserting raw cell text.

The script joins those lines into one dated, counted message and checks its length. JSON.stringify() serializes the payload rather than manually assembling JSON; UrlFetchApp.fetch() posts it with previews disabled. Both response status and body are checked before success is logged.

Separately, installDailyTrigger() replaces the current account’s matching triggers before creating the daily schedule, rather than simply adding another.

When does the daily trigger run, and what can cause duplicate reports?

The Apps Script installer requests a daily run around 9:00 a.m. in the project time zone, not an exact-minute delivery.

nearMinute(0) allows approximately 15 minutes on either side of the requested minute. Weekends are included; weekday-only delivery requires an additional weekday check.

Reinstalling replaces matching triggers owned by the current account, not another editor’s triggers. Designate one installer. Manual executions also post eligible tasks, and no once-per-date ledger prevents a second digest. Each report is a current snapshot, so unresolved blockers recur.

How do I fix missing Slack posts, rejected requests, or duplicate digests?

Fix Google Sheets-to-Slack digest failures by checking the execution message, source tab, webhook configuration, and trigger ownership.

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 Tasks; set A1 through D1 to Task, Owner, Status, and Blocker. Headers are case-sensitive after trimming. Run postDailyBlockedTasks again.

A blocked task produces no message

Symptom: A successful execution logs No blocked unfinished tasks.

Exact fix: Put blocker text in column D, not a comment or note. Confirm column C is not Done, and verify SPREADSHEET_ID points to the intended spreadsheet.

Slack rejects the digest

Symptom: Slack rejected the digest with an HTTP status.

Exact fix: Confirm the Slack app and destination remain available. Replace a revoked or disabled webhook and overwrite SLACK_WEBHOOK_URL in Script Properties. Do not change posting code or log the secret.

Two daily reports arrive

Symptom: Similar messages arrive around the scheduled time.

Exact fix: Check each installing account’s Triggers page. Delete extra postDailyBlockedTasks triggers, retaining one designated installer; reinstalling cannot remove another account’s triggers.

When should I avoid this Google Sheets-to-Slack webhook digest?

Avoid this webhook digest for urgent alerts, exact-minute delivery, oversized reports, or workflows requiring interactive Slack updates.

• Immediate or precisely scheduled alerts: Choose an event-driven integration or scheduler with explicit timing and retry controls.
• Reports exceeding 35,000 characters: Use a short aggregate notification and an access-controlled detailed report instead.
• Acknowledgment buttons or status edits: Use a Slack app with interactive handlers and authenticated write-back logic.
• Broader Slack membership than sheet access: Do not post task details to that channel; choose an appropriately restricted destination.