In this article we will show how to post a message to slack wherever your google sheet is updated. This could be due to a cell value being changed or a row being added.
Our solution involves importing the Google Sheet to our free spreadsheet software called Lido.
The Google Sheet we will import contains status updates to the task assigned to the listed people. What we want to do is to send a message to Slack when someone is done with the task assigned.
We will accomplish this using the built-in formula in Lido called SENDSLACK.
Open the Google sheet that you want to send a Slack message from when a cell value changes.
Click share in the top right hand corner. When the sharing window loads, click restricted under General Access and change it to Anyone with the link.
Once done click Copy Link. We will paste this link into Lido in a later step to import your spreadsheet data.
First, create a free Lido account here: www.lido.app/go/signup
To import the Google Sheet to Lido, click Connect Data on the upper-left corner of Lido.
The Add data box will load, showing you platforms you can import into Lido. Select Google Sheets.
You will be asked to paste the Google Sheets spreadsheet URL into the textbox.
After pasting the URL, click Connect with Google Sheets. You will get a message saying 'Successfully connected to Google Sheets". Click Next.
Select the column headers you want to import and then click Add Data. By default, all the columns are selected by Lido. For our example, we need all the columns, so we simply click Add Data.
Your Google Sheet has now been imported to Lido.
We need to create columns for the following:
Step 1. Create a new Computed Column called Action. This will hold the SENDSLACK formula.
To create a Computed Column, hover over column B, and you'll see a + sign. Choose "Computed Column" and it will add an empty column to the right of the table. Double click on the header cell, and rename it Action.
Step 2. Create a new Linked Column called Action Done? This column will store a note whether or not a message has been sent.
Create a Linked Column the same was as above, but choose "Linked Column". You will need to choose an ID column, and this should be another column in your data where the values are guaranteed to be unique. Timestamp, ID, or email are often good choices for unique values.
Step 3. Create another Computed Column to store the personalized message to send. Name this Message.
Your file should end up looking like this:
We will set column E to contain the message we send to Slack when Google Sheets is updated. We'll create a personalized message that pulls in the person's name.
```=A2&" has completed the task."```
What it does is to read the value in cell A2, then combine it with the string “ has completed the task”. For example, if the value of A2 is John, the corresponding message is
John has completed the task.
Insert the formula in cell E2, then press Enter.
Lido will automatically copy the formula to all the other rows, generating the message for each row.
We are now ready to add the formula to send the message to Slack. The formula is:
```=IF(AND(condition=TRUE, action_done<>"success"), SENDSLACK(<sender-credential>, channel, message, action_done))```
Where:
Action_done - cell you mark when you have successfully sent the message to Slack. For row 2 for example, that would be D2.
Condition - cell you want to scan if their value updates
Credential - Lido credential associated with the Slack workspace. You can enter this by typing in the first part of the formula then pressing "Add Credential"
A new tab will appear, where Lido App is requesting permission to access the workspace you set. Click Allow.
Channel - Slack channel name, enclose this in double quotes
Message - cell where the message you want to send to Slack is stored
In our example, this formula in cell C2 is:
```=IF(AND(B2=TRUE, D2<>"success"), SENDSLACK(<sender-credential>, "channel", E2, D2))```
The formula will read column B (labeled Task Due), send the message stored in Column E to Slack, then write success to column D to mark that the message has been sent to Slack.
After adding the formula, press Enter. Lido will also copy the formula to all the other rows along the same column.
If you haven't already sent a Slack test, choose one of records that is ready to send a Slack message (Task Due = TRUE) and choose Run Action from the individual cell menu.
When the action runs successfully, you will get a notification in the Slack workspace!
After adding all the formulas, we now want automate the sheet.
Lido makes it easy to automate an entire column of actions. Click on the menu for the Action column and select "Run column on a schedule."
The New Automation sidebar will appear. Choose an automation frequency and click Save.
And that's it!
Each interval you've selected (e.g. every 1 hour, or every day at a certain time) Lido will pull in the latest data and check if there are any Actions to be run in that column, and automatically run them.
You will now be able to receive notifications automatically in Slack!
To import the Google Sheet to Lido, click Connect Data on the upper-left corner of Lido.
The Add data box will load, showing you platforms you can import to Lido. Select Google Sheets.
You will be asked to paste the Google Sheets spreadsheet URL into the textbox.
After pasting the URL, click Connect with Google Sheets. You will get a message Successfully connected to Google Sheets. Click Next.
Select the data you need to input by selecting the columns then clicking Add Data. By default, all the columns are selected by Lido. For our example, we need all the columns, so we simply click Add Data.
The sheet is now imported to Lido.
We need to create columns for the following:
Your file should end up looking like this:
We will set column E to contain the message we send to Slack when Google Sheets is updated. We'll create a personalized message that pulls in the person's name.
```=A2&" has completed the task."```
What it does is to read the value in cell A2, then combine it with the string “ has completed the task”. For example, if the value of A2 is John, the corresponding message is
John has completed the task.
Insert the formula to cell E2, then press Enter.
Lido will automatically copy the formula to all the other rows, generating the message for each row.
We are now ready to add the formula to send the message to Slack. The formula is:
=IF( action_done<>"success", SENDSLACK(<sender-credential>, channel, message, action_done))
Where:
Action_done - cell you mark when you have successfully sent the message to Slack. For row 2 for example, that would be D2.
Condition - cell you want to scan if their value updates
Credential - Lido credential associated with the Slack workspace. You can enter this by typing in the first part of the formula then pressing "Add Credential"
A new tab will appear, where Lido App is requesting permission to access the workspace you set. Click Allow.
Channel - Slack channel name, enclose this in double quotes
Message - cell where the message you want to send to Slack is stored
In our example, this formula in cell C2 is:
```=IF(D2<>"success", SENDSLACK(<sender-credential>, "channel", E2, D2))```
The formula will check to see that a Slack message has not already been sent for that row, then write success to column D to mark that the message has been sent to Slack.
After adding the formula, press Enter. Lido will also copy the formula to all the other rows along the same column.
If you haven't already sent a Slack test, choose one of records that is ready to send a Slack message (Task Due = TRUE) and choose Run Action from the individual cell menu.
When the action runs successfully, you will get a notification in the Slack workspace!
After adding all the formulas, we now want automate the sheet.
Lido makes it easy to automate an entire column of actions. Click on the menu for the Action column and select "Run column on a schedule."
The New Automation sidebar will appear. Choose an automation frequency and click Save.
And that's it!
Each interval you've selected (e.g. every 1 hour, or every day at a certain time) Lido will pull in the latest data and check if there are any new rows, and run the SENDSLACK action formulas for just those rows.
You will now be able to receive notifications automatically in Slack!
