Export to SQL DB

Section header image mobile

If your team uses a database (PostgreSQL or MySQL) and it is your single source of truth keeping it up to date from various systems within your tech stack can be time-consuming.

Updating the data within your Google Sheet and pushing those updates directly to your database using the “Export to…” feature can now make bulk updates to fields/tables within your database a breeze.💨 With actions, Update, Insert, and Delete, records can be managed directly from Google Sheets without having to write additional queries.

What is Export to SQL DB?

How to Export to SQL DB?

Re-Use an Existing Mapping

Export to SQL DB Actions

Update

Insert

Delete

Schedule an Export to SQL DB

FAQs for Export to SQL DB

What is Export to SQL DB?

Export to SQL DB is a Coefficient feature that allows you to connect your database, pull your data into Google Sheets, update the values, and push those updates back to your database without the hassle of writing complicated scripts.

ℹ️ NOTE: You need to use your own database connection with Coefficient to push data from Google Sheets into your database using Coefficient. Shared data sources are NOT supported with this feature due to security reasons.

How to Export to SQL DB?

ℹ️ NOTE: We recommend doing an import of your data from your database into Google Sheets prior to using the Export to SQL Db. This allows us to automatically detect/map certain fields, rather than you having to map every field manually.
(Example: Insert new record to MySQL into the film table)

1. Open the Coefficient sidebar and click on the “Export to...” button.

Screenshot 2023-11-08 at 7.29.02 PM.png

2. Select the data source you would like to export your data to. (ie: MySQL)

Untitled (9).png

3. Select the Tab in your Workbook that contains the data you want and the Header row (the Header row is not the Coefficient header, but the row that has the database field headers in it) then hit "Next".

Screenshot 2023-11-08 at 11.34.58 PM.png

ℹ️ 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. Designate the Table in your database that you are Inserting the record to. Make sure you choose the right Action from the list (ie: Insert).

Untitled (10).png

5. Complete the Field Mappings for this Export. Coefficient REQUIRES a Primary Key or ID field to be mapped for certain Actions (UPDATE and DELETE) so that the updates made are to the correct records in your database.

Untitled (11).png

ℹ️  Primary Keys:

UPDATE and DELETE Actions - Primary Keys may be required for your Export to SQL DB depending on how your primary key column(s) are set up in your database. 

INSERT Actions - This MAY or MAY NOT be required for your Export to SQL DB. If the primary key is set to auto-populate in your database, you can skip the mapping of the ID column, as the database SHOULD automatically create/assign a value for these newly inserted records. If you choose not to map the ID column, the ID value assigned to new records will take the last record and increment the new ID by +1.

6. Advanced Settings:

Column for Results: Each time you Export to SQL DB, 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.

Export Empty Cells on Update: Enabling this feature will allow you to remove/clear out existing values in your database in bulk using Coefficient.

image (89).png

 

image (90).png

7. Confirm your settings.

Untitled (12).png

8. Select the row(s) on your sheet that you need to Export to SQL DB. You can select (highlight) specific rows OR update all the rows in your sheet.

Untitled (13).png

9. Confirm the row(s) to update.

ℹ️ NOTE: Confirm your changes BEFORE you commit to these changes. These actions cannot be undone!

Untitled (14).png Untitled (15).png Untitled (16).png

10. Records that have been updated will have the export status and the date/time stamp of the update in your Sheet. Untitled (17).png

Re-Use an Existing Mapping 

If you have used the Export to function before then you will see the previous Export Mappings in the Coefficient sidebar located 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. Using the Export to SQL DB feature a second time will reveal the “Export” button in the Coefficient Header as well as the previous mappings in the sidebar.

 ℹ️ NOTE: The "Export" button will show if you have the "Include export button on sheet" setting enabled and if your data came from a Coefficient import.Screenshot 2022-11-30 at 9.13.40 AM.png

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. Screenshot 2022-11-30 at 9.16.00 AM.png3. Make sure to review the mappings before performing the Export to SQL DB again. (From here you can view the run history and settings) then proceed with the typical “Export to…” process.

Screenshot 2022-11-30 at 9.19.32 AM.png Screenshot 2022-11-30 at 9.19.45 AM.png

Export to SQL DB Actions

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.

Insert

Creates a NEW ID and inserts data into a new record in your 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.

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 SQL DB

If you work with data frequently, you may find yourself performing repetitive tasks. Our "Export to SQL DB" 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 SQL DB here.

 

FAQs for Export to SQL

What databases are supported with this feature?

At this time the Export to SQL DB feature supports PostgreSQL and MySQL.

Why can’t I use a shared connection with this feature?

For additional security of your data, Coefficient does not support shared connections in the Export to SQL DB feature. This security measure will help to avoid the unauthorized edits/deletions of records in your database by users who currently do not have permission to perform these actions.

Why are some columns greyed out when I try to map them or update them?

In database tables, there are sub-permissions on the individual columns. Columns may be read-only, in which case they will be greyed out and not mappable.

I’m having issues with my SQL DB Writeback, what should I do?

  • Confirm that you have the correct permissions in your database to use the Export to SQL DB feature. (You need to have Read/Write permissions) If you are not sure about your permissions, please contact your Database Admin to confirm.
  • Check your settings; field mappings, primary key, ID, etc.
  • Coefficient supports Export to SQL DB actions to single tables. Sheets that contain JOINED data from 1+ tables are not supported with this feature.

Was this article helpful? 0 out of 0 found this helpful