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 inbigfunctions
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:
- FormatSalesForExcel: This temporary function formats each row of the
daily_sales
table into a JSON string. This is necessary becausejson2excel
expects a JSON array as input. - SalesData: This CTE selects the relevant sales data for today.
- FormattedSales: This CTE uses the
FormatSalesForExcel
function to convert each row into a JSON string. - 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 thejson2excel
function, which returns the Excel file encoded as a base64 string. - 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!