Skip to content

bigfunctions > upload_table_to_gsheet

upload_table_to_gsheet

Call or Deploy upload_table_to_gsheet ?

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

  • This upload_table_to_gsheet 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

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

  1. 💡 For this to work, share your Google Sheet in edit mode to 749389685934-compute@developer.gserviceaccount.com
  2. 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

Examples

1. upload 1000 rows from a table

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');

screenshot

2. with a query

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');
Need help using upload_table_to_gsheet?

The community can help! Engage the conversation on Slack

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

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

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

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.

Spread the word

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

Share on Add a on