Send a daily report of some metrics from BigQuery to Slack

 import logging
import time
from typing import Dict, Any
from abilities import run_query_on_bigquery
import os
import slack_sdk
from slack_sdk import WebClient
from slack_sdk.errors import SlackApiError
from datetime import datetime, timedelta
from tabulate import tabulate
import requests
import json
import pytz
from apscheduler.schedulers.background import BackgroundScheduler

logger = logging.getLogger(__name__)

#TODO replace the query and title
TITLE = "Example title"
QUERY = """
your query goes here

About this template

This app fetches data from BigQuery using a provided SQL query, formats the data into a table, and posts the table to a specified Slack channel. The data posting is scheduled to happen every day at 10 am UK time.

Introduction to the Daily BigQuery Report to Slack Template

Welcome to the Daily BigQuery Report to Slack Template! This template is designed to help you automate the process of fetching data from BigQuery, formatting it into an easy-to-read table, and sending this report to a Slack channel of your choice. This is particularly useful for teams who need to keep track of key metrics and want to receive daily updates without manual intervention.

Getting Started with the Template

To begin using this template, simply click on "Start with this Template" on the Lazy platform. This will set up the template in your Lazy Builder interface, ready for customization and use.

Initial Setup: Adding Environment Secrets

Before you can use this template, you'll need to set up a couple of environment secrets that the code requires to function properly:

  • SLACK_API_TOKEN: Your Slack API token which allows the app to post messages to your Slack channel.
  • SLACK_CHANNEL: The ID of the Slack channel where you want the reports to be sent.

To add these environment secrets, navigate to the Environment Secrets tab within the Lazy Builder and enter the key-value pairs for the SLACK_API_TOKEN and SLACK_CHANNEL.

Test: Pressing the Test Button

Once you have configured the environment secrets, it's time to test the app to ensure everything is working as expected. Press the "Test" button in the Lazy Builder. This will deploy the app and launch the Lazy CLI.

Entering Input

If the template code requires any user input through the CLI, you will be prompted to provide it after pressing the Test button. In this template, there is no need for additional user input as the query and Slack channel are already defined in the code and environment secrets.

Using the App

There is no frontend experience for this app as its primary function is to send data to Slack. Once the app is running, it will automatically perform its task without further interaction.

Integrating the App

After testing the app and confirming that the reports are being sent to your Slack channel, you may want to customize the QUERY and TITLE in the code to match the specific data you wish to report on. Replace the placeholder text in the QUERY constant with your actual BigQuery SQL query and update the TITLE constant with a descriptive title for your report.

Remember, you can always modify the scheduler settings in the code if you need the report to be sent at a different time or with a different frequency. The scheduler is currently set to trigger the send_metrics function every day at 10 am UK time.

If you need to refer to any documentation or require assistance, please use the links provided in the code comments or reach out to the Lazy customer support for further help.

That's it! You're now ready to receive daily BigQuery reports in your Slack channel, all automated through your Lazy app.

Last published
May 18, 2024

More templates like this

CSV Deduper

A webpage that dedupes a CSV based on the values in the first column and allows for downloading.


Versatile Input Form

Develop a versatile input form for users.


AI Specific Website Scraper

Provide a url and the information you need to extract . It will provide you the extracted information from that url

Send a daily report of some metrics from BigQuery to Slack