Skip to content

bigfunctions > json2excel

json2excel

Call or Deploy json2excel ?

✅ You can call this json2excel bigfunction directly from your Google Cloud Project (no install required).

  • This json2excel function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want 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). Discover the framework

Public BigFunctions Datasets:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

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.

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... |
+------------------+

Need help using json2excel?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using 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.

For professional suppport, don't hesitate to chat with us.

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.

Spread the word

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on