Connect and transfer data from Google Sheets to BigQuery

Follow the steps below to connect and transfer data from Google Sheets to BigQuery:

Step-1: Navigate to your BigQuery account: https://console.cloud.google.com/bigquery

Step-2: Click on the drop-down menu at the top:

Step-3: Click on the ‘New Project’ button:

Step-4: Name the project ‘Google Sheets’, rename the project id (by clicking on the ‘edit’ button) to something easily recognizable and then click on the ‘create’ button:

Step-5: Click on the ‘SELECT PROJECT’ link on the top right-hand side of your screen:

Step-6: Click on your project ID:

Step-7: Click on the ‘CREATE DATASET’ button on the top right-hand side of your screen:

Step-8: Name your data set (say ‘Google_Sheets_Dataset’) and then click on the ‘Create dataset’ button:

Step-9: Click on the name of your new dataset:

Step-10: Click on the create table button:

We are going to use this table for storing data from Google Sheets.

Step-11: Click on the ‘Create table from’ drop-down menu:

Step-12: Click on the ‘Drive’ option:

You should now see a screen like the one below:

Step-13: Navigate to the Google Sheets whose data you want to send to BigQuery and then copy the sheet URL:

Step-14: Paste the Google sheet URL in the text below ‘Select drive URL’:

Step-15: Set file format to ‘Google Sheet’:

Step-16: Enter the cell range to indicate which sheet and cell range in the Google Sheets to create a table from:

Note: If you entered an incorrect cell range then either you may get unexpected data when you query your table or your query may fail to execute. 

Step-17: Enter a table name (say ‘Traffic_Data_Table’):

Now we need to define a schema for our new table. 

You won’t be able to query your data table without defining the schema first. 

The schema that you define should be based on the data type of the data in your Google Sheets:

Step-18: Click on the ‘+Add field’ button:

Step-19: Enter the name of your field, set the data type and then click on the ‘+Add field’ button to add another field:

Step-20: Repeat step-19 as many times as required and then click on the ‘Create table’ button:

Step-21: Click on the name of your new data table:

Step-22: Click on the ‘QUERY TABLE’ button:

Step-23: Enter your SQL statement(s) and then click on the ‘Run’ button:

If you set up your table schema correctly then your query would run and you should be able to see query results from your Google sheets document:

If your table schema is not set up correctly or the data in your Google Sheets is not formatted correctly or you used an incorrect cell range while creating the table then your query could fail and you would see an error message like the one below:

Note: If you did not set up your table correctly then you would need to delete your table and create a new table:

Step-24: Click on the ‘SAVE RESULTS’ button to save your query results:

Step-25: Choose where to save the results data from the query from the drop-down menu

Let’s select ‘BigQuery table’:

Step-26: Enter the name of the data table where to want to store your query results and then click on the ‘Save’ button:

You should now see a new table listed under the data set ‘Google_Sheets_Dataset’:

Step-27: Click on the name of this data table:

Step-28: Click on the ‘Preview’ tab:

You should now be able to see your previous query results permanently stored as a data table in BigQuery:

Google Sheets to BigQuery

Other articles on Google Analytics BigQuery

  1. Advantages of using Google BigQuery for Google Analytics
  2. Cost of using BigQuery for Google Analytics
  3. Guide to BigQuery Cost optimization
  4. What is Google BigQuery Sandbox and how to use it
  5. Understanding the BigQuery User Interface
  6. Sending data from Google Analytics to BigQuery without 360
  7.  How to connect GA4 (Google Analytics 4) with BigQuery
  8. events_& events_intraday_ tables in BigQuery for GA4 (Google Analytics 4)
  9. Using Google Cloud pricing calculator for BigQuery
  10. How to access BigQuery Public Data Sets
  11. How to use Google Analytics sample dataset for BigQuery
  12. How to query Google Analytics data in BigQuery
  13. How to send data from Google Ads to BigQuery
  14. What is BigQuery Data Transfer Service & how it works.
  15. How to send data from Facebook ads to BigQuery
  16. How to send data from Google Search Console to BigQuery
  17. How to pull custom data from Google Analytics to BigQuery
  18. Best Supermetrics Alternative – Dataddo
  19. Google Analytics BigQuery Tutorial
  20. How to backfill Google Analytics data in BigQuery
  21. How to connect and export data from GA4 to BigQuery

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and Beyond
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!