Apps Script tutorial: Upload to a database (Sheets => BigQuery)

Course 5 of 5 Apps Script + Sheets tutorials

LEVEL: Advanced to build, but anyone can use this sample.

Introduction

It’s common for financial spreadsheets that help us with pivotal operations to reach limitations when they begin to take the place of a database. Performance issues begin to be noticed such as speed or in usability such as search. This is where Google’s BigQuery database comes in, especially to help centralize data from multiple sources or process very large volumes of rows and columns when data exceeds spreadsheet quotas. It is incredibly fast, and organizations love to build reporting prototypes especially with Google’s free Data Studio dashboards because of BigQuery’s very large free monthly tier (10GB of data).

In this article we will review how to push data from multiple spreadsheets simultaneously into a database using an Apps Script.

Table of contents

A. 6 min video walk through

B. Get your sheet

C. Organize sheet and enter data

• Login to BigQuery
• Create a new dataset in BigQuery
• Prepare your Google Sheet
• Run script to upload data to database
• Verify data has been uploaded

D. How the code works

A. (Optional) 6 min video walk through

B. Get your sheet

• Make a copy of this spreadsheet.

C. Organize sheet and enter data

Login to BigQuery

• BigQuery is hosted on the Google Cloud Platform. There’s a free 30 day trial if you are new to it. Visit BigQuery and either login to your account or use the sandbox (if it’s your first time and are logged into a Google or Gmail account).

Create a new dataset in BigQuery

• If it’s your first time you will have one table pre-created for you below Resources (bottom left sidebar), select it, and on the bottom right click CREATE DATASET.
• If you already have a BigQuery account, select a dataset of your choice and then create a dataset.

• A side panel will appear, give your dataset the name sheets_upload and leave the remaining values to their default. Then choose Create dataset.

• You will now see your new dataset below on the left.

2) Prepare your Google Sheet

• Visit your spreadsheet, you will update column B with your BigQuery project in a bit.
• To do so, from the BigQuery console, copy your project ID which can be found by selecting the drop-down at the top

Google Cloud project drop down

• Then copy your Project ID. If there is more than one, pick the one that you created the sheets_upload dataset under.

• Visit your sheet and paste your project ID value under column B called Project ID for all 3 rows.

• Leave column A, C, D, and E the same. You want to use the sheets and names as they are presently listed for this first test.

Note:

• Ensure column D does not have any spaces, replace spaces with underscores instead.
• Column E called ‘Append?’ has a drop-down option of FALSE or TRUE. If you select TRUE, this helps the script know that you are not creating a new table, and want to append or add data to an existing table. Cell E4 for example is marked as TRUE because we want to add new data to the CA financial data table which is already created in E3.

Important: You can overwrite an existing table of data by marking a row as FALSE in column E and using the same table ID of an existing dataset (false means it is not meant to be appended, but created net new).

3) Run script to upload data to database

• Locate the Sheets to BigQuery menu at the top of your Google Sheet, and select Upload.

• In a few seconds the script will ask you to authorize running it on your behalf. Select Continue.

• Choose your email and accept.

Note if you get the following message that your app is not verified, click Advanced and then the bottom link that says Go to <name of sheet> to continue.

• In a few seconds, the upload will be complete and column F of your sheet called Status will be populated with the timestamp of the upload for verification purposes.

IMPORTANT: If you do not see the Status field populate (column F), you may need to click the Sheets to BigQuery > Upload custom menu again, and that should do it!

• To ensure the upload worked, let’s visit BigQuery (refresh browser).
• Locate dataset under the resources section in the bottom left by selecting the triangle to expand the project ID, and then the dataset sheets_upload.
• You should now find two new datasets called CA_financial_data and financial_data like this:

4) Verify data has been uploaded

• By selecting a table’s name in BigQuery under the dataset sheets_upload, and then choosing details, you can see the total number of rows for example.
• In this case if you select CA_financial_data you should have 12 total rows. This is because we uploaded 6 rows from a sheet called california, and then appended another 6 from another tab called new california.

D) How the code works

• From your sheet visit the code by locating the Tools menu option > Script editor.

• There are two files appsscript.json and Code.js

• In appsscript.json, note that BigQuery’s API is enabled and displayed in this file by having visited Resources > Advanced Google Services > (toggle on) BigQuery API

• Then toggle Code.js. At the top is the logic for displaying the custom menu option called Sheets to BigQuery.

• The following constants locate the position of columns in the sheet.

• The `.forEach()` method processes each row and upon completion, it updates the Status column with a timestamp.

Note: if there is an error with the upload, it will display it in the status field.

• The function sheetToBigQuery uploads the sheet data into BigQuery, by passing all the necessary parameters such as the URL of the sheet of where the data resides, project ID, dataset and table name, as well as confirmation if a sheet should be a new table (mark FALSE) or have its data appended to an existing dataset (mark TRUE) in the Append column.

Note: users must have at least edit permissions to the desired BigQuery dataset to write values to.

Caution: if the table name already exists in the same dataset and the append column is listed as False, it will override the dataset.

• loadJob declares the BigQuery’s load job configuration.

• Since BigQuery can only load files, we convert the array of rows into one string formatted as a Comma Separated Values (CSV) file. Bigquery.Jobs.insert executes the loadJob declared before.
• Next, using try… catch, any errors that arise in the upload process are displayed in the status column along with a timestamp.

• By using Logger.log we specify what to store in the logs. We pasted the GCP the link for visiting the jobs history, which saves a lot of time in locating that correct page directly in the logs.

• The function create DatasetIfDoesntExist() performs another try… catch to see if the dataset exists and you have permissions to create it, if they both do, then it will work correctly. Otherwise, if the dataset is not created in BigQuery, the function will give create it.

• Finally the openSheetByURL() function allows one to granularly specify what tab in a spreadsheet to upload and throws an error if that tab does not exist to let the user know, as it is common for a user to make modifications on individual sheet tabs within a spreadsheet.