We love to try new things, and therefore often share links to interesting information from the world of IT and programming with our colleagues. We are long-standing users of slack and for such links we have a separate educational channel where everyone can find something interesting for themselves. But since we are ordinary people, periodically in the heat of work, we forget that we need to share these links, and the activity in the channel fades, although many of us have something to share.We faced an interesting task of developing a “bot” that could automatically take pre-collected information and share links to it on a regular basis. We were looking for a ready-made solution on the Internet, and now it has appeared as an integration of zapier and slack .
However, we did not find such integration before and decided to write our own small bicycle. Below we will describe how exactly we did it.When thinking about the problem, the following solution came to my mind:Javascript code that receives information about relevant useful links from the google table and sends it to Slack. Calling a script from the first item on a schedule using cron. Let's start in order.Getting a token for Slack.
Everything is simple here and it has been repeatedly described in other articles, so I will give only a brief description.There are two options: make your own Slack application or use legacy tokens, the first option is a bit more complicated, but it is recommended by Slack, the second one is more simple and quite suitable for our needs, because of its simplicity we will choose it.We open a link to generate a new token and create a new token for the desired workspace (do not forget to copy it, the token is issued once).
In the future, when implementing code that interacts with the Slack API and Google Sheets, we will be able to use this token for its intended purpose.Now you need to get the token for Google Sheets. First, open Node.js Quickstart for Google Sheetsand enable the Google Sheets API. Then we copy the code index.js from the example and execute it, go through the authorization process and get the credentials in the file, which are useful for use with our script.Script implementation
Now is the time to start developing a script to publish messages from Google Sheets to Slack. The full script code can be found by clicking on the link .First of all, we will analyze the part of the code that sends messages to the slack channel.This happens by calling the Slack REST API.const post = async (text, channel = <CHANNEL ID>) => {
const uri = `https://slack.com/api/chat.postMessage?token=${SLACK_AUTH_TOKEN}&channel=${channel}&text=${text}`;
const result = await fetch(encodeURI(uri), {
headers: {
'Content-Type': 'application/json',
},
method: 'POST',
body: JSON.stringify({
channel,
text,
as_user: true
})
});
await result.json();
};
For authorization in the Google API, two functions are used (authorize, getNewToken), the code of which is taken from Node.js Quickstart, we will not dwell on them in detail.The next interesting block of code is getting information to send from Google spreadsheets, this is done using the following function:const getMessageAndPost = async (auth, spreadsheetId = <ID Google >) => {
const sheets = google.sheets({ version: 'v4', auth });
sheets.spreadsheets.values.get({
spreadsheetId,
range: 'Sheet1!A1:B999',
}, async (err, res) => {
if (err) return console.log('The API returned an error: ' + err);
const rows = res.data.values;
if (rows.length) {
// Print columns A and E, which correspond to indices 0 and 4.
const ix = rows.findIndex(r => !r[1]);
await post(rows[ix][0]);
sheets.spreadsheets.values.update({
spreadsheetId: '18VzilQTEDGXBnaH1f_k-uAfa8Mb470gx32Phir6xQT4',
range: `Sheet1!B${ix + 1}`,
valueInputOption: 'RAW',
requestBody: {
range: `Sheet1!B${ix + 1}`,
values: [['x']]
}
})
} else {
console.log('No data found.');
}
});
};
The table with which the script interacts has the following form:
Accordingly, for next to each link after sending a message to Slack, the script puts “x”. Thus, to find the message you want to send, you should select the first line, which does not yet have an “x”, take the message, send it and mark the selected line.The script that we just disassembled is quite simple and has confirmed its performance for several years in our team. We just have to deploy this script to the server and enjoy using it.For this, in our company we use the usual Ubuntu VPS on Digital Ocean (even the most low-power one will do).First of all, you need to configure the environment for executing javascript code, to do this, install node.js (installation guide )After that, configure the cron task to call the script on a schedule:crontab -e
####
24 14 * * 1-5 cd /root/google-sheets-to-slack && node post.js
Using these settings, our script from the / root / folder will be called daily from Monday to Friday at 14:24.The setup is over, but the attentive reader must have noticed some flaws in the script, for example, if there is any error when calling the script, we will never know about it, because it will “quietly fall” and that’s all. To solve this problem, you can add error handling and output of the result of the script call to the same Google spreadsheet or log file.Also, instead of sending a message at a fixed time, you can send it with a random delay, so that our message is more like a message from a “live” user.Another interesting idea from one of our colleagues was to create a second table from which messages would be sent exclusively on Fridays and would be more entertaining.In general, we see countless ideas for refinement and we will gradually implement them, but in the existing form this script came out quite usable.An example table that can be used is available here .Thanks for reading and enjoy using.