Send a Slack Message when a Google Sheet is Updated [2023]
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.
Send Slack Message When a Cell Value is Changed
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.
1. Prepare your Google Sheet
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.
2. Click Data to Import the Sheet
First, create a free Lido account here: www.lido.app/go/signup
To import the Google Sheet to Lido, click 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 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.
3. Add Columns for the Action, Slack Message, and Status
We need to set columns for the following:
- Formula to send a message to Slack. We will call this column Action;
- A Note to indicate whether a message was sent to Slack or not. As we will automate this later on, this will ensure that notifications about the same update will not be repeatedly sent. We call this column Action Done?; and
- The notification message itself. We call this column Message.
4. Generate Slack Message
We will set column E to contain the message we send to Slack when Google Sheets is updated. We can create a generic message and then copy it to all columns. Alternatively, as the sheet is about checking task completion, we want to personalize it a bit by adding the name of whoever completed the task. To do so we can use the following formula:
```=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.
5. Add Formula =IF(AND(condition=TRUE, action_done<>"success"), SENDSLACK(workspace, channel, message, action_done)) to Send Message to Slack
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(workspace, channel, message, action_done))
Where
Action_done - cell you mark when you have successfully sent the message to Slack
Condition - cell you want to scan if their value updates
Workspace - Slack workspace name, enclose this in double quotes. This should be the name of the workspace (e.g. "Lido") and NOT the Slack URL
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("workspace", "channel", E2, D2))```
The formula will read column B (labeled Task Done?), 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.
6. Automate Sending to Slack
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!
7. Test Run the Sheet
You need to run the Actions in the sheet for the first time in order to connect Lido to Slack. Right-click one of the SENDSLACK formulas, then select Run action. For this example we will. choose a row whose value under column B has changed from FALSE to TRUE.
.png)
Right Click, Select Run action
A new tab will appear, where Lido App is requesting permission to access the workspace you set. Click Allow.
When the action runs successfully, you will get a notification in the Slack workspace!
Send Slack Message When a Row is Added
1. Click Data to Import the Sheet
To import the Google Sheet to Lido, click 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.
2. Add Formula =IF(action_done <> "success", SENDSLACK(workspace, channel, message, action_done)) to Check If New Row is Added
Hover over your table and select Add Linked Column. This will be your action_done column, where you'll keep track of whether a Slack message has already been sent. If a message has not been sent, then we'll want to send one.
.png)
Next, right click in your table and add a computed column:
Inside of your computed column, enter this formula and hit enter:
=IF(action_done <> "success", SENDSLACK(workspace, channel, message, action_done))
Where
Action_done - cell you mark when you have successfully sent the message to Slack
Workspace - Slack workspace name, enclose this in double quotes
Channel - Slack channel name, enclose this in double quotes
Message - cell where the message you want to send to Slack is stored
This formula checks to see whether a message has already been sent for a row and sends a message if one has not. When a new row gets added, Lido's computed column will automatically copy the formula down to apply for the new row of data.
3. Automate the Action column
Now, let's automate checking whether there are any actions to run in our column, and run them automatically if there are.
Click the menu button in your Action column and select Run column on a schedule.
The New Automation sidebar will appear. Select an interval for checking whether there are updates and click save.
On your selected interval, Lido will automatically refresh the latest data from Google Sheets, check to see if there are any Actions to run, and run them.
We will now automatically get notifications whenever a new row is added to Google Sheets!
4. Test Run the Sheet
You need to run the actions in the sheet for the first time in order to connect Lido to Slack. Right-click any cell with a SENDSLACK formula, then select Run action.
A new tab will appear, where Lido App is requesting permission to access the workspace you set. Click Allow.
Lido App is Requesting Permission to Access Slack Workspace
When the action runs successfully, you will get a notification in the Slack workspace!
Slack Message New Rows Added
