Google Sheets + Slack Apartment Link Tracker

This source looks out for Slack messages containing links with specific domains and stores those links, along with the person in the channel who posted them and when they were posted, in Google Sheets. I used it when coordinating an NYC apartment search with my roommates - we would find and share links with each other on https://streeteasy.com/, post them in our Slack workspace, and would go back and compare them later.

However, I've set it up so that your project will be able to look for links from any domain you'd like by setting an environment variable, so you can use it for other purposes as well.

User's Guide

To set up your tracker, you'll need to set up a Google Sheet you'd like to use. Your spreadsheet must have these three case-sensitive values in the A row, with no whitespace after them:

Sourcer
Link
Date

Your spreadsheet should look like this:

After installing your app, you can open functions/events/slack/message/channels.js to see the logic for the project. This endpoint will trigger whenever a message is posted to your channel. It checks to see whether the message text matches one of the domains that you specify using a regex like this:

let taggedDomains = process.env.TAGGED_DOMAINS.split(',').join('|');
let matches = [...(event.event.text || '').matchAll(new RegExp(`(https://|http://)?(${taggedDomains})[^\\s>]*`, 'gi'))];

Then, if the message contains one or more links for the domains we're watching for, the endpoint will insert the matches one by one into the linked Google Sheet using the googlesheets.query API:

for (let i = 0; i < matches.length; i++) {
  result.googlesheets.insertQueryResult = await lib.googlesheets.query['@0.3.0'].insert({
    range: `A:Z`,
    fieldsets: [
      {
        'Sourcer': `${result.slack.user.name}`,
        'Link': `${matches[i][0].split('|')[0]}`,
        'Date': `${format.asString('MM/dd/yy', new Date())}`
      }
    ]
  });
}

Finally, it'll send you a nice ephemeral message in Slack letting you know that some links have been stored in the Google Sheet!

result.slack.messageResponse = await lib.slack.messages['@0.6.1'].ephemeral.create({
  channelId: `${event.event.channel}`,
  userId: `${event.event.user}`,
  text: `Great finds! I've added the following links to the Google spreadsheet:`,
  attachments: matches.map((match) => {
    return {
      text: `*${match[0].split('|')[0]}*`
    }
  })
});

Thanks for reading, and I hope this helps you find the apartment of your dreams! I'm on Twitter @bleizman.