DEV Community

loading...
Cover image for Send progress reports as bulk Slack DM’s using Google Sheets + Script

Send progress reports as bulk Slack DM’s using Google Sheets + Script

Shalini Kumari
Software engineer with passion for solving problems. Upholder of "Well done is better than well said"
・4 min read

First things first, why I built this
I was chosen as the Google CloudReady Facilitator for my campus and it was one of the best things that happened in my developer journey. The Google CloudReady Facilitator program added unexpected value to my overall growth. I interacted with hundreds of students, shared my knowledge, and became much better at management skills.

The journey started with setting up the community. I created a slack workspace and invited all the students to it. After the enrollment period ended, next thing was to interact with students, help them with their quests completions and send them their daily progress reports.

Coming to the daily progress reports, initially, I thought about sending emails, as it was pretty easy to automate the emails using zapier. The only thing was that it had a limit of free tasks. After the free tier ended, I realized that we needed some other solution.

One of the institutions developed a web app where students could enter their email id or qwiklabs profile URL to get their progress reports. It was an amazing app but I wanted to have a different solution. My concern was that procrastination is a real thing and we all do it. We sometimes need a constant reminder or motivation to do things. Therefore, I thought of using the slack API to send personalized daily progress reports to students in their DMs to constantly motivate them to complete their learning.

My plan was to send bulk DMs to all the students and I also needed to take the quests and skill badge completions as input from the google sheets I had. I used the Google Apps Script to write the script that would call the slack API and send the data from the google sheets to slack.

On the basis of the email ID of the students, I fetched their slack user id using the users.lookupByEmail method. Then I used the user id to send the DM. There was one more issue that some students were not on slack and it was necessary to filter them out. I colored the row with the email id that was not present on slack and then filtered the rows on the basis of color. It made my life a lot easier and students also got their progress reports directly on their slack.

The detailed process of creating the slack app is documented below
Go to https://api.slack.com/apps and hit Create New App on the top right. Choose the Create an app from scratch option. Give it a name and pick a workspace where you can create a channel to test your bot. You will need a channel so create a private channel #test.

Click on Incoming Webhooks under the Features section. Turn on incoming webhooks during setup using the On/Off toggle. Tap on “Add New Webhook to Workspace”. Choose a channel, the #test channel that we created, where the App can post. You can use this channel for testing. Click Allow.
Alt Text
Tap on “OAuth & Permissions” and then on “Add an OAuth Scope” under “User Token Scopes”. Select “chat:write”.
You might see a notification to reinstall your app. Click on it and then choose a channel. Click Allow.
Alt Text

You would see an OAuth Access token. Copy it as we will need it later. Make sure to copy the one that starts with "xoxp".

Now, we will head to google sheets. Create a new google sheet. Rename Sheet1 to Input.

Here is what my sheet looked like:
Alt Text
From the menu select Tools > Script editor. This will create a Google script. Paste the code from here.

You will need to paste the slack API token that you copied earlier in place of 'YOUR_USER_OAUTH_TOKEN'.
You can specify the test that you want to send in place of 'SLACK_MESSAGE_GOES_HERE'.

I wanted to send two progress metrics so I put them in COL_A and COL_B. You might need to change the indexes of the data.

Click on “Run” to Authorize it. You will need to click through a series of screens to Authorize your app. You may even be told it is potentially unsafe, but you may proceed because it’s your app.

Back on the Google Sheet, you will now see Message Menu in the toolbar. Click on it and then hit the Send to Recipients button. You may need to authorize the sheet for the first time.

Initially, you can test it with your slack email. You will receive a DM from yourself and then you can go ahead with sending Bulk DMs.

This Slack App helped me avoid sending emails and have more interaction with students. It was a great addition to my learning too. Automation always helps!!!

Discussion (0)