❓
Knowledge Base
🔄

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 / insert rows, consider using a
📋
Forms
 instead

You can sync data from a Google Sheet back to your database using the

, 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, e.g.
    2. update dummy.users 
set plan = '{{plan}}' 
where email = '{{email}}'
  2. Add the form tag
    3. image
  3. Launch the addon
  4. Select the block created in step #1 (you should see "Form" to the right of it)
    5. image
  5. Click "Set up a sync" under the Run button
    6. image
  6. Add the range that your data is in, e.g. "Sheet1!A:G". You need to include the cell range using A1 notation. Your table in sheets needs to have headers / column names
    7. 💡
    You can only have one sync per sheet, but you can have multiple syncs in the same spreadsheet.
    image
  7. If the table in Sheets has different column names then your parameters, specify the names under each parameter name.
  8. Click Save
  9. Done! 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.

image

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 casted as Signed Integers in MySQL to avoid errors:

CAST('{{your_param}}' AS SIGNED INTEGER)