Automated weekly project status report from Smartsheet

PromptCreate

Every Monday at 8am, roll up my whole Smartsheet project portfolio into a written status brief so I stop rebuilding it by hand. Use a cron trigger set to Mondays at 8am in my timezone.

Start by finding the projects. Use Smartsheet List Sheets to find the project sheets in my portfolio workspace. If I already keep a portfolio roll-up report, use Get Report on it instead and treat that as the list of projects in scope. Then run Get Sheet on each project sheet to read the task rows, including task name, percent complete, status, assigned owner, and start and due dates.

Then work out what changed. For each project, use Get Cell History on the status column to see which projects changed colour since last week. Pay particular attention to any status that flipped from Green to Yellow, or from Green or Yellow to Red, within the last seven days.

Judge which projects are genuinely at risk rather than just dumping counts. The signals that matter are: an end date that has slipped later than it was last week, overdue tasks that sit on the critical path, a status that flipped down a colour, and progress that is materially behind where the remaining timeline implies it should be. A single late low-priority task is not a project at risk, so leave it out. The value of this brief is the risk judgement, not the row count.

Format the brief with a combined "Needs attention" list at the very top, naming each at-risk project with a one-line reason. Below that, write one short paragraph per project covering where it stands, what changed this week, what is blocking it, and who owns the next step. Skip anything already marked complete. Keep the whole thing readable by an executive in about two minutes, with no raw tables and no restating of every task.

Deliver it in two places. Post the finished brief to my Slack channel using the Slack Send a Message action, and email the same brief to the leadership list using the Gmail Send a Message action, with a subject line along the lines of "Portfolio status for week of" plus the date. Both copies should carry identical content so nobody has to reconcile two versions.

If a sheet cannot be read, or has no recognisable status or due date column, note it in a short line at the bottom of the brief rather than failing the whole run.

What does this prompt do?

Reads every project sheet in your Smartsheet portfolio each Monday morning, pulling task progress, owners, due dates and current status.
Works out which projects are genuinely in trouble, such as slipping end dates, overdue critical tasks, or a status that just turned yellow or red.
Writes a short paragraph per project under a combined needs attention list, and leaves out anything already marked complete.
Posts the same brief to your Slack channel and emails it to the leadership list, so there is only one version of the truth.

What do I need to use this?

A Smartsheet account with access to the project sheets in your portfolio workspace
A Slack workspace and the channel where the brief should land
A Gmail account to send from, plus the leadership email list
Optional: a portfolio roll-up report in Smartsheet, if you already keep one

How can I customize it?

Change the timing. Monday at 8am is a starting point, though some teams prefer a Friday afternoon wrap-up.
Adjust what counts as at risk, such as how many days of slippage matter or which status colours trigger a callout.
Swap the recipients, whether that means a different Slack channel, a wider email list, or Slack only.

FAQs

Do I need a portfolio report set up in Smartsheet first?

No. If you already keep a roll-up report it will use that as the starting point, but otherwise it finds the project sheets in your workspace on its own.

Will it include projects that are already finished?

No. Anything marked complete is skipped, so the brief stays focused on live work and stays short enough to actually read.

How does it decide that a project is at risk?

It looks at whether end dates have moved out, whether important tasks are past their due date, and whether the status colour changed since last week. It makes a judgement call on each project rather than just counting rows.

Can it post to Slack without also sending the email?

Yes. You can drop either destination when you set it up, or add more channels and recipients later.

What if one of my project sheets is set up differently from the others?

It reads each sheet on its own terms. If a sheet cannot be interpreted, it gets flagged in a short note at the bottom of the brief instead of stopping the whole report.

Related templates

Auto-fix your calendar when a flight slips, and flag what's at risk

When your flight moves, your calendar times get corrected automatically and you get a Slack note naming the meetings you're about to miss.

Agentic Task

Trace phishing emails to the sending IP and report abuse

Every 15 minutes, forwarded phishing reports get traced back to the server that really sent them, with a verdict in Slack and the worst senders reported.

Agentic Task

Turn procurement portal tenders into CRM deals each morning

Every weekday at 7am, sign in to the tender portals you track, filter new notices against your bid criteria, and open a deal for the ones worth chasing.