Push to Slack Channel on Google Form submission.

(Original article was written November 7th 2015)

A recent project that I was apart of was using Google forms for an account request process.  It is not unusual to want to notify a team about new submission and the group working on this project recently adopted using Slack for some communications.  The first thing I looked for is an existing “add-on” to Google spreadsheet, which there was at the time but it has some limitations for the initial free version to about 40 notifications and then you need to buy more (apparently “add-ons” no longer can run in the background so there is no Slack notification “add-on” anymore). The price wasn’t steep but I figured this would be a great opportunity to play with the both theGoogle sheet scripts and the Slack API to push some automated notifications to one of our channels – also when you write and deploy your own solution you are confident that it is more secure and your information is not going somewhere else (with an app/add-on you have no idea what is happening).  So after some research into doing some Google sheets  app scripting.  I came up with the following functions (source can be found on github https://github.com/scleveland/google-form-to-slack).


function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
 
  //Set a trigger when the form updates the spreadsheet to call our Slack notification function
  ScriptApp.newTrigger("CreateMessage")
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit()
    .create();
 
}

function CreateMessage(e){
  try {
    var spreadsheet, columns;
    var my_message;

    //fetch the column names
    spreadsheet = SpreadsheetApp.getActiveSheet();
    columns = spreadsheet.getRange(1, 1, 1, spreadsheet.getLastColumn()).getValues()[0];

    // Only include form values that are not blank
    for (var keys in columns) {
    var key = columns[keys];
      var val = e.namedValues[key] ? e.namedValues[key].toString() : "";
      if (val !== "") {
        my_message +=key + ' :: ' + val + '\n';
      }
    }
    SendSlackMessage(my_message);

  } catch (e) {
    Logger.log(e.toString());
  }
}

function TestSlack(){
  SendSlackMessage("testing my stuff"); 
}

function SendSlackMessage(message){
var url = "https://slack.com/api/chat.postMessage";
  
  var payload =
      {
        "token" : "CHANGE TO YOU KEY",
        "as_user" :"false",
        "text" : "New Request\n" + message,
        "channel" : "#YOUR-CHANNEL",
        "attachments" : [{"pretext": "Notification", "text": message}],
        "type" : "post",
      };
  
  var options =
      {
        "method"  : "POST",
        "payload" : payload,   
        "followRedirects" : false,
        "muteHttpExceptions": true
      };
  
  var result = UrlFetchApp.fetch(url, options);
  
  if (result.getResponseCode() == 200) {
    
    var params = JSON.parse(result.getContentText());
    
    Logger.log(params);
  }
}

To use the above code you must have a Google form setup then go to the response Google sheet associate with it.  Once on the sheet you can go to “Tools->script editor…”.  A new tab should open with the script editor and some default code in the “Code.gs” tab should be visible.  Remove the default code and replace it with the above and save it as whatever project name you wish.  Next you need to run the initialize function by going to “Run” in the menu and choosing “initialize” it will ask you to approve and allow the script to run – do so.

To get this function with Slack you have to have to use your Slack user or create a Bot and set a token that you can use as part of your payload to authenticate to your Slack team space (NOTE the “as_user” field can be set to “true” s that the either you or the named bot show up as the entity posting the message in the channel if you leave this “false” it just posts as “bot”).  You can do this by going to you account settings or your integration settings if you wish to use the Bot. Next you need to decide which channel to use or create a new one and set it in the payload (NOTE If you are using a specific bot to post the message they have to be a member of this new channel otherwise it will not be able to post messages).

Once you have repleaced the token and channel in your script and it has been initialized by running the initialize function in the editor you are good to go. You can test that the settings are correct and will post to your slack channel by selecting the TestSlack function from the dropdown and pushin the play/run button – it should add a message in your slack channel “testing my stuff”. You can ten test by submitting to your Google form and seeing the results in your slack channel.

Comments ( 5 )

  1. ReplyJustin Noel
    I get this error: "ReferenceError: "columns" is not defined. (line 17, file "Code")" What's missing and how do I fix it?
    • Replyscleveland
      Justin, Are you implementing this script on the google sheet? If you try and do this on the actual form it will not work as this code triggers when the sheet updates and then sends the new update to Slack. Also, you can't select the "SendSlackMessage" function and try to run it from the script editor because you have no payload (i.e new columns) so it throws that error. To test it you need to do a form submission and check that your Slack channel receives the update. Hopefully that helps. Cheers, -Sean
    • Replyscleveland
      Justin, You might find the refactored code works easier if you are still having issues. Cheers, Sean
  2. Replyschlingel
    Thank you for sharing! One thing I don't get: You create the variable user_info and add all the non-blank keys into it. After that it is never used. Please help me understand.
    • Replyscleveland
      schlingel, In my haste in generalizing this code initially I left some things out. I have gone back and refactored the code and updated it so it functions in two separate functions - one to process the spreadsheet and one to send the slack message - this is how it was originally written with the first function actually sending an email. I have also added a test function that can be run from the script editor once you have updated your token and channel so that form submission is not required to be sure that you have configured slack correctly. Please also see the updated directions as they a few more details now. Cheers, Sean

Leave a Reply