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!

Google Sheets Send 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 is the three projects that 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 a super-efficient and quick way to send grades 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:

Google Sheets Send To Slack

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

Google Sheets send 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:

=index(Marking!$B$4:$U$23,match($A4,Marking!$A$4:$A$23,0),match(B$3,Marking!$B$3:$U$3,0))

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:

=$M4="Yes"

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.

The first step is to open up your Apps Script code editor from your Google Sheet menu:

Extensions > Apps Script

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 that 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.

 
function sendStudentScores() {
  // select the range from the Summary sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Summary");
  var lastRow = sheet.getLastRow();
  
  var range = sheet.getRange(4,1,lastRow-3,15).getValues();
  
  // create timestamp to mark when communication was sent
  var timestamp = new Date();
  
  // loop over range and send communication if "Yes" option chosen
  for (var i = 0; i < range.length; i++) {
    if (range[i][12] == "Yes") {
      
      // choose email, slack or both channels
      switch (range[i][13]) {
        case "Email":
          // send email to student by calling sendEmail function
          sendEmail(range[i]);
          break;
        
        case "Slack":
          // post message to slack
          sendToSlack(range[i]);
          break;
          
        case "Both":
          // send email and post to Slack
          sendEmail(range[i]);
          sendToSlack(range[i]);
          break;
      }
      
      // add timestamp to final column to show when communication was sent
      sheet.getRange(i+4,15,1,1).setValue(timestamp);
    };
  }
}

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: https://my.slack.com/services/new/incoming-webhook/

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.

// function to send message to Slack
function sendToSlack(student) {
  var timestamp = new Date();
  
  // custom slack webhook
  // change the XXXXX's to your own slack webhook. Get it from: 
  // https://my.slack.com/services/new/incoming-webhook/
  var url = "https://hooks.slack.com/services/XXXXXXXXX/XXXXXXXXX/XXXXXXXXXXXXXXXXXXXXXXXX";
  
  var payload = {
    "channel": "@"+student[2],
    "username": "ben",
    "text": "Hi " + student[0] +
      "\n Here are your project scores and feedack. Let us know if you have any questions! \n" +
      "\n Section 1 Score: " + student[5] +
      "\n Section 2 Score: " + student[6] +
      "\n Section 3 Score: " + student[7] +
      "\n Overall Score: " + student[8] +
      "\n *Positive notes:* " + student[9] +
      "\n *Areas for improvement:* " + student[10] +
      "\n *Any other comments:* " + student[11] +
      "\n \n Marked by: " + student[3] +
      "\n Date: " + timestamp +
      "\n \n Sent care of Marking Mail Merge tool built by <https://www.benlcollins.com/|benlcollins>",
    "icon_emoji": ":inbox_tray:"
  };

  var options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };
  
  return UrlFetchApp.fetch(url,options);
}

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:

    "channel": "@"+student[2],</pre> 

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

<pre class="theme:github toolbar:2 striped:false start-line:12 lang:js decode:true " >    "username": "ben",</pre>

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

<pre class="theme:github toolbar:2 striped:false start-line:13 lang:js decode:true " >
    "text": "Hi " + student[0] +
      "\n Here are your project scores and feedack. Let us know if you have any questions! \n" +
      "\n Section 1 Score: " + student[5] +
      // etc...

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

// function to create and send emails
function sendEmail(student) {
  var timestamp = new Date();
  MailApp.sendEmail({
     to: student[1],
     subject: "Feedback for Project",
     htmlBody: 
      "Hi " + student[0] +",<br><br>" +
      "Here are your project scores and feedack. Let us know if you have any questions!<br><br>" +
      "<table  border='1'><tr><td>Section 1 Score</td>" +
      "<td>Section 2 Score</td>" +
      "<td>Section 3 Score</td>" +
      "<td>Overall Score</td></tr>" +
      "<tr><td>" + student[5] + "</td>" +
      "<td>" + student[6] +"</td>" +
      "<td>" + student[7] + "</td>" +
      "<td>" + student[8] + "</td></tr></table>" +
      "<br><b>Positive Notes:</b><br>" +
      student[9] + "<br>" +
      "<br><b>Areas for improvement:</b><br>" +
      student[10] + "<br>" +
      "<br><b>Any other comments:</b><br>" +
      student[11] +
      "<br><br>Marked by: " + student[3] +
      "<br>Date: " + timestamp +
      "<br><br>Sent care of Marking Mail Merge tool built by <a href='https://www.benlcollins.com/'>Ben Collins</a>"
   });
}

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:

sendStudentScores

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.

I welcome any comments or questions below.

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

47 thoughts 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?

    thanks
    harin

  2. Hi Ben!

    Thanks very much for sharing this with everyone, it’s indeed very useful. However, i’m facing a small problem in my implementation.
    I would like the bot to share the results in a pubic channel, but i can’t find a way to use the @ sign to tag someone in the channel. If I write and @ in the script or add and @ to the name of the users, slack will not tag the user and the user won’t see a notification in the channel with (1). If you could help with this issue it will be amazing!
    PS: your ebook is very useful as well!

    1. Hi Eloi,

      Unfortunately there’s no quick fix for this as far as I know. From some research, it’ll involve calling the API to get the user’s ID number, as per the last answer of this Stack Overflow question: http://stackoverflow.com/questions/32419756/how-do-you-tag-people-with-a-slack-bot

      As a workaround, you could just send them a copy of the message to their own channel too.

      I may well revisit this Slack/Sheets integration bot in 2017, so keep an eye out.

      Cheers,
      Ben

      1. Dear Ben,

        Thanks very much for your reply! However, do you know which would be the configuration to send the information to each personal channel of every different user?

        Thanks again for your work and support 🙂

        1. Hi, you’ll want to have all your usernames in your Google Sheet, pass them to Apps Script and then loop through them and set the channel to match this username (making sure it has the “@” symbol at the start), e.g. something like this:

          "channel": "@"+student[i]

          where “student[i]” is the variable holding the username. It’s the same script as the example in the article too 🙂

          Hope that helps!

          Ben

  3. hi can you help me please with my integration? my idea is to post anything that is updated in google sheet and send notification to a specific channel. also a warning notification that whenever a value in a cell reach below 5 help pls

    1. This is old enough where you many not respond, but I am confused as to where the email is pulled from. Where would this get the email to send to?

      1. Hi Erika,

        It gets pulled from column B of the marking template, so you can add a unique email address for each row of data.

        Thanks,
        Ben

  4. Great article! Any tips / online resources on using slackbot or slash cmd to post info onto Slack from Google Sheets?

    Slack Example:
    me: what is today’s menu OR /menu today
    slackbot / APP:

    1. Hey Mark,

      Never tried, and haven’t found anything apps script specific on the slack bot, but don’t see why it shouldn’t be possible. Here’s the api guidelines for slackbot:

      https://api.slack.com/bot-users

      and for slash commands:

      https://api.slack.com/slash-commands

      Also, here’s another apps script/slack integration resource:
      https://www.bettercloud.com/monitor/the-academy/use-apps-script-push-live-news-headlines-slack-google-sheets/

      Cheers,
      Ben

  5. Hi from Colombia. I’m new in Apps Script and with the resources that i found in your page i can say that i am a lucky man!

    I need your help. I’m trying to use this template to send emails from google sheets, but i need to add a file that be into folder in Drive. I have each file’s names. One different for each line.

    For example in this template: We have a Student Name 1, and the file has the same name, but i need a script that add the file for each email when i do click on submit.

    I appreciate if you can guide me.

  6. Hi Ben,

    Great article. I’m trying to send the “Comments” as a hyperlink with different sources.
    I figure for a static link I can just add it in the html. But I will like for a link that I make with the data. I tried making the cell content as a Hyperlink as well but it didn’t send in the email.

    If you have any suggestion, it will be super appreciated.

    Thanks

  7. Not having any luck with this even after repeating each step multiple times…I keep getting a ‘Server error occurred. Please try saving the project again.’ error message.

  8. Hi Ben,

    I am getting this error when i run the script for the slack option.
    Request failed for https://hooks.slack.com/services/(my slack webhooks URL) returned code 404. Truncated server response: channel_not_found (use muteHttpExceptions option to examine full response)

    Please help.

    Regards,
    Ash

    1. Hi Ash!

      Make sure your code has a valid Slack channel name to post to. In my example, I put all the Slack channel names in my spreadsheet and then have the code retrieve them with this line:

      "channel": "@"+student[2]

      If it’s a direct message channel (like my student example), you have to make sure that the channel has a @ at the front. For channels like general, I don’t think you need the @ symbol…

      Hope that helps.

      Ben

      1. I am running into similar problems.
        Request failed for https://hooks.slack.com/services/(my slack webhooks URL) returned code 404. Truncated server response: channel_not_found (use muteHttpExceptions option to examine full response).

        And I solve it by using the User ID.
        You could find user’s ID by clicking its profile.
        Click “the 3 vertical dot”. There you will see the user’s ID.

        Hopefully it will help.

    1. Hey Freddy,

      After this line:

      sheet.getRange(i+4,15,1,1).setValue(timestamp);

      You could add another line, something like this to reset to No

      sheet.getRange(i+4,13,1,1).setValue("No");

      Cheers,
      Ben

  9. Hey Ben,

    Love this walk through, it’s extremely well thought out and very helpful!

    However, I was hoping to modify your example to send multiple rows in the same email, where I have multiple rows for the same student?

    Any assistance is greatly appreciated!

    1. Hey Nick,

      Sorry, must have missed your comment originally.

      You could definitely modify this to work with multiple lines. If it was always a fixed number of lines (i.e. each student had say 3 lines) then it’s not too difficult, however if it varies for each student then it would be more complex as you’d have to identify where each student stops and starts.

      The single line is identified by the range[i] in the code, where i denotes the row number, so you’d need to change this to grab however many rows you wanted, and then pass all of them through to the email and slack functions.

      Cheers,
      Ben

  10. Hey Ben,

    Let me start by saying thank you so much for putting together this meticulous well thought out guide for us fellow code-y humans. I tremendously appreciate the time and effort you’ve put in.

    This guide is incredibly close to what I’ve been researching to do. Here’s my question:
    What if you have a group Project and so you want your code to create a channel with all the group members so as to send them the feedback together, how would I go about creating the POST request for creating a channel and adding members?

    Thanks a lot!
    -Mark

  11. Hi really interesting idea. It’s kind of an integrated Mail Merge isn’t it. This has potential to be an amazing marking aid for teachers – if you created a comment box in a dropdown list, and then had option printing those results (as an alternative to email/slack!) it would save teachers hours of writing student books.
    Do you think that is possible? THank you!

    1. Hey Tom,

      Apps Script runs on the Google Servers so doesn’t have any access to local network printers, so you can’t directly print from your apps script. What you could do however, is have an option to email yourself (or a colleague or whoever) a pdf copy of the Sheet which you could then print very easily.

      See this post for how to use apps script to automatically create a pdf from a Google Sheet: https://ctrlq.org/code/19869-email-google-spreadsheets-pdf

      Hope that helps!

      Ben

  12. hi, how do i extend the scipt to more than 20 students, say i have 300 students? thanks. i really don’t know how coding works but i really believe your system will make my life easier. You are great man!

  13. Hi Ben I am getting an error when trying to send an email, the script error is the following:

    TypeError: Cannot read property “1” from undefined.

    I copied your GitHub script as is and just marked the first row as “Sumit=yes” “Channel=Email” and usd my own email address for the student.

    Any ideas what could be wrong?

    Cheers,

  14. Hello, how do I tag the person using the information on the sheets to slack? I managed to get it to work but it is not tagging the people I want to be tagged notified.

    e.g “@Ben” <- this usually will be highlighted on slack but it's not highlighted when I try to select a user from sheets and then sent to slack

    1. Hi Ken,
      Maybe you could try by adding “” to the username.
      e.g “” .
      If it is not working , replace the username with his Slack ID.
      e.g “”

      Hopefully it will solve the problem.

      1. Sorry my typing is not displayed properly.
        ===================================
        Hi Ken,
        Maybe you could try by adding to the username.
        e.g
        If it is not working , replace the username with his Slack ID.
        e.g

        Hopefully it will solve the problem.

        1. I managed to get it to work now after contacting slack support. Apparently I have to just on google sheets to get that to work. Thank you for your reply.

          I’m now looking for ways where I can change the profile picture of the bot

  15. Hello,

    Whenever I post to slack, it seems I cannot delete the thread if it was a duplicate or mistake, is there any I can bypass this without being an admin or owner of the slack server? Please help a newbie!

  16. Hi Ben,

    Thank you for this great guide. I have adapted it to connect Woccommerce and Google sheets for new orders. But I am finding it confusing to add slack in the mix. I want to send the details of the new order which are being pushed by WC webhook , into a specific channel on slack. I have the admin access of the slack channel but it is not working out.
    I checked various resources online but could not find a solution. Can you suggest ideas?

  17. Thanks so much for sharing your work on this, it was invaluable in helping me set up a similar grade notification system for my classes.

  18. I have modified this script for use in sending sales reps their daily commissions. This is a great idea. I have changed the name from “student” in the script to “rep” and now I am stuck on where I define that. Can someone point me where?

  19. Hey Ben,

    Thanks for this script, it helped a lot some tasks I had.

    I’d like to ask if you know a script who would be able to print-screen a table from Google Sheets and send it to Slack.

    Cheers,

  20. I keep getting an error on this line:
    for (var i = 0; i < range.length; i++) {
    if (range[i][7] == “Yes”) {

    Syntax error: SyntaxError: Unexpected token ‘;’ line: 13 file: Code.gs

Leave a Reply

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