Blog
>
Tutorials

Connect Slack to Google Sheets (Easiest Way in 2023)

6 Minutes

In this article we will show how to connect slack to google sheets in just a few simple steps with a custom script and with Lido.

Connect Slack to Google Sheets with AppsScript

1. Create Custom App in Slack in api.slack.com/apps 

First go to https://api.slack.com/apps. You can create a custom app in Slack that allows you to send data to Google Sheets via the Slack API. Click Create an App

slack to google sheets, slack api main page

You will be asked how you want the app to be initially configured, either From scratch or From an app manifest. Select From scratch.

google sheets slack integration

You will then be asked to name the app and choose the workspace to develop your app in. Set these options, then click Create App.

slack google sheets integration

slack to google sheets

After clicking Create App, the app information and settings will be loaded. Go to the left sidebar and click OAuth & Permissions.

connect google sheets to slack

You will get several options for authenticating access to Slack data via the custom app. Scroll down to find Scopes first. 

The Scopes set the permissions and capabilities of the custom app we made. 

google sheets to slack

To add permissions, click Add an OAuth Scope. A drop-down box will appear.

google sheets slack

Add the following scopes:

channels:history

channels:read

users:read

connect slack to google sheets

2. Install Custom App

Scroll up to the top of then page, then click Install App in the left-sidebar.

slack google sheets workflow

A page with the message Install App to Your Team will be loaded. Click Install to Workspace.

slack integration with google sheets

A new tab or window will be loaded where the app you created will ask for permission to access the workspace you set in its configuration. It will list the scopes you set in the previous step. Click Allow.

permissions to access the workspace

3. Add Custom App to Slack Workspace Channel

Go back to the workspace channel where you want to add the custom app bot. Click the name of the channel with the hashtag.

workspace channel

In the channel configuration settings, click the tab Integrations. Find the Apps label and then click Add an App.

 workspace channel settings

You will be directed to the Add apps page. You can either type the name of the app in the search bar or scroll down to the list of apps in your workspace. Click the Add button besides the name of the app.

Alternatively you can type /invite YourAppName in the channel chat box and click enter.

add apps to the slack channel

A message saying the custom app bot has been added to the channel will appear in the channel.

custom app bot added to the channel

4. Add Custom Script to Google Sheets

At this point, we then open Google Sheets. You can either make a new spreadsheet or open an existing one. Afterwards, click Extensions, then select Apps Script.

click extensions, select apps script

A new tab or window will be loaded for Google Apps Script.

Clear the code area, then copy the code in the box below. You can copy the code by clicking the green copy button or by highlighting all of the text in the box. Be sure to scroll down in the box as it's a long script.


    // Script Developed by Lido.app - Import Data to a Lido Spreadsheet in Seconds without Complicated Appsscript. Sign up now. Script is free to use provided this line remains intact

const SLACK_API_TOKEN = 'xoxb-...'; // Replace with your Slack API token
const SHEET_ID = 'your_google_sheet_id'; // Replace with your Google Sheet ID

function onInstall(e) {
  onOpen(e);
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Slack Backup');
  menu.addItem('Backup messages from a channel', 'promptForChannel');
  menu.addToUi();
}

function promptForChannel() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter the channel name or ID:');
  if (response.getSelectedButton() === ui.Button.OK) {
    backupChannelMessages(response.getResponseText());
  }
}

function backupChannelMessages(channel) {
  getChannelId(channel, function(channelId) {
    getChannelName(channelId, function(channelName) {
      fetchChannelMessages(channelId, function(messages) {
        const userIds = new Set(messages.map(message => message.user));
        fetchUsers([...userIds], function(users) {
          const messagesWithNames = messages.map(message => {
            const user = users.find(u => u.id === message.user);
            const userName = user ? user.name : 'unknown';
            const messageText = message.text.replace(/<@(U\w+)>/g, (match, userId) => {
              const mentionedUser = users.find(u => u.id === userId);
              return mentionedUser ? `@${mentionedUser.name}` : match;
            }).replace(/<#(C\w+)\|([^>]+)>/g, (match, channelId, channelName) => {
              return `#${channelName}`;
            });
            return { ...message, user: userName, text: messageText };
          });

          appendToSheet(channelName, messagesWithNames);
        });
      });
    });
  });
}


function fetchSlackApi(method, params, callback) {
  const url = `https://slack.com/api/${method}`;
  const headers = {
    'Content-Type': 'application/x-www-form-urlencoded',
    'Authorization': `Bearer ${SLACK_API_TOKEN}`,
  };

  const options = {
    method: 'GET',
    headers: headers,
    payload: params,
    muteHttpExceptions: true,
  };

  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());
  if (!json.ok) {
    throw new Error(json.error);
  }

  if (json.response_metadata && json.response_metadata.next_cursor) {
    callback(json, json.response_metadata.next_cursor);
  } else {
    callback(json, null);
  }
}

function getChannelId(channel, callback) {
  fetchSlackApi('conversations.list', { limit: 1000 }, function(response) {
    const targetChannel = response.channels.find(ch => ch.name === channel || ch.id === channel);
    if (!targetChannel) {
      throw new Error('Channel not found');
    }

    callback(targetChannel.id);
  });
}

function getChannelName(channelId, callback) {
  fetchSlackApi('conversations.info', { channel: channelId }, function(response) {
    callback(response.channel.name);
  });
}

function fetchChannelMessages(channelId, callback) {
  let allMessages = [];
  let cursor = null;

  function fetchPage(cursor) {
    const params = { channel: channelId, limit: 200 };
    if (cursor) {
      params.cursor = cursor;
    }

    fetchSlackApi('conversations.history', params, function(response, nextCursor) {
      allMessages = allMessages.concat(response.messages);
      cursor = nextCursor;

      if (cursor) {
        fetchPage(cursor);
      } else {
        callback(allMessages);
      }
    });
  }

  fetchPage(null);
}

function fetchUsers(userIds, callback) {
  const users = [];

  function fetchNextUser(index) {
    if (index >= userIds.length) {
      callback(users);
      return;
    }

    const userId = userIds[index];
    fetchSlackApi('users.info', { user: userId }, function(response) {
      if (response.ok && response.user) {
        users.push({ id: response.user.id, name: response.user.profile.real_name });
      }
      fetchNextUser(index + 1);
    });
  }

  fetchNextUser(0);
}

function appendToSheet(sheetName, messages) {
  const sheet = getOrCreateSheet(sheetName);
  const rows = messages.map(message => [new Date(parseFloat(message.ts) * 1000), message.user, message.text]);

  const range = sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 3);
  range.setValues(rows);
}

function getOrCreateSheet(sheetName) {
  const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    sheet.appendRow(['Timestamp', 'User', 'Message']);
    sheet.setColumnWidths(1, 3, 150);
  }
  return sheet;
}

function getChannelName(channelId, callback) {
  fetchSlackApi('conversations.info', { channel: channelId }, function(response) {
    callback(response.channel.name);
  });
}

function fetchChannelMessages(channelId, callback) {
  let allMessages = [];
  let cursor = null;

  function fetchPage(cursor) {
    const params = { channel: channelId, limit: 200 };
    if (cursor) {
      params.cursor = cursor;
    }

    fetchSlackApi('conversations.history', params, function(response, nextCursor) {
      allMessages = allMessages.concat(response.messages);
      cursor = nextCursor;

      if (cursor) {
        fetchPage(cursor);
      } else {
        callback(allMessages);
      }
    });
  }

  fetchPage(null);
}

function fetchUsers(userIds, callback) {
  const users = [];

  function fetchNextUser(index) {
    if (index >= userIds.length) {
      callback(users);
      return;
    }

    const userId = userIds[index];
    fetchSlackApi('users.info', { user: userId }, function(response) {
      if (response.ok && response.user) {
        users.push({ id: response.user.id, name: response.user.profile.real_name });
      }
      fetchNextUser(index + 1);
    });
  }

  fetchNextUser(0);
}

function appendToSheet(sheetName, messages) {
  const sheet = getOrCreateSheet(sheetName);
  const rows = messages.map(message => [new Date(parseFloat(message.ts) * 1000), message.user, message.text]);

  const range = sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 3);
  range.setValues(rows);
}

function getOrCreateSheet(sheetName) {
  const spreadsheet = SpreadsheetApp.openById(SHEET_ID);
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    sheet.appendRow(['Timestamp', 'User', 'Message']);
    sheet.setColumnWidths(1, 3, 150);
  }
  return sheet;
}

Then paste it to the code area in Apps Script.

In the next step, we will edit the code to work with our google sheet and slack accounts.

5. Add your Slack User OAuth Token and Google Sheet ID to the Script

Go back to api.slack.com/apps and find the custom app you made earlier. Click OAuth & Permissions again in the left sidebar.

Scroll down a little to find OAuth Tokens for Your Workspace. A textbox containing the token can be found there. Click Copy

oauth & permissions page, oauth token shown

Go back to the Apps Script tab, then edit line 3:

const SLACK_API_TOKEN = 'xoxb-...';

Replace the string inside the single quotes with your token. It should also begin with xoxb.

Go back to the Google Sheets tab. Copy the google sheet id by looking at the address bar and copying the string after /d/. 

address bar containing google sheet id


In the Apps Script tab, then edit line 4:

const SHEET_ID = 'your_google_sheet_id';

Replacing the string inside the single quote marks with your sheet id. 

slack api token and sheet id

Once these changes have been made you can click the save icon beside the run button.

6. Run and Authorize the Script

We are now ready to run the script. Click the drop-down box beside the Debug button in the toolbar above the code area and select onOpen.

select onOpen function

Click Run the selected function.

 run the selected function

As this is custom code and it is the first time it is being run in Google Apps Script, you need to authorize the code to run and access your data. A pop-up box labeled Authorization Required will appear. Click Continue

 authorization required

A new window will be loaded, asking you to choose the Google account. Most of the time, only the account you are using to create the sheet will be listed. Select it.

choose account to authorize the script‍

A warning saying that Google hasn’t verified the app will appear. Click the link labeled Advanced at the bottom of the message. A link labeled Go to Untitled project (unsafe) will appear. Click it. 

prompt noting the script wasnt verified by google

A list of permissions needed by the app will be listed next. Click Allow.

permissions needed by the script listed‍

The window will close and you will be brought back to the Apps Script tab where an execution log will appear below the code. You will get two messages: Execution started then Execution completed.

 execution log, execution completed successfully‍

7. Select Slack Backup then click Backup messages from a channel

Return to the tab for Google Sheets, then click Refresh. A new option in the file menu will appear, labeled Slack Backup. Click Backup messages from a channel.

backup messages slack

8. Enter the Channel Name or ID

A small box will appear where you can enter the channel name or ID in a textbox. You must enter your channel name in lowercase without the #. After entering it, click OK.

enter slack channel name

A new tab with the same name as the channel will be added, containing all of the messages that were in the channel.

slack integration successful

You have now connected slack to google sheets!

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.