Awakening the Data Messenger by Integrating BigQuery with Slack

Have you ever found yourself needing to share specific data directly from a spreadsheet into a Slack channel? but the whole process of extracting the data, consolidating it, and then sending out a message seems exhausting. Or maybe you are a project manager who needs to keep everyone on the team updated with the latest data from your spreadsheets, but manually copying and pasting data from your spreadsheet to Slack seems tedious and error-prone. With Google's BigQuery, Google Sheets, Google Apps Script, and the Slack API, this task becomes incredibly straightforward.

In this blog, we'll guide you through setting up an automated Slack notification system connected to BigQuery, making data monitoring and sharing a breeze for you and your team!

Let’s check out a few use cases,

Sales and Marketing Analytics: 

• Share: Sales data, marketing campaign metrics, customer acquisition stats.
• Benefit: Keeps the team informed about performance and progress toward targets.

‍

Customer Support Analytics: 

• Share: Open support tickets, average resolution time, ticket distribution by issue or customer.
• Benefit: Enables quick responses and enhances customer service quality.

Operational Metrics: 

• Share: Warehouse performance, supply chain stats, production data.
• Benefit: Increases awareness of operational issues and the impact of changes.

‍

Project Management: 

• Share: Open tasks, completed tasks, reached milestones.
• Benefit: Ensures timely project progression and addresses bottlenecks.

‍

Website Analytics: 

• Share: Page views, unique visitors, user behavior insights.
• Benefit: Informs about website performance and highlights areas for optimization.

‍

Software Development: 

• Share: Software testing results, bug tracking, system performance, feature usage stats.
• Benefit: Accelerates software improvements and ensures consistent quality.

‍

These are just a few examples. The beauty of this system is that it's highly flexible and can be adapted to a wide range of use cases - anywhere you have data in BigQuery that you want to share regularly with your team.

Step 1: Constructing the BigQuery Query

Our first step involves formulating a BigQuery SQL query to extract the data we want to monitor. For instance, if we want to track our daily total sales, our SQL query might look like this:

  
SELECT DATE(order_timestamp) AS order_date, SUM(total_amount) AS daily_sales
FROM `my_project.my_dataset.my_table`
WHERE DATE(order_timestamp) >= (CURRENT_DATE() - INTERVAL 1 WEEK)
GROUP BY order_date
ORDER BY order_date DESC;
 

‍
The results of this query give us the total sales for the last 7 day available in the data.

‍

Step 2: Connecting BigQuery to Google Sheets

Google Sheets comes with a useful feature that lets you connect directly to BigQuery. To do this,

• Go to Data > Data connectors > Connect to BigQuery within your Google Sheets interface.

‍

• This connection enables you to either directly link to your BigQuery table or input a ‘custom query’ to extract the necessary data. 

‍

• In the future if you need to update the query, you can click on ‘connection settings’ on the BigQuery connector spreadsheet

‍

‍

• Once you've fetched the data, you can perform any required data transformations on a new separate sheet by clicking on the ‘extract’ button 

‍

• You can also set the data refresh rate by enabling the ‘Refreshed options’ on the left side which opens up a sidebar to the right. Enable the scheduled refresh option with the desired settings. This ensures your data in the spreadsheet stays current and relevant. 

‍

‍

Step 3: Creating a Slack App

‍

‍

To send our data to Slack, we need to create a custom Slack App and use its 'Incoming Webhooks' feature. Follow these steps:

• Visit Slack API and connect to your Slack account

‍

‍

• Click on "Create New App” > From scratch

‍

• Name your app and choose your Slack workspace.

• Once your app is created, select 'Incoming Webhooks'.

• Turn on the 'Incoming Webhooks' and click on 'Add New Webhook to Workspace'.

‍

• Choose the Slack channel you want to send your notifications to or create a new channel for your company settings.

‍

‍

• Copy the Webhook URL, which will be used later within the App Script code.  

‍

Step 4: Setting Up Google Apps Script Functions

Our final step involves using Google Apps Script (a JavaScript-based scripting language) to fetch our data from the spreadsheet and send it to our Slack channel. 

Click on Extensions > App Script: create a new Apps Script file

The script editor will be empty, and you will need to follow the steps below to set things up. We'll need to write three functions

• buildReport: This function fetches data from our spreadsheet builds the notification payload, and is responsible for getting the data from cells A2:B: skipping the header name.
• buildAlert: This function formats the data into a user-friendly format.
  The data you just fetched in the previous step is in JavaScript array format. You can’t send it to Slack in this format – Slack won’t understand it. Instead, it needs to be converted into specially structured JSON that Slack will understand. Slack calls this structure ‘Blocks‘. 
• sendAlert: This function takes the payload you just created in step 2 as an argument. The ‘options’ object simply makes sure we are making the right type of request in the right format, then the ‘UrlFetchApp.fetch’ part makes the request. We wrap it in a try/catch block, just in case something goes wrong. The only part you need to do is paste in the webhook URL you created
  
  

‍

Here is an example of how you might implement these functions:

  
// Function to build the sales report.
function buildReport() {
// Get the active spreadsheet.
const ss = SpreadsheetApp.getActive();


// Get data from the "weekly_sales" sheet, columns A and B, from the second row to the end.
let data = ss.getSheetByName("weekly_sales").getRange("A2:B").getValues();


// Build the payload for the Slack message from the data.
let payload = buildAlert(data);


// Send the payload as a Slack message.
sendAlert(payload);
}


// Function to process and format the sales data.
function mySalesDaTa(data){
// Log the original data for debugging purposes.
Logger.log(data)


// Filter the data to remove any rows where the first column (the date) is missing or null.
data = data.filter(item => item[0] && item[0].toString().length > 0);


// Log the filtered data for debugging purposes.
Logger.log(data)


// Map each row in the data to a string in the format '[date] $[sales]\n'.
var sales = data.map(item => {
// Format the date using the script's time zone and the format "yyyy-MM-dd".
var formattedDate = Utilities.formatDate(new Date(item[0]), Session.getScriptTimeZone(), "yyyy-MM-dd");
return '' + formattedDate + ' $' + item[1]+ "\n";
}).join("\n"); // Join all the strings into a single string separated by newlines.


// Log the final sales string for debugging purposes.
Logger.log(sales)
// Return the final sales string.
return sales;
}


// Function to build the payload for the Slack message.
function buildAlert(data) {
// Get today's date.
let today = new Date();


// Process and format the sales data.
let sales = mySalesDaTa(data);
Logger.log(sales)


// Build the payload for the Slack message.
let payload = {
'username' : 'My Test Slackbot',
'text' : 'Datadice rocks!!!',
'attachments': [{
'text': ' :chart_with_upwards_trend: Weekly Sales Metrics :dollar:'+ "\n" + sales + "\n",
}]
}


// Return the payload.
return payload;
}


// Function to send the payload as a Slack message.
function sendAlert(payload) {
// Paste your webhook URL here.
const webhook = "";


// Define the options for the HTTP request.
var options = {
"method": "post",
"contentType": "application/json",
"muteHttpExceptions": true,
"payload": JSON.stringify(payload)
};


// Try to send the HTTP request.
try {
UrlFetchApp.fetch(webhook, options);
} catch(e) {
// If the request fails, log the error.
Logger.log(e);
}
}

 

‍

Here is how the alert Slack looks like

‍

‍

Step 5: Schedule your Slack bot.

Lastly, don't forget to set up triggers for your Google Apps Script functions to control when your alerts get sent out. Triggers can be accessed in the Apps Script editor under Edit > Current project's triggers.

‍

And that's it! With these four simple steps, you can build a powerful automated Slack notification system that integrates directly with your BigQuery data without leaving your Google Sheets interface. Say goodbye to manual data extraction and hello to streamlined data sharing!

Conclusion

While this blog post focuses on connecting Google Sheets with BigQuery and then Slack using Google Apps Script, the beauty of these tools lies in their adaptability. This process can be tailored to fit various use cases and data sources. This flexibility allows us to better harness data, make informed decisions faster, and ultimately drive our operations more efficiently.

As technology continues to evolve, so will how we can leverage these tools. So stay curious, keep exploring, and remember - when it comes to automating your workflows, the sky is truly the limit!

‍

Further Links

‍

Check out our LinkedIn account to get insights into our daily working life and essential updates about BigQuery, Data Studio, and marketing analytics.

‍

We also started with our own YouTube channel. We discuss important DWH, BigQuery, Data Studio, and many more topics. Check out the channel here.

‍

If you want to learn more about using Google Data Studio and taking it to the next level in combination with BigQuery, check out our Udemy course here.

If you need help setting up an ETL Pipeline on Y42 or need help setting up a modern and cost-efficient data warehouse or analytical dashboards in general, email us at hello@datadice.io, and we will schedule a call.