Skip to content

json2excel

json2excel(data)

Description

Dump data to excel file encoded as a base64 string.

This function is useful to combine with send_mail if you want to send an excel file to some people.

Usage

Call or Deploy json2excel ?
Call json2excel directly

The easiest way to use bigfunctions

  • json2excel function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy json2excel in your project

Why deploy?

  • You may prefer to deploy json2excel in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

json2excel function can be deployed with:

pip install bigfunctions
bigfun get json2excel
bigfun deploy json2excel

Examples

select bigfunctions.eu.json2excel(json '[{"col1": "row1", "col2": 1}, {"col1": "row2", "col2": 2}]')

select bigfunctions.us.json2excel(json '[{"col1": "row1", "col2": 1}, {"col1": "row2", "col2": 2}]')

select bigfunctions.europe_west1.json2excel(json '[{"col1": "row1", "col2": 1}, {"col1": "row2", "col2": 2}]')

+------------------+
| excel_base64     |
+------------------+
| UEsDBBQAAAAAA... |
+------------------+

Use cases

A practical use case for the json2excel function would be generating and emailing a quick report of daily sales data. Let's say you have a BigQuery table called daily_sales that gets updated every day with sales information. You want to send a summary report as an Excel file to your sales team.

Here's how you could use json2excel combined with a hypothetical send_mail function (assuming it exists and takes base64 encoded attachments):

#standardSQL
CREATE TEMP FUNCTION FormatSalesForExcel(row STRUCT<date DATE, product STRING, quantity INT64, revenue FLOAT64>) AS (
    TO_JSON_STRING(row)
);

WITH SalesData AS (
    SELECT *
    FROM `your_project.your_dataset.daily_sales`
    WHERE date = CURRENT_DATE()
),
FormattedSales AS (
    SELECT FormatSalesForExcel(t) AS json_row
    FROM SalesData t
),
ExcelFile AS (
    SELECT bigfunctions.your_region.json2excel(
        '[' || STRING_AGG(json_row) || ']'
    ) AS excel_base64
    FROM FormattedSales
)
SELECT bigfunctions.your_region.send_mail(
    'sales_team@example.com',
    'Daily Sales Report',
    'Please find attached the daily sales report.',
    'daily_sales_report.xlsx', # Filename
    excel_base64
)
FROM ExcelFile;

Explanation:

  1. FormatSalesForExcel: This temporary function formats each row of the daily_sales table into a JSON string. This is necessary because json2excel expects a JSON array as input.
  2. SalesData: This CTE selects the relevant sales data for today.
  3. FormattedSales: This CTE uses the FormatSalesForExcel function to convert each row into a JSON string.
  4. ExcelFile: This CTE uses STRING_AGG to combine all the JSON strings into a single JSON array string, enclosed in brackets []. This array is then passed to the json2excel function, which returns the Excel file encoded as a base64 string.
  5. Final SELECT statement: This statement calls the hypothetical send_mail function, passing the email addresses, subject, body, desired filename, and the base64 encoded Excel data.

This example demonstrates how to use json2excel to dynamically generate an Excel file from BigQuery data and then use it within a larger workflow, such as emailing reports. You can adapt this pattern to create other kinds of reports, export data extracts in Excel format, or integrate with other systems that consume Excel files. Remember to replace your_project, your_dataset, and your_region with your actual values.

Need help or Found a bug?
Get help using json2excel

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about json2excel

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on