Automating Slack with Google Sheets: Posting Unique Messages from a Spreadsheet

Ever wished you could take a list from Google Sheets—like a column of names—and magically turn it into Slack messages without cluttering your channel with duplicates? I recently tackled this exact challenge, and after some tinkering with Google Apps Script and the Slack API, I’ve got a solution that works like a charm. In this post, I’ll walk you through how to set it up, avoid duplicates, and even pop up a little alert to tell you how many messages made it to Slack. Let’s dive in!

The Goal

I had a Google Sheet with a list of names in column F, and I wanted each name to kick off a new conversation thread in a Slack channel. But here’s the catch: I didn’t want to spam the channel with repeats if a name was already posted. Plus, I needed to know how many new names actually landed. Sound familiar? Maybe you’re managing team tasks, client lists, or event attendees—this trick can adapt to all sorts of use cases.

Step 1: Setting Up the Tools

Before we code, we need a few things in place:

• Google Sheet: I used a sheet named "Sheet1" in my active spreadsheet. Column F, starting at F2, held my names (e.g., "Alice", "Bob", "Charlie"). Empty cells? No problem—they’d be skipped.
• Slack Token: I grabbed a user token (xoxp-...) from my Slack app, which needed chat:write to post messages and channels:history to check existing ones. You can create an app at https://api.slack.com/apps, add these scopes under "OAuth & Permissions," install it to your workspace, and copy the "OAuth Token for Your Workspace."
• Channel ID: For me, it was a specific channel ID (let’s call it C1234567890). Find yours by right-clicking a channel in Slack, selecting "Copy Link," and grabbing the ID from the URL (e.g., https://yourworkspace.slack.com/archives/C1234567890).

Step 2: The Script

Google Apps Script is the glue here, connecting Sheets to Slack. Here’s the code I ended up with after some trial and error:

// Slack API endpoints and tokens
const SLACK_USER_TOKEN = 'YOUR_SLACK_USER_TOKEN'; // Replace with your user token
const SLACK_CHANNEL = 'C1234567890'; // Replace with your channel ID
const POST_MESSAGE_URL = 'https://slack.com/api/chat.postMessage';
const HISTORY_URL = 'https://slack.com/api/conversations.history';

// Fetch recent chat history from Slack channel
function fetchChatHistory() {
  const options = {
    method: 'get',
    contentType: 'application/x-www-form-urlencoded',
    headers: { 'Authorization': `Bearer ${SLACK_USER_TOKEN}` },
    payload: {
      channel: SLACK_CHANNEL,
      limit: 100
    },
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(HISTORY_URL, options);
  const result = JSON.parse(response.getContentText());
  
  if (!result.ok) {
    Logger.log(`Error fetching chat history: ${result.error}`);
    return [];
  }
  
  return result.messages.map(msg =%3E msg.text);
}

// Post a message to Slack
function postMessageToSlack(name) {
  const payload = {
    token: SLACK_USER_TOKEN,
    channel: SLACK_CHANNEL,
    text: name
  };

  const options = {
    method: 'post',
    contentType: 'application/x-www-form-urlencoded',
    payload: payload,
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(POST_MESSAGE_URL, options);
  const result = JSON.parse(response.getContentText());
  
  if (!result.ok) {
    Logger.log(`Error posting to Slack: ${result.error}`);
    return false;
  } else {
    Logger.log(`Posted name: ${name}`);
    return true;
  }
}

// Main function to create threads from Google Sheets
function createSlackThreadsFromSheet() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Sheet1');
  if (!sheet) {
    Logger.log('Sheet "Sheet1" not found.');
    return;
  }

  // Get last row with data in column F
  const lastRowInF = sheet.getRange('F:F').getLastRow();
  if (lastRowInF %3C 2) {
    Logger.log('No data in column F starting from F2.');
    return;
  }

  const range = sheet.getRange(`F2:F${lastRowInF}`);
  const values = range.getValues();
  const names = values.flat().filter(String);

  // Fetch existing messages to avoid duplicates
  const existingMessages = fetchChatHistory();
  let addedCount = 0;

  // Post unique names
  names.forEach((name) => {
    if (name) {
      if (existingMessages.includes(name)) {
        Logger.log(`Skipping duplicate name: ${name}`);
      } else {
        const success = postMessageToSlack(name);
        if (success) addedCount++;
        Utilities.sleep(1000); // Avoid rate limits
      }
    }
  });

  // Show pop-up with count
  const ui = SpreadsheetApp.getUi();
  ui.alert('Slack Thread Creation Complete', `${addedCount} name${addedCount === 1 ? '' : 's'} were added to the channel.`, ui.ButtonSet.OK);
}

// Add a menu to Google Sheets
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Slack Tools')
    .addItem('Create Threads', 'createSlackThreadsFromSheet')
    .addToUi();
}

How It Works

1. Grab the Names: The script targets the "Sheet1" sheet, finds the last non-empty cell in column F (e.g., if "Charlie" is in F5, it stops there), and grabs everything from F2 down.
2. Check for Duplicates: It pulls the last 100 messages from your Slack channel using conversations.history and skips any name already posted (case-sensitive, so "Alice" ≠ "alice").
3. Post New Names: Each unique name becomes a new message, posted with a 1-second delay to stay under Slack’s rate limit (about 50 messages per minute).
4. Count and Notify: Tracks how many names get posted and pops up an alert like "3 names were added to the channel."

Setting It Up

• Sheet Prep: Add your names in "Sheet1," column F, starting at F2. Mine looked like:

  F2: Alice
  F3: Bob
  F4: Charlie
  

• Script Setup: In Google Sheets, go to Extensions > Apps Script, paste the code, replace YOUR_SLACK_USER_TOKEN with your token, and update C1234567890 with your channel ID.

• Run It: Reload the sheet, hit "Slack Tools > Create Threads," and watch the magic happen. Check View > Logs for details.

The Result

After running it, my Slack channel got three new messages: "Alice", "Bob", and "Charlie." I ran it again with "Bob" and "Dave" in the sheet—only "Dave" posted, and the pop-up said "1 name was added." No duplicates, just clean, fresh threads ready for replies!

Tips and Tweaks

• Case Sensitivity: Want "Bob" and "bob" to be the same? Change includes(name) to includes(name.toLowerCase()) and compare with existing.toLowerCase().
• More Messages?: The script checks the last 100 messages. Bump limit: 100 to 1000 if your channel’s busy, but it’ll slow things down a bit.
• Bot vs. User: I used my user token, so posts showed as me. For a bot vibe (e.g., "MyBot: Alice"), use a bot token instead, add chat:write bot scope, and invite the bot to the channel.
• Rate Limits: If you’ve got tons of names, tweak Utilities.sleep(1000) to avoid hitting Slack’s cap.

Why It’s Cool

This little script turned my Google Sheet into a Slack command center—no more manual copying or duplicate spam. It’s perfect for syncing lists (names, tasks, whatever) to Slack with zero fuss. Plus, that pop-up at the end? It’s like a high-five for a job well done.

Give it a spin with your own list, and let me know how it goes!