Connect Slack to Google Sheets (Easiest Way in 2024)

    // 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;
}