How to use Google Sheets document as a data source for BigQuery table

We know that many of our users prefer to use Google Sheets for collecting and processing marketing data. However, if you're using OWOX BI products, you'll need to integrate with Google BigQuery cloud storage. The question then becomes: how can you use data that has already been collected in Google Sheets with OWOX BI?

In this article, we'll outline step-by-step instructions for connecting your Google Sheet to a BigQuery table, which you can then leverage within OWOX BI Transformations.

Getting started requirements

• You are using the Google Cloud Platform and have an active project (how to set up GCP).
• In this active GCP Project, you have a BigQuery Admin or BigQuery Data Owner role (how to share access).
• The data you will work with is stored in Google Sheets and you can retrieve the Drive URI in format https://docs.google.com/spreadsheets/d/FILE_ID or https://drive.google.com/open?id=FILE_ID where FILE_ID is the alphanumeric ID for your Drive file.

Step 1. Choose a BigQuery project and dataset

1.1 In the Google Cloud console, open the BigQuery page.

1.2 In the Explorer panel, expand your project and select a dataset.

1.3. Expand the Actions option and click Open.

1.4. In the details panel, click Create table.

Step 2. Create a table

2.1 On the Create table page, in the Source section:

• For Create table from, select Drive.
• In the Select Drive URI field, enter the Drive URI. Note that wildcards are not supported for Drive URIs.
• For File format, select the format of your data: Sheets.

2.2. (Optional) In the Sheet range box, specify the sheet and cell range to query. You can specify a sheet name, or you can specify sheet_name!top_left_cell_id:bottom_right_cell_id for a cell range; for example, "Sheet1!A1:B20". If the Sheet range is not specified, the first sheet in the file is used.

2.3. On the Create table page, in the Destination section:

• For Dataset name, choose the appropriate dataset, and in the Table name field, enter the name of the table you're creating in BigQuery.
• Verify that Table type is set to External table.

2.4. Click Create table.

2.5. Select your account and then click Allow to give the BigQuery client tools access to Drive.

If you have any difficulties while creating a table, please refer to the official Google documentation.

2.6. After the table is successfully created, you can check its settings on the Details tab:

Step 3. Share BigQuery dataset with OWOX BI

Share your BigQuery dataset, which contains this table, to the OWOX BI service account, following this instruction.

Step 4. Share your Google Sheet document with OWOX BI

4.1. Share your Google Sheet document to the OWOX BI service account (you can copy it from the 'Shared datasets' page):

4.2. Your Google Sheet data is now available as a BigQuery table and can be leveraged within OWOX BI Transformations.