Configure access to the document by the link

1. Open a Google Spreadsheet.
2. Open the share settings by clicking Share button.
3. Select the Anyone on the internet with this link can view option.
4. Copy the link.

IMPORTANT: make sure that you share the spreadsheet with the account used in GBQ.

Configure data export to a table

1. Open a project in GBQ and select an agency dataset.
2. Press the Create table button.

3. Set the following settings:

Source settings

Create table form

Select the Drive option in the drop-down list.

Select Drive URL

Here you should paste the link to the spreadsheet.

File format

Select the Google Spreadsheet option in the drop-down list.

Destination settings

Project

Select BROWSE and use search for a project option.

Dataset

Choose the dataset name matching your agency.

Table

Enter a table name here.

Schema settings

‍Here you can select Auto detect to generate schema automatically. However, be careful. Problems might occur if your data contain any artifacts, e.g., missed columns or strings at numeric column.

IMPORTANT: if you create a schema manually, you don’t need to know column names from the spreadsheet. Here you define how it will be named in GBQ from left to right (the first column here corresponds to the most left column in the spreadsheet and so on).

Advanced options

You can also select advanced options according to your original doc. Typically it’s necessary to set the Header rows to skip to 1. Otherwise, column names from the spreadsheet will be written as values to GBQ.

However, if there are some meta-information or empty rows at the top, then:

• Header rows to skip = Meta rows + Empty rows + Header rows

More detailed instruction is attached near each parameter.

4. Click the Create table button.

Schedule data extraction

The created table doesn’t contain any rows and loads data from the spreadsheet every time it’s queried. If the spreadsheet is large enough, processing the query might take some time. It’s possible to create the Scheduled query, which will run at defined time intervals, and the result will be written in the pre-defined table.

IMPORTANT: You need to inform us about the preferred update period (for example, you may ask to update the table on the 9th of every month. etc.).

Step 1. Write query

1. Write an SQL query that produces the result you want to save as a new table.

At the simplest situation it’s:

• SELECT * FROM `<project_name.agency_name.table_name>` where %DateField% is not null

IMPORTANT: there should be no LIMIT at the end of the query!

However, if data contains some artifacts, this is where you can implement necessary transformations.

2. Click the Schedule and select the Create new scheduled query in the drop-down list.

Step 2. Configure new scheduled query

Details and schedule

The name for a scheduled query might be scheduled_<table_name>

Schedule options

Repeats define the frequency of running this request.

Available options:

• Hourly
• Daily
• Weekly
• Monthly
• Custom
• On-demand

Click the Schedule start time to define running your query every day/week/month/hour or the Start now to run the query right now.

By default, it will be Daily. If it’s necessary to define a specific frequency, you might use Custom. More details here.

Destination for query results

1. Here you need to define the appropriate Project name and Dataset name.

2. By default, the Table name will be view_<table_name>, but you can use any other name if you prefer.

3. Leave the Destination table partitioning field blank.

4. The default value for the Destination table write preferences is Overwrite table. However, you can choose this depending on your requests.

You will be able to check and edit all scheduled query settings.

So if you need to make changes to an existing schedule, you need to go to the tab and select the desired schedule, find the required query and go to the Configuration section.