Configuring Google Apps Script BigQuery Connection Simplified 101: Critical Aspects

on Data Warehouse, Google BigQuery • March 22nd, 2022 • Write for Hevo

apps script bigquery: FI

In today’s digital era, Google is dominating the world with its unique features and end-to-end application and services platform. Google’s various products have in-built libraries and APIs to interconnect various products and maximize their utility as per requirement. 

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 connection with Google BigQuery. 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.

Table of Contents

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.

Simplify the Google BigQuery ETL & Analysis with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Data Sources (including 40+ Free Sources) to a Data Warehouse/Destination of your choice such as Google BigQuery in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector.

Get Started with Hevo for Free

Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Connectors: Hevo supports 100+ Integrations to SaaS platforms, Files, Databases, BI tools such as Tableau, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.  
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources) that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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.
apps script bigquery: apps script code editor
Image Source: Self

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

apps script bigquery: bigquery dashboard
Image Source: Self
  • 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.
apps script bigquery: Creating Dataset under the BigQuery project
Image Source: Self
  • Step 4: Dataset ‘new_sheet’ will be created under the project ‘sacred-armor-202709’.
apps script bigquery: Dataset created under Project
Image Source: Self

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

  • Step 1: First, you need a Google Sheet with data. Click here for a sample Google Sheet you have created by the name ‘NewSheet’.
apps script bigquery: Create Google Sheet with Data
Image Source: Self

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.
apps script bigquery: Apps Script Code Editor
Image Source: Self
  • 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.
apps script bigquery: Set up Bigquery API Connection
Image Source: Self
  • Step 2: BigQuery will be added below the Services tab as shown in the below image:
apps script bigquery: Set up Bigquery API Connection established
Image Source: Self

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. 
apps script bigquery: upload code to bigquery
Image Source: Self
  • Step 2: In a few seconds, it will ask for authorization. Click on Review Permissions.
apps script bigquery: upload code to bigquery review permissions
Image Source: Self
  • 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)’.
apps script bigquery: upload code to bigquery google verification
Image Source: Self
  • Step 4: Click on Allow to give Apps Script the required permission. This will make your script run.
apps script bigquery: upload code to bigquery google account access
Image Source: Self
  • Step 5: See the execution log for errors or success messages. From the below image you can see the execution is completed successfully.
apps script bigquery: upload code to bigquery execution completed
Image Source: Self

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.
apps script bigquery: check BigQuery Dashboard for Results
Image Source: Self
  • 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 100+ Data Sources (including 40+ Free 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.

No-code Data Pipeline for Google BigQuery