Save time with this custom Google Sheets, Slack & Email test scoring bot

You’ve marked your students test scores and recorded it all in a beautiful Google Sheet.

You’ve dotted your i’s and crossed your t’s, checked your spelling and made sure all your scores are ready to go.

Now comes the tedious part, copying and pasting each student’s scores and feedback into an email or Slack message to send back to them.

Wait, there’s a better way!

With a little bit of upfront effort using Google Apps Script, you can build yourself a custom Google Sheets Slack/Email app that can automate that whole final step for you.

Besides being useful, it’s also pretty fun to post stuff from your Google Sheet direct to Slack!

Send data from Google Sheets to Slack

I’ve taught two iterations of General Assembly’s DC Data Analytics course, a 10 week part-time course covering data analysis and visualization using Excel, SQL and Tableau. A big component of the course are the three projects which the students complete, which I and my fellow instructors grade. I created a Google Sheet for each class to facilitate the marking process. The final step was to take the scores and feedback in the Google Sheet and copy-paste it into each student’s Slack channel. This was a laborious and error-prone process. Hence, I decided to build this tool to automate the whole feedback process.

In this post, I’ll show you how to build a scoring template in Google Sheets, which sends scores/feedback directly to students via email or Slack, without ever having to leave the Google Sheet. It’s super efficient and quick way to send grade and give feedback.

Now the steps are as follows:

  1. Grade all of the student submissions in the Google Sheet per the grading rubric.
  2. Select the students I want to send feedback to.
  3. Select the channel by which I want to send feedback, in this case by email or Slack message.
  4. Click Submit and all the selected students will receive their feedback immediately, with no manual copy-pasting or emailing required.

Here’s a GIF showing submission of project scores and feedback to an individual student in Slack:

Send data from Google Sheets to Slack

And here’s a GIF showing sending the feedback in an email:

Send from Google Sheets to email

There are a few steps involved in building this system, and I’ll run through each step here. Firstly let’s create the Google Sheet to record all our scores and feedback:

Setting up a student scorecard in Google Sheets

I setup my Google Sheet with two tabs, although you could simply stick with a single grading sheet. I wanted to have a “working” sheet for data entry (scoring) that fed automatically into a front summary sheet, which only displayed total scores and had the Slack/email control options.

I recorded the following data-points in the marking sheet:

  • Student name, email address and Slack name
  • Name of the marker
  • Date when the project was received
  • Individual section markings and a total mark for each section of the project
  • Positive feedback & Areas for Improvement comments
  • Any other comments (for open ended comments)

There’s nothing fancy going on here, and I added color coding to distinguish the sections. It’s a simple spreadsheet for data entry, although I could have potentially linked this to a Google Form and conducted the marking that way.

My final sheet looks like this:

Marking template screenshot

You can see that I’ve recorded various individual scores for each section, and then combined them into a total score for each section, which carries through to the Summary sheet.

The summary sheet has the student names, total scores and feedback, and some controls to send the feedback and choose what channel to use.

The summary sheet looks like this:

Marking template summary sheet

I use an index/match formula to pull in all the other data:


After the data columns, I added three new columns in M, N and O, as follows:

Submit (in column M): Here I used data validation to create a Yes/No drop down menu, to indicate whether you want to send feedback to this student at this specific time.

Submit data validation

Channel (in column N): Again, using data validation I create a drop down menu to choose which channel you want to send a message through: Slack, Email or Both.

Data validation drop down menu

Date sent (in column O): this column is blank, and gets filled in automatically by the program when a communication is sent.

Next I added conditional formatting, to highlight every row that is selected for submitting (i.e. has a “Yes” in column M), to make it clear which students will and won’t receive feedback.

conditional formatting

To get the conditional formatting applied to the whole row, first highlight the whole range of student information (from the first student name to the last row/last column), choose Format > Conditional formatting... menu option, choose the custom formula option and enter the following formula:


as shown in the following diagram:

Conditional formatting menu

The last thing to do in the Google Sheet is to add that Submit button. This is a drawing that we’ll eventually assign our program to, so that when it’s clicked the required Slack messages and/or emails are sent to the selected students.

To insert an image, go to menu option Insert > Drawing... which pops up a blank canvas. I used the rounded rectangle:

Google Sheets Drawing menu

Here you can draw your button and add text:

Google Sheets drawing button

Click on Save & Close when you’re done and the button will be inserted into your Google Sheet. You can resize and move the button to fit your needs.

We’ll revisit later, once we’ve written some code, and connect the button to program so something happens when it’s clicked (at the moment it does nothing).

That’s it with the Google Sheet for now. You can make a copy of the Google sheet here and use that as your template, then add the code shown below.

Adding the Apps Script code

If you’re new to Apps Script, you may want to check out my previous post: Beginner guide to Apps Script, which will show you the ropes.

First step is to open up your Apps Script code editor from your Google Sheet: Tools > Script editor...

script editor menu

There are three parts to our program: i) a function that gets the data from our spreadsheet and checks which students and channels are selected, and then calls part ii) a function which sends out emails, and iii) a function that sends messages to Slack.

In the code editor window, clear out all the existing default code so you have a blank window, then paste in the 3 parts of code below.

[Note: a full copy of the code is available from here on Github, which you can paste directly into the script editor window. You’ll still need to get your Slack webhook url to insert into the code, and the full details are below.]

Part 1: function to get student data and determine what action to take

This is the main function, the engine, which selects the students’ data, sees which students have been selected, what channel to use, and then requests the program send emails or Slack messages as required.

What’s it doing?

  1. It grabs all of the relevant student data from the Summary sheet and puts it into a variable called “range” (lines 3 to 7).
  2. It loops through this entire range, student by student… (lines between 13 and 37).
  3. …at each iteration of the loop, it checks if that row of data (an array, corresponding to one student) is marked with a “Yes” in the Submit column (line 14).
  4. If it is “Yes”, then we proceed on to the switch statement, which looks at whether I want to send feedback to the student via a slack message, an email or both (lines 17 to 32). Based on my choice in the Google Sheet, it either calls the sendToSlack function (which will send a Slack message) or the sendEmail function (which sends an email) or calls both (which sends a Slack message and an email).
  5. Finally, a date is added to the Google Sheet in the final column of the Summary sheet, against any rows for which we have sent Slack messages and/or emails.
  6. If the loop finds a “No” at the If check (item 3 above), then it simply moves on to the next student, and no communications are sent.

Part 2: Apps Script code to send feedback to Slack

Firstly, a big thanks to Ivan Kutil’s excellent post on how to integrate Apps Script with Slack, which helped me enormously.

There are a few steps here to set this up. First we need to grab a unique webhook for our Slack team. This is a url (an endpoint) we can send data to (in JSON format) that will get posted into our Slack channel.

So, go to this url:

You’ll be prompted to login in with your Slack team name:

Enter slack team
Enter your Slack team name

And next, your login details:

Slack credentials
Next login in to Slack

Then you’ll be presented with this screen, where you choose the channel (but don’t worry, it’s the same webhook for your team, no matter which channel you choose. Besides, our program will choose each channel on the fly based on the student selected). So for now, choose any channel, e.g. #general:

Slack webhook page
Choose your channel

When the big button turns green, click it:

Slack webhook
When the green button is activated, click it to get your Slack webhook url

This takes you to a new screen with your webhook showing, highlighted in red in this image:

Slack webhook url
Slack webhook url

Make a copy of your webhook and add it to the code below (where the XXXXXXX’s are in line 8) and then paste the whole block of code to your code file in the script editor window.

What’s going on?

The Slack channel we will post to (i.e. direct message channel with a specific student) is created automatically by pulling that student’s slack handle from our data array, combining with an “@” and setting this as the “channel” parameter, all in line 11:

The name of the message sender (i.e. the name that will show on the Slack message) is in line 12:

The body of the message comes in lines 13 to 24, under the “text” key:

Finally, I specified an icon to show next to the Slack message, in this case an inbox emoji, on line 25.

This diagram shows the code in Apps Script next to the final output in Slack:

Apps Script Code to Slack

Note, to get new lines in Slack, we use the \n command.

Next up I specify the options for my request, on lines 28 to 32 – it’s a POST request, sending a packet of JSON to the Slack webhook.

Finally the last line actually sends the message to Slack (line 34). It makes a request to fetch the url (our Slack webhook) with the options we specified (POST this JSON data to a specific Slack channel) using UrlFetchApp service. An HTTP response is returned, which is an “Ok” in our case.

Part 3: Apps Script code to send feedback via email

This function has some similarities with the Slack function. It creates the body of an email on the fly, based on the information it is given (a particular student’s data).

I use the MailApp email service (line 4) within Apps Script that allows users to send emails, with complete control over the email contents.

Then I build up the email piece by piece, under the htmlBody key. This is now HTML code so I use <br> to insert line breaks.

The entire code can be seen here on Github.

Connect your Apps Script to your spreadsheet

Now, we can finally connect our program to that beautiful Submit button we created a while back. Click on the button in your spreadsheet so Google adds the editing handles around the edges and a small grey triangle on the right side of the button. Click that and the following menu will open:

assign script menu

Choose Assign script... and type in the name of your script:


That’s it, there’s no need to add any parentheses.

Click the button….and the first time you’ll be prompted to authorize your app:

Authorize apps script app

Click Continue to get to the next prompt screen, which summarizes the actions your script wants permission to do:

Apps script authorization

Click Allow to run your script.

Voila! You should now be able to click on the submit button and send emails or slack messages from your Google Sheets direct to anyone marked with a Yes, something like this:

Send data from Google Sheets to Slack

I welcome any comments or questions below.

What other additions, modifications or improvements would you like to see?

One thought on “Save time with this custom Google Sheets, Slack & Email test scoring bot”

  1. hi,
    this is extreme good example to connect sheet to slack.
    Could you help with code to run / command from slack to get data from excel?


Leave a Reply

Your email address will not be published. Required fields are marked *