Export Slack channel messages to Google Sheet

Welcome! In this blog, I am going to share how to export your Slack channel message to an Excel sheet using Google App Script. This will help in backing up selected channel messages, and better filtering and visualizing messages in a chart.

And all that you need is,

Prerequisites

▹ Slack workspace.

▹Google sheet with App Script.

Slack account changes

Create a New Workspace in Slack or choose an existing one.

Slack workspace

• We will be using Slack API to fetch data from the Slack channel.
• Slack has exposed a wide range of APIs that can be used to control our Slack Workspace. To make use of it we need to create a Slack App and integrate it with our workspace. (Here is the documentation on Slack API)
• Go to https://api.slack.com/ and create a new app.

Slack create app

• Choose, From Scratch in the next step.

Slack create app options

• Enter a Slack App Name and Pick a Workspace.

Slack choose workspace

• Great, we have created our Slack app. Now we need to generate a User Token which is basically used to authorize our API requests. (Here is the documentation on Slack Tokens).
• Click on Permissions under Add features and functionality.

Slack set permissions

• Under the Scopes section, we have our User Token Scopes. Enable the following User Tokens.
  – channels:history
  – users:read
  – channels:read

Slack enable permissions

• Click Install to Workspace under OAuth Tokens for Your Workspace section.

Slack install workspace

• Click Allow in the Next step.

Slack allow permission

• Now we have successfully created our User OAuth Token, which looks like xoxp-xxxxxxxxxxxxx-xxxxxxxxxxxxx-xxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxit will be used in our API to authenticate our request.

Slack generates an OAuth token

Google Sheet changes

• Create a new Google sheet or choose an existing one, and click Extension -> App Script.

Google Sheet enable app script

• This will open a new tab in which we are going to add our logic for exporting Slack Channel messages.
• Add the following code to the Code.gs file.

const SHEET_NAME = 'Sheet1'; //Sheet to extract the message goes here
const SLACK_USER_TOKEN = "xoxp-xxxxxxxxxxxxx-xxxxxxxxxxxxx-xxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; //Slack user token goes here
const CHANNEL_HISTORY_URL = "https://slack.com/api/conversations.history";
const USER_INFO_URL = "https://slack.com/api/users.info";
const CHANNEL_LIST_URL = "https://slack.com/api/conversations.list";
const WORKSHEET = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
const MESSAGE_LIMIT=200;

//This method gets executed on Sheet open.
//It sets the drop down filter for channel name.
function onOpen() {
  const googleSheet = SpreadsheetApp.getUi();
  googleSheet.createMenu('My Menu')
     .addItem('Export Channel Message', 'ExportMessage') //Adds MenuClicked menu
     .addToUi();

  let resultSheet = WORKSHEET;
  resultSheet.setColumnWidth(1,400);
  resultSheet.setColumnWidth(2,200);
  generateChannelFilter();
}

//This method exports channel message to excel sheet
function ExportMessage() {
  let resultSheet = WORKSHEET;
  let channelName = resultSheet.getRange(SHEET_NAME+'!B1:B1').getValue();
  if (channelName === '')
    return;
  resultSheet.getRange(SHEET_NAME+'!A3:A3').setValue('Channel Messages')
  resultSheet.getRange(SHEET_NAME+'!A3:A3').setBackground('#7ec7ff')
  resultSheet.getRange(SHEET_NAME+'!A3:A3').setFontWeight('bold')
  resultSheet.getRange(SHEET_NAME+'!A4:A').setValue('');
  let messageResp = null;
  let cursor = '';
  let startRow = 4;
  do {
    messageResp = fetchData(getURL(CHANNEL_HISTORY_URL, 
                      { "channel":channelName.split(":")[1], 
                        "limit": MESSAGE_LIMIT,
                        "cursor": cursor }
                  ));
    if (messageResp.has_more) {
      cursor = messageResp.response_metadata.next_cursor;
    }
    if (messageResp.ok) {
      let messages = messageResp.messages;
      for(let i=0 ; i < messages.length ; i++) {
        let message = modifyMessage(messages[i].text);
        resultSheet.getRange(startRow, 1).setValue(message)
        startRow++;
      }
    }
  }
  while(messageResp?.has_more)
}

//Helper method to change user id to user name
function modifyMessage(messageText){
  if (messageText[0] === '<' && messageText[1] === '@' && messageText[13] === '>')
  {
    let userID = messageText.substring(2,13);
    let userName = slackGetUser(userID);
    messageText = messageText.replace("<@" + userID + ">", userName);
  }
  return messageText;
}

//Helper method to get user name using user id
function slackGetUser(userID) {
  let userResp = fetchData(getURL(USER_INFO_URL, {"user":userID}));
  return userResp.user.profile.real_name;
}

//Gets all channel name
function generateChannelFilter() {
  let resultSheet = WORKSHEET;
  resultSheet.getRange(1,1).setValue('Choose Channel:')
  resultSheet.getRange(1,1).setBackground('#7ec7ff')
  resultSheet.getRange(1,1).setFontWeight('bold')
  let channelResp = fetchData(getURL(CHANNEL_LIST_URL, {}));
  let rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(channelResp.channels.map(a=> a.name + ":"+ a.id));
  resultSheet.getRange(SHEET_NAME+'!B1:B1').setDataValidation(rangeRule);
}

//Helper method to generate URL
function getURL(url, querys) {
  let queryParms = '';
  for(let key in querys){
    queryParms += key + "=" + querys[key] + "&";
  }
  if (queryParms !== '')
    url += "?" + queryParms.slice(0, -1);

  return url;
}

//Helper method to make API calls
function fetchData(url) {
  let options = {
      "method": "get",
      "headers": {
          "Authorization" :  "Bearer " + SLACK_USER_TOKEN
      }
  };
  let response = UrlFetchApp.fetch(url, options);
  return JSON.parse(response.getContentText());
}

• Click on Triggers and Create a new Trigger.

Google sheet trigger setup

• Choose onOpen method in the function to run and the On open option in the Event Type and click Save.

Google Sheet App Script trigger settings

• The next step will request Google Verification, Choose your account, click Advance, and then click Go to DemoAppScript (unsafe).

Google Sheet permission for App Script

• Click Allow in the next step.

Allow App Script to use Google Sheet

• This will create a new Trigger for onOpen function.

App Script Trigger setting for Google Sheet

• Now Refresh your Google sheet
• You’ll have a drop-down in cell B1 to choose the channel, choose the channel in which you want to export the message.

Google sheet after app script setup

• Also, there will be an additional option in Menu Bar, My Menu. Click on it and choose Export Channel Message.

Google Sheet export slack channel message

• This will export all Channel Messages.

Google Sheet export slack channel message

Conclusion

This is just a small App Script with start-up logic for exporting Slack Channel messages, modifying theExportMessage method or modifyMessage method can help in generating more customized results. The exported messages can be used as a backup.