Configuring Google Apps Script BigQuery Connection Simplified 101: Critical Aspects

Google Apps Script offers a cloud-based scripting platform that can easily integrate with Google Workspace applications. It has an in-built Google BigQuery API that allows seamless Apps Script BigQuery integration. The 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 discusses the easy steps involved in Configuring Google Apps Script BigQuery Connection and gives a brief introduction to 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 to extend various Google Apps’ functionality and build 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 effortlessly fills the gaps in project workflows.

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.
• 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, multi-cloud data warehouse with a built-in query engine. It is a fully featured, fully manageable data warehouse that enables scalable analysis of petabytes of data. Google developed BigQuery and launched it on May 19, 2010. It is designed to use the processing power of Google’s infrastructure and makes a single SQL query that analyzes petabytes of data in seconds.

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: Login to BigQuery using a Google or Gmail account.
• Step 2: One project is, by default, created in BigQuery.

• Step 3: Create or upload a new dataset under the project.

• Step 4: The dataset will be created under the project.

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.

• Step 1: Create Google Sheet with Data
• Step 2: Open Apps Script Code Editor
• Step 3: Set up BigQuery API Connection
• Step 4: Add code to connect to BigQuery
• Step 5: Run Script to upload data to BigQuery
• Step 6: Check BigQuery Dashboard for Results

Step 1: Create Google Sheet with Data

• Step 1.1: First, you need a Google Sheet with data. A sample Google Sheet is provided by the name ‘NewSheet.’

Step 2: Open Apps Script Code Editor

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

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

Step 3: Set up BigQuery API Connection

• Step 3.1: In the Apps Script code editor window, click on Services, select BigQuery API, and click Add.

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

Step 4: Add Code to connect to BigQuery

• Step 4.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 4.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 4.3: The upload() function calls another function, upload_to_BigQ. This function takes the range in the spreadsheet as input and uploads it to the BigQuery. Find the detailed code for your reference.

Step 5: Run Script to upload data to BigQuery

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

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

• Step 5.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 5.4: Click on Allow to give Apps Script the required permission. This will make your script run.

• Step 5.5: Check the execution log for errors or success messages. The execution is completed successfully, as shown in the image below.

Step 6: Check BigQuery Dashboard for Results

• Step 6.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 6.2: From the above snapshot, it is verified that the ‘NewSheet_data’ table is created and data is uploaded from Google Sheets successfully.

Conclusion

• Google Cloud Platform offers multiple services in a single platform, making the user’s job easy. From the above steps, you can see that it is very easy to Configure Google Apps Script BigQuery Connection and 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.

Want to take Hevo for a spin? Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions