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:
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.