upload_table_to_gsheet¶

upload_table_to_gsheet(table_or_view_or_query, max_rows, spreadsheet_url, worksheet_name, write_mode)

Description¶

Upload data from table_or_view_or_query to Google Sheet (maximum max_rows rows will be uploaded).

💡 For this to work, share your Google Sheet in edit mode to 749389685934-compute@developer.gserviceaccount.com

write_mode controls what is done if a worksheet with worksheet_name already exists. It must be one of:

write_truncate: if the sheet already exists, it will be recreated.

write_append: if the sheet already exists, data will be appended to it.

raise_error: if the sheet already exists, an error will be raised.

do_nothing: if the sheet already exists, nothing will be done.

null: same as write_truncate

Usage¶

Call or Deploy upload_table_to_gsheet ?

Call upload_table_to_gsheet directly

The easiest way to use bigfunctions

upload_table_to_gsheet 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 upload_table_to_gsheet in your project

Why deploy?

You may prefer to deploy upload_table_to_gsheet 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

upload_table_to_gsheet function can be deployed with:

pip install bigfunctions
bigfun get upload_table_to_gsheet
bigfun deploy upload_table_to_gsheet

Examples¶

1. upload 1000 rows from a table

EUUSeurope-west1

call bigfunctions.eu.upload_table_to_gsheet(
      'bigfunctions.eu.sales', 
      1000, 
      'https://docs.google.com/spreadsheets/d/xxxxxxxxx', 
      'my worksheet', 
      'write_truncate');

call bigfunctions.us.upload_table_to_gsheet(
      'bigfunctions.us.sales', 
      1000, 
      'https://docs.google.com/spreadsheets/d/xxxxxxxxx', 
      'my worksheet', 
      'write_truncate');

call bigfunctions.europe_west1.upload_table_to_gsheet(
      'bigfunctions.europe_west1.sales', 
      1000, 
      'https://docs.google.com/spreadsheets/d/xxxxxxxxx', 
      'my worksheet', 
      'write_truncate');

2. with a query

EUUSeurope-west1

call bigfunctions.eu.upload_table_to_gsheet(
      '(select 1 as foo)', 
      null, 
      'https://docs.google.com/spreadsheets/d/xxxxxxxxx', 
      'my worksheet', 
      'write_truncate');

call bigfunctions.us.upload_table_to_gsheet(
      '(select 1 as foo)', 
      null, 
      'https://docs.google.com/spreadsheets/d/xxxxxxxxx', 
      'my worksheet', 
      'write_truncate');

call bigfunctions.europe_west1.upload_table_to_gsheet(
      '(select 1 as foo)', 
      null, 
      'https://docs.google.com/spreadsheets/d/xxxxxxxxx', 
      'my worksheet', 
      'write_truncate');

Use cases¶

Here are a few use cases for the upload_table_to_gsheet function:

1. Reporting and Sharing Data:

Regular Reporting: A marketing team could use this function to automatically export weekly or monthly website traffic data from a BigQuery table to a Google Sheet. This sheet could then be used for reporting, visualization, and sharing with stakeholders who may not have direct access to BigQuery.
Ad-hoc Data Extracts: A business analyst might need to quickly extract a subset of customer data for a specific analysis. They could use upload_table_to_gsheet to pull the relevant data into a Google Sheet for easier manipulation and sharing with collaborators.
Data Sharing with External Parties: You might need to share data with a client or partner who doesn't have access to your BigQuery project. Exporting the data to a Google Sheet offers a simple and accessible way to share information.

2. Collaboration and Data Entry:

Collaborative Data Editing: A team working on a project might use a Google Sheet as a central hub for data entry and review. upload_table_to_gsheet could be used to seed the sheet with initial data from BigQuery, allowing the team to build upon it collaboratively.
Collecting Feedback: You could upload survey results from BigQuery to a Google Sheet to facilitate collaborative analysis and discussion among team members.

3. Data Integration and Transformation:

Preprocessing Data for Other Tools: Some tools and applications might not have direct integration with BigQuery. Exporting data to a Google Sheet can serve as an intermediary step, allowing you to format and prepare the data for import into those tools.
Manual Data Cleansing and Enrichment: While BigQuery is powerful for data transformation, sometimes manual cleaning or enrichment is necessary. Exporting data to a Google Sheet provides a user-friendly interface for making such adjustments.

4. Small-Scale Data Backup:

Backing Up Important Tables: For relatively small tables, upload_table_to_gsheet can be a simple way to create a backup copy in a different format. However, for large datasets, BigQuery's native backup and recovery mechanisms are more suitable.

Example Scenario:

An e-commerce company uses BigQuery to store sales data. Every Monday, the marketing team needs a report of the previous week's sales by product category. They could schedule a query to calculate this data and then use upload_table_to_gsheet to automatically export the results to a designated Google Sheet. This automates the reporting process and makes the data readily available for analysis and visualization.

Need help or Found a bug?

Get help using upload_table_to_gsheet

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about upload_table_to_gsheet

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