Coefficient’s Export to BigQuery feature allows you to seamlessly transfer data from your spreadsheet to BigQuery, making it easier to manage and analyze large datasets. Whether you need to update existing tables or create new ones, this tool automates the process, eliminating manual uploads and complex scripts. With just a few clicks, you can ensure your data is always up to date in BigQuery, enabling faster insights and more efficient decision-making.
Schedule an Export to BigQuery
What is Export to BigQuery?
Export to BigQuery is a very powerful tool in Coefficient. This feature allows you to update data from Google Sheets/Excel and then uses Coefficient to push (or write back) those updated data directly into your BigQuery instance.
How to Export to BigQuery?
ℹ️ NOTE: We recommend creating a BigQuery import of your data into Google Sheets/Excel using Coefficient before performing the Export to BigQuery. This allows us to detect/map certain fields automatically. (This is not required but HIGHLY recommended.)
Example: Insert new records into the test_city database in BigQuery using an existing import.
|
1. Click on the “Export to...” button from the sidebar. |
2. Select the data source to which you would like to export your data. (ie, BigQuery) |
3. Select the Tab and Header row (the Header row is not the Coefficient header, but the row with the BigQUery field headers), then hit "Next." |
ℹ️ NOTE: Coefficient will automatically select the sheet you have open as the Tab for this Export. If it is a different tab, you will need to click the drop-down and select it.
|
4. Destination Settings: Select the BigQuery Table you are exporting the data to and the Action you would like to take, then hit "Next". (See the screenshots for details on each Action)
|
|
|
|
5. Field Mappings: Depending on the Action you are taking, specific fields may be required to be mapped for this export. Click "Save" to proceed. Example: An Update to the records on your BigQuery table requires you to map the ID field/column on your export sheet. Coefficient will automatically map the fields for you if the data in the sheet came from an import from BigQuery using Coefficient. ℹ️ NOTE: If the data in the sheet was not generated from a Coefficient import, you will need to map the data manually. |
|
6. Advanced Settings: Column for Results: Each time you Export to BigQuery, Coefficient will write information back to your sheet related to that update. Coefficient will try to guess what the first empty column is in your sheet and suggest that as the column to use to populate this information. We will write 3 columns to the sheet, (1) the Record ID (2) Result (OK, ERROR, etc.), and (3) the date/time stamp that the record was updated last using Coefficient.
Include export button on sheet: Coefficient adds an "Export" button on your sheet to quickly access your export configuration in the sidebar. This works if your data is generated from a Coefficient import. |
|
|
7. Confirm your settings, then click "Export". |
8. Select the row(s) on your sheet to Export to BigQuery. You can select (highlight) specific rows OR update all the rows in your sheet. |
9. Coefficient will scan the data on your sheet and display the number of rows to be updated or skipped. Click on "Preview changes". |
10. The "Export Preview" window opens, displaying the fields that will be updated in your BigQuery table (highlighted in blue). If there are potential errors with your export, they will be visible here, allowing you to make necessary adjustments to your data. When you have confirmed all the changes/updates that will be pushed to BigQuery, click the "Insert 4 rows in BigQuery" button.
ℹ️ NOTE: Toggle the buttons at the top (enabled by default) to either "Show only the changed rows" or "Show only the mapped columns" for better visibility.
ℹ️ PRO TIP: This action CANNOT be undone, so ensure all your settings and updates are correct before proceeding.
11. When the Export to BigQuery is complete (and successful), you can see the number of rows exported/skipped. Congratulations on your first Export to BigQuery. 🎉
Re-Use an Existing Mapping
If you have used the "Export to BigQuery" function before, you will see the previous Export Mappings in the Coefficient sidebar underneath your Import list. This list will allow you to reuse a previous mapping. We will go through the process of using both the sidebar and the button below.
1. Export to BigQuery a second time will reveal the “Export” button in the Coefficient Header and the previous mappings in the sidebar.
ℹ️ NOTE: The "Export" button will show if you have enabled the "Include export button on sheet" setting and if your data came from a Coefficient import.
2. When clicking on the “Export” on-sheet button, the previous mapping(s) related to the sheet that you are currently on will appear in the sidebar. Select your desired export action (eg. Update: test_city).
Make sure to review the mappings before performing the Export to BigQuery again. (From here, you can view the run history and settings) then proceed with the typical “Export to…” process.
Export to BigQuery Actions
Insert
Creates a NEW ID and inserts data into a new record in your BigQuery database.
- Depending on how your database is set up, you MAY skip mapping the ID column on your sheet and leave the value blank. If you choose not to map the ID column, the ID value assigned to new records usually will take the last record and increment the new ID by +1 (depending on the database configuration). The ID column is still required on the sheet even if you are not mapping it.
Update
Pushes updates to an existing field/record on your database.
- The record/s MUST have an ID field mapped to the primary key column on your database.
Upsert
An Upsert action is a combination of the Insert and Update. It allows you to either update existing records or insert new ones based on whether the record already exists.
- The record MUST have a Primary Key mapped to it. If the row with a matching primary key does not exist, the new row/record will be inserted.
- Depending on how your BigQuery instance is set up, you MAY skip mapping the ID column on your sheet and leave the value blank. If you choose not to map the ID column, the ID value assigned to new records will usually take the last record and increment the new ID by +1 (depending on your configuration). The ID column is still required on the sheet even if you are not mapping it.
- If "Export Empty Cells" is enabled and the cell value is empty, the corresponding entry in the database will be set to null.
- Before proceeding with the upsert action, you'll see the number of rows updated or inserted into your database records (see the screenshot below).
Delete
This action will delete the ENTIRE record from your database. Be careful with this one, as it cannot be undone.
- The record MUST have a record ID mapped to your primary key column from your database.
Schedule an Export to BigQuery
If you work with data frequently, you may find yourself performing repetitive tasks. Our "Export to BigQuery" feature allows you to schedule pushing your data back to your database at a specific time. This feature can save you a significant amount of time by automating the export process. 😎
See how you can schedule an Export to BigQuery here.
FAQ for Export to BigQuery
What data types are supported for the Export to BigQuery feature?
Here's the list of all supported and unsupported data types for the Export to BigQuery feature:
|
Supported Data Types:
|
Unsupported Data Types:
|
How can I use a shared connection my colleague created to Export to BigQuery?
Shared Connections are not supported with the Export to BigQuery feature; this is a security measure the team put in place to prevent unauthorized edits/deletions of data/records in your database by users who do not currently have these levels of permissions with their own access.
The easiest way to update/undo a mistake is to update your Coefficient sheet and perform an Export to BigQuery (again) to "undo" the mistake. You are also welcome to fix it in the BigQuery UI.
How do you rename an export mapping so you can recognize it and reuse it later?
| In the Coefficient sidebar, you will see all of your imports; if you scroll down, you will see a section called Exports. Locate the Export you would like to rename, hover over the name with your mouse, and click on the 3-vertical dots that appear. Select “Rename” from the list. Update the name of your Export, and press “Enter” to save. |
How do you view an Export’s Run History?
In the Coefficient sidebar, you will see all of your imports, and if you scroll down, you will see a section called “Export". Locate the Export you would like to see the Run History for and click on it to open up the details. You should see a “See Full Run History” link when you collapse the last export information.
You will be redirected to a page dashboard where you can download the full log in CSV format (see the attached screenshots below).