Slack Newsletters [Tutorial]

Automatically generate a weekly newsletter from most engaging messages posted to a channel in Slack. Other features:

• Manually Flag messages to be added
• Email draft created for weekly review before sending out to members
• Tools used: Google Sheets, Gmail, Slack, Zapier

1. Slack

• Create a custom "flagged for newsletter" emoji (instructions)
• This will be used to reactji on messages you'd like to manually flag for the newsletter

1. Google Sheets

• Make a copy of this Google Sheets Template
• Don't edit anything in the sheet. The only thing you can touch is the Settings tab to determine how many top posts to include and the time frame from looking for posts.
• This will be used as your database of Messages

1. Zapier

• Send all messages from a Slack channel into Google Sheets. Use this Zap template.
• Slack - Choose Channel
• Filter - already configured to stop the Zap if message is a reply (taking out threaded messages)
• Formatter (Date/time) - choose "Format" as the transform type. Then, adjust timezone.
• Google Sheets - Create Spreadsheet Row. Choose your spreadsheet and then choose the "Messages" worksheet
• Turn it on!
• Video screencast showing the steps mentioned above
• Send Flagged for Newsletter messages to Google Sheets. Use this Zap template.
• Slack - Choose Reaction (this is your custom emoji). Choose Channel. Choose either yourself as user or leave open for anyone to flag messages.
• Formatter (Date/time) - choose "format" as the transform type, then adjust timezone.
• Google Sheets - Create Spreadsheet Row. Choose your spreadsheet and then choose the "Flagged for Newsletter" Worksheet. Important: add the number "1" to the Lookup Column field.‍
• Turn it on!‍
• Video screencast showing the steps mentioned above.

• Add Reactji Count to messages. Use this Zap template.
• Slack - Choose channel. Leave Reaction and User blank to trigger on all reactji's from anyone.
• Formatter (Date/time) - choose "format" as the transform type, then adjust timezone.
• Google Sheets - Lookup Spreadsheet Row. Choose Spreadsheet, choose Messages worksheet, choose the Lookup Column "Link." This will find the reactji'd message in the Google Sheet.
• Google Sheets - Update Spreadsheet Row. Choose Spreadsheet and choose Messages worksheet. Everything else is configured to update Reactji count and Engagement Score (engagement score is not shown in screencast, but should be available in the template).
• Turn it on!
• Video screencast showing the steps mentioned above.

• Add Thread Count to messages. Use this Zap template.
• Slack - Choose Channel
• Filter - already configured to stop the Zap if message is a non-reply. (We only want threaded replies to move the Zap forward)
• Formatter (Date/time) - choose "format" as the transform type, then adjust timezone.
• Google Sheets - Lookup Spreadsheet Row. Choose Spreadsheet, choose Messages worksheet, choose the Lookup Column "Date/TS" This will find the parent message in the Google Sheet.
• Google Sheets - Update Spreadsheet Row. Choose Spreadsheet and choose Messages worksheet. Everything else is configured to update Thread count and Engagement Score.
• Turn it on!‍
• Video screencast showing the steps mentioned above.

• Create Email Newsletter Draft (because this Zap involves Paths, I cannot share a template, yet. So, follow along carefully! Join our Slack Channel if you want to ask me questions.)
• Schedule by Zapier - choose the day/time of week to create draft
• Google Sheets - Find many spreadsheet rows (with line item support). Choose Spreadsheet, choose Messages Top worksheet, choose the Lookup Column "Lookup Column."
• Formatter (Utilities) - choose "Line-item to Text" as the transform type, then choose Step 2 > Rows Col$a field to get all messages text. The output of this step will break out each message text into a single item.
• Formatter (Utilities) - choose "Line-item to Text" as the transform type, then choose Step 2 > Rows Col$a field to get all messages text. The output of this step will break out each message text into a single item.
• Formatter (Utilities) - choose "Line-item to Text" as the transform type, then choose Step 2 > Rows Col$b field to get all messages links. The output of this step will break out each link text into a single item.
• Google Sheets - Find many spreadsheet rows (with line item support). Choose Spreadsheet, choose Newsletter Flagged List worksheet, choose the Lookup Column "Lookup Column."
• Paths - if no messages were flagged for the week, then these paths take that into account. If you plan to flag at least one message a week, you can simplify your Zap by not using paths.
• Path A - No Flagged Messages - Looks at the ID for the Google Sheet find flagged messages step. If it does not exist, there were no flagged messages. Then move forward with the Zap and setup an email template.
• Path B - Flagged Messages exist - Looks at the ID for the Google Sheet find flagged messages step. If it exists, there were flagged messages. Then move forward with the Zap and setup an email template.
• Turn it on!‍
• Video screencast showing the steps mentioned above.

This automation is pretty involved! The next step would be to set it up via Bubble to automate the set up so that anyone can easily create these newsletters.

Happy building,

Bryce