Check google sheet for new update

Questions
,
1

Hi Team,

Is it possible to check changes from google sheet and update it also to mySQL database table:

  1. If row is deleted in gsheet will also delete in mySQL table.
  2. If row is updated in gsheet will also update in mySQL table.
  3. If row is inserted in gsheet will also inserted in mySQL table.

If that’s even possible. Urgent please. Thanks.

Sincerely,

OBI

2

Hey @daniel.o,

it is possible but it is not easy, we don’t currently have a trigger for Google sheets so a couple of options would be to use a google app script to post to a webhook that n8n picks up on any changes or on a schedule read the entire sheet and the database and work out what is missing / added using a couple of merge nodes.

This blog post might help with a bit of it: How to connect Google Sheets to MySQL – n8n Blog

3

The thing is we cannot use apps script since it cannot detect changes made by formula. We already tried it a couple of times.

4

Hey @daniel.o,

That sounds like the only solution would be the second approach.

5

And apps script has limits in usage, what we want to do is a realtime every minute checking and update from google sheet to mySQL database.

6

Hey @daniel.o,

That will make it trickier but you can run a schedule every minute, You would just need to make sure that the data you have can be processed and checked within that 60 second window to avoid any possible conflicts.

Does it have to be a Google Sheet or could something like Airtable or Baserow be an option?

7

What we want is whatever changes happens in google sheet will also be updated in mysql table. We need to check it probably every 10 minutes on a realtime basis.

8

image
image708×723 42.2 KB

Do you have a sample workflow for this?

9

What I have rather in mind is [cron - every 10 minutes] → [empty mySQL table] → [read all data from gsheet] → [map data from gsheet] → [insert all data to MySQL table]

Is this doable in n8n?

10

Hey @daniel.o

Yeah that would be possible, It is not much different from the second workflow in the link. You would just need to start with a MySQL query to delete your database.

11

image

Can you give me a sample working workflow for this?

12

Hi @daniel.o,

How far did you get with the previous link I shared with you?

13

Yes I was calculating output result if I try that one but what rather I have in mind is:

  1. First, read all ID column of a google sheet.
  2. Search each ID inside a mySQL table using mySQL query like SELECT * FROM db_table WHERE ID=‘Gsheet_Column_Values’
  3. If data found, update that row in mySQL using that rows data from gsheet.
  4. If not found, add new row to MySQL using that rows data from gsheet.

We want to run it in a cron setup every 10 minutes.

14

Hey @daniel.o,

That is fine, How did you get on with building out that workflow? In theory it would be just as you have written it so as a basic structure you would want to start with something like the below, You would just need to work out what If statement to use to work out if you have data or not.