Connect Slack to Google Sheets (Easiest Way in 2024)
May 8, 2024
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.
You will be asked how you want the app to be initially configured, either From scratch or From an app manifest. Select From scratch.
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.
After clicking Create App, the app information and settings will be loaded. Go to the left sidebar and click OAuth & Permissions.
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.
To add permissions, click Add an OAuth Scope. A drop-down box will appear.
Add the following scopes:
channels:history
channels:read
users:read
2. Install Custom App
Scroll up to the top of then page, then click Install App in the left-sidebar.
A page with the message Install App to Your Team will be loaded. Click Install to Workspace.
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.
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.
In the channel configuration settings, click the tab Integrations. Find the Apps label and then click Add an App.
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.
A message saying the custom app bot has been added to the channel will appear in 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.
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.
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/.
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.
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.
Click 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.
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.
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.
A list of permissions needed by the app will be listed next. Click Allow.
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.
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.
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.
A new tab with the same name as the channel will be added, containing all of the messages that were in the channel.
You have now connected slack to google sheets!
We hope this article has helped you and given you a better understanding of how to connect Slack to Google Sheets. You might also like our articles on how to connect Typeform to Google Sheets and connect Discord to Google Sheets.
To optimize your workflow, we recommend reading our guide on how to import a CSV from an email into Google Sheets.
Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Work less, automate more!
Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->
