Configuring Google Apps Script BigQuery Connection Simplified 101: Critical Aspects

 Google Apps Script offers a cloud-based scripting platform that can integrate with Google workspace applications easily. Google Apps Script has an in-built Google BigQuery API which allows seamless Apps Script BigQuery integration. BigQuery API gives users the ability to manage their BigQuery projects using Apps script. Users can easily connect, upload new data, and execute queries on BigQuery using Apps Script.

This article talks about the easy steps that are involved in Configuring Google Apps Script BigQuery Connection and gives a brief introduction of Google Apps Script and Google BigQuery.

What is Google Apps Script?

Google Apps Script is a cloud-based scripting platform developed by Google and launched in August 2009. It is widely used for extending the functionality of various Google Apps and for building lightweight cloud-based application development. In simple terms, users write small programs with Apps Script to extend the standard features of Google Workspace Apps. Apps Script fills the gaps in project workflows effortlessly.

Apps script is a code editor window, no installation is required. Codes can be easily written in modern JavaScript and various Google Workspace applications like Gmail, Calendar, Drive, BigQuery can accessed easily. Apps Script “provides simple ways to automate tasks across Google products and third-party services,” according to Google.

Features of Google Apps Script

• Apps Script is a rapid and versatile application development platform.
• There is no installation required. The code editor is available right in the browser.
• Tasks can be easily automated with Apps Script.
• With Apps script, users can add custom menus, dialogues, and sidebars to Google Docs, Google Sheets, and Forms.
• You can write using Apps Scripts write custom functions and run macros for Google Sheets.
• Users can build add-ons with Apps Script and publish them to the Google Workspace Marketplace.
• Web apps(either standalone or embedded) can easily be published with Apps Script in Google Sites.
• Apps Scripts can easily interact with various Google services like AdSense, Analytics, Calendar, Google Drive, Gmail, BigQuery, and Maps.

Getting Started With Google Apps Script Code Editor

• Step 1: Go to Google Apps Script link from here.
• Step 2: Click on New Project
• Step 3: The code editor window will open.

What is Google BigQuery?

Google BigQuery is a highly scalable, serverless, and multi-cloud data warehouse that uses a build-in query engine. It is a highly scalable serverless, fully-featured, fully manageable data warehouse that enables scalable analysis over petabytes of data. It is developed by Google, launched on 19th May 2010. It is designed such that it uses the processing power of Google’s infrastructure that makes a single SQL query to analyze petabytes of data in seconds.

BigQuery is also called SQL-based Data Warehouse as a Service (DWaaS) with zero infrastructure management. It is a serverless warehouse that does not require any upfront hardware provisioning or management.  BigQuery runs SQL Queries and all requests are to be authenticated. Google provides a complete package to their users with big data loading features on Google Cloud Storage and connectivity with various Google apps like Apps Script. Google BigQuery has many built-in features like machine learning and AI capabilities, geospatial analysis, and business intelligence. 

Features of Google BigQuery

• BigQuery is a serverless warehouse with zero infrastructure management.
• BigQuery offers infinite storage with high performance in analyzing petabyte-sized datasets in seconds. 
• Query languages like SQL allow users to query data through BigQuery.
• The BigQuery can analyze big data across multiple cloud platforms with BigQuery Omni.
• BigQuery offers built Machine Learning and Artificial Intelligence capabilities.
• Real-time analytics is possible with BigQuery. It integrates real-time data quickly and efficiently and makes analysis available for business.
• Data Visualization is available in a variety of graphic formats.
• BigQuery provides information about location and mapping using BigQuery Geographic Information Systems (GIS).

Getting Started With Google BigQuery Dashboard

• Step 1: BigQuery is hosted on the Google Cloud Platform. Login to BigQuery using Google or Gmail account.
• Step 2: One project is by default created in BigQuery. In the above screenshot project name is ‘sacred-armor-202709’. 
• Step 3: Create a new dataset under project ‘sacred-armor-202709’ with the name ‘new_sheet’ as shown below.

• Step 4: Dataset ‘new_sheet’ will be created under the project ‘sacred-armor-202709’.

Configuring Google Apps Script BigQuery Connection

The target is to configure Google Apps Script BigQuery connection and upload data from Google Sheets to Bigquery using Apps Script.

• Configuring Google Apps Script BigQuery Connection: Create Google Sheet with Data
• Configuring Google Apps Script BigQuery Connection: Open Apps Script Code Editor
• Configuring Google Apps Script BigQuery Connection: Set up BigQuery API Connection
• Configuring Google Apps Script BigQuery Connection: Add code to connect to BigQuery
• Configuring Google Apps Script BigQuery Connection: Run Script to upload data to BigQuery
• Configuring Google Apps Script BigQuery Connection: Check BigQuery Dashboard for Results

Configuring Google Apps Script BigQuery Connection: Create Google Sheet with Data

• Step 1: First, you need a Google Sheet with data. Click here for a sample Google Sheet you have created by the name ‘NewSheet’.

Configuring Google AppsScript BigQuery Connection: Open Apps Script Code Editor

• Step 1: In Google Sheet click Extensions → Apps Script to open the Apps Script code editor window.

• Step 2: Name the Apps Script to ‘Load Data to BigQuery’.

Configuring Google Apps Script BigQuery Connection: Set up BigQuery API Connection

• Step 1: In the Apps Script code editor window, click on Services then Select BigQuery API and click Add.

• Step 2: BigQuery will be added below the Services tab as shown in the below image:

Configuring Google Apps Script BigQuery Connection: Add Code to connect to BigQuery

• Step 1: Add function upload to Code.gs as shown below.

function upload() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var project_id = 'sacred-armor-202709'
  var data_set_id = 'new_sheet';
  var table_id = 'NewSheet_data';
  var range = sheet.getRange('A1:A8');
  var writeDisposition = 'WRITE_EMPTY';
  var has_header = true;
  var schema_bq = 'automatic'; 

upload_to_BigQ(range,project_id,data_set_id,table_id,writeDisposition,has_header,schema_bq) 
}

• Step 2: In the above code:
  • project_id is the Project Id from the BigQuery Dashboard.
  • data_set_id is the Dataset Id created under the project.
  • table_id is the name given to the table in which data will be uploaded.
  • the range is the range of the data to be uploaded.
  • writeDisposition means the writing options: WRITE_EMPTY, WRITE_TRUNCATE or WRITE_APPEND.
  • has_header indicates if the first row in the sheet is a header(true) or not(false).
  • schema_bq defines the table structure; takes the value ‘automatic‘ to detect the structure on its own.

• Step 3: upload() function calls to another function upload_to_BigQ. This function takes the range in the spreadsheet as input, uploads it to the BigQuery. For detailed code click here.

Configuring Google Apps Script BigQuery Connection: Run Script to upload data to BigQuery

• Step 1: Select the function upload() and run the code. 

• Step 2: In a few seconds, it will ask for authorization. Click on Review Permissions.

• Step 3: Choose your Google account to continue. Google hasn’t verified this app message will appear. Click on advanced and then click on ‘Go to Load Data to BigQuery (unsafe)’.

• Step 4: Click on Allow to give Apps Script the required permission. This will make your script run.

• Step 5: See the execution log for errors or success messages. From the below image you can see the execution is completed successfully.

Configuring Google Apps Script BigQuery Connection: Check BigQuery Dashboard for Results

• Step 1: Go to the BigQuery Dashboard Window where the ‘new_sheet’ dataset has been created. Refresh the window and Check the details of the table created under the dataset.

• Step 2: From the above snapshot, it is verified that the ‘NewSheet_data’ table is created and data is uploaded from Google Sheet successfully.

Conclusion

Google Cloud Platform offers multiple services in a single platform. This makes the user’s job easy. From the above steps, you can see it is very easy to Configure Google Apps Script BigQuery Connection and to upload data to BigQuery via Apps Script.

For a hands-on experience, follow each step sequentially and learn to connect Google Apps Script with BigQuery seamlessly.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 150+ Data Sources allows you to not only export data from your desired data sources & load it to the destination of your choice such as Google BigQuery but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.