Sync data from Google Sheets to SQL

The database user for this source will need write access to the database. If you need to add or insert rows, consider using 📋 Forms instead.

You can sync data from a Google Sheet back to your database using the ➕ Google Sheets Addon. To get started, install the addon. Updates are triggered as soon as an edit in the Sheet is completed, so the updates write back to your database in near-real time.

  1. Create a new SQL block with ⚙ Parameters. It’s best to give the parameter the same name as the column, for example:

    update dummy.users
set plan = '{{plan}}'
where email = '{{email}}'

  2. Add the form tag.

    SQL tags in SeekWell

  3. Launch the addon.

  4. Select the block created in step 1 (you should see "Form" to the right of it).

    Select block created in Step 1

  5. Select "Set up a sync" under the Run button.

    Set up a sync

  6. Add the range that your data is in, for example, "Sheet1!A:G". You need to include the cell range using A1 notation. Your table in Sheets needs to have headers / column names.

    You can only have one sync per Sheet, but you can have multiple syncs in the same spreadsheet.

    Enter range under MySQL in A1 notation

  7. If the table in Sheets has different column names than your parameters, specify the names under each parameter name.

  8. Select Save.

You can now test your sync by updating a value in the Sheet and confirming the write back to your database. Please let us know if you run into any issues with this.

Test sync

Tips and tricks

Upserts

In MySQL, Upserts (updating and inserting data at the same time) can be performed with "On Duplicate Key Update" after the Insert statement. Your table must already have a unique index. When the row being inserted has a key that already exists in the table, the specified fields will be updated for that key in the database.

In this example, "id" is the unique index. When a certain id already exists in the table, its title, author and year_published will be updated instead of inserting a new row.

INSERT INTO books
    (id, title, author, year_published)
VALUES
    ({{id}}, '{{title}}', '{{author}}', '{{year_published}}')
ON DUPLICATE KEY UPDATE
    title = '{{title}}',
    author = '{{author}}',
    year_published = '{{year_published}}';

Handling null data

Null numbers can be cast as Signed Integers in MySQL to avoid errors:

CAST('{{your_param}}' AS SIGNED INTEGER)
Was this page helpful?
×