json2excel.md

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(
    '[email protected]',
    '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.