Create Bigquery Tables With Google Sheets and Apps Script
Creating BigQuery tables using the standard GUI can be a hassle. This article describes an alternative way to create BigQuery tables using the BigQuery Table builder sheet. This Google Spreadsheet automates the creation of the BigQuery tables, documents the tables and supports collaboration around the table schema design.
Setup and Configuration of the sheet
Basic knowledge of the Google Cloud platform, projects, enabling API’s and BigQuery is assumed. This sheet will only work with a project that has the BigQuery API enabled. In case you get stuck, leave a comment below and I will assist with the setup of the sheet.
First create a copy of the 2019 BigQuery Table builder sheet. A new menu item “BigQuery menu” will appear. This might take up top 30 seconds. The create table menu option will activate the creation of a BigQuery table. The first time you run this action you need to authorize the script that will create the table.
Next add the project id and datasets that you want to use in the config tab
Each tab in the spreadsheet represents a table. For multiple tables, create a copy of an existing tab and replace the field definitions with the new ones.
In column B select the correct project id, dataset and the name of the table. The description field is optional, it will be copied to the description field of the table. For each field fill in the name, data type and type and optionally the description. Use the dot notation for repeated fields.
Once the sheet is set up and the table is defined the tables can be created. To prevent deleting existing tables the sheet will never overwrite a table, the sheet will throw an error message if the table already exists.
How it started
I created this sheet to speed up the process of creating the BigQuery tables. It started as a simple sheet with a few standard functions that produced the JSON schema. This schema could be copied into the GUI to create the table. This worked fine and I started using the sheet for all the BigQuery tables. A bonus was the documentation and creation of the tables in one place.
Next step was sharing the sheet in the team. Google sheets is great for collaboration and provided me a way to share the table designs with other team members. The debate about field names was moved to the comments and everybody was using the same version.
The added value of the sheet was clear and I started looking for some optimisations. Automating the creation of the table, support for records and repeated fields would be nice.
How the sheet works
When you think automation in G Suite then you will end up at Google Apps Script. From the website:
Apps Script is a rapid application development platform that makes it fast and easy to create business applications that integrate with G Suite.
I used apps script to create a custom function in the sheet that generates the JSON schema. A custom menu is added to the sheet that runs the function that creates the table. The function uses Advanced Google services in Apps Script to call the BigQuery API.
The custom menu is added by the onOpen function.
This function is called when the sheet loads and it will add the menu item to the menu bar.
The menu item calls the createTable function. The *createTable *function collects the data from the sheets and calls the BigQuery API. By design it will fail if the table already exists. This is to prevent replacing an existing table with data.
The bqShema function is used to create the BigQuery schema. The function uses recursion to create record in the bq schema.