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.
1. Open a project in GBQ and select an agency dataset.
2. Press the Create table button.
3. Set the following settings:
Select the Drive option in the drop-down list.
Here you should paste the link to the spreadsheet.
Select the Google Spreadsheet option in the drop-down list.
Select BROWSE and use search for a project option.
Choose the dataset name matching your agency.
Enter a table name here.
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).
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:
More detailed instruction is attached near each parameter.
4. Click the Create table button.
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.).
1. Write an SQL query that produces the result you want to save as a new table.
At the simplest situation it’s:
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.
The name for a scheduled query might be scheduled_<table_name>
Repeats define the frequency of running this request.
Available options:
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.
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.