Hi I have a problem with filtering data. I want the data retrieved from MySQL to be:
- if they exist in Google Sheets to be updated
- if it does not exist to be added to a new row.
I tried to do this with IF, but if for example I delete any row in Google Sheets then it marks all the rest down as if they don’t exist.
In the “IF” for FALSE, I should only get item number 28.
This is what I have in Google Sheets (changed data for example):
This is what I get from Database in n8n (after converted in “Date&Time”):
This is what I get from Google Sheet READ:
This is what I get in “IF” for TRUE:
This is what I get in “IF” for FALSE:
My workflow:
CODE:
{
"name": "MySQL -> Google Sheets",
"nodes": [
{
"parameters": {},
"name": "Start",
"type": "n8n-nodes-base.start",
"typeVersion": 1,
"position": [
200,
150
],
"alwaysOutputData": false
},
{
"parameters": {
"operation": "executeQuery",
"query": "=SELECT ID, user_login, user_email, user_registered, display_name FROM wp_users"
},
"name": "MySQL",
"type": "n8n-nodes-base.mySql",
"typeVersion": 1,
"position": [
350,
150
],
"alwaysOutputData": true,
"credentials": {
"mySql": "MySQL"
}
},
{
"parameters": {
"operation": "update",
"sheetId": "10rtXQerHJaSx7CMtopcV*****************",
"range": "A:E",
"key": "ID",
"options": {
"valueInputMode": "USER_ENTERED"
}
},
"name": "Google Sheets",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 1,
"position": [
1050,
50
],
"alwaysOutputData": true,
"credentials": {
"googleApi": "Google Services"
}
},
{
"parameters": {
"value": "={{$json[\"user_registered\"]}}",
"dataPropertyName": "user_registered",
"custom": true,
"toFormat": "YYYY-MM-DD HH:mm",
"options": {}
},
"name": "Date & Time",
"type": "n8n-nodes-base.dateTime",
"typeVersion": 1,
"position": [
500,
150
],
"alwaysOutputData": true
},
{
"parameters": {
"conditions": {
"string": [],
"boolean": [
{
"value1": "={{$node[\"Date & Time\"].json[\"ID\"].toString()}}",
"value2": "={{$node[\"Google Sheets2\"].json[\"ID\"].toString()}}"
}
]
}
},
"name": "IF",
"type": "n8n-nodes-base.if",
"typeVersion": 1,
"position": [
800,
150
],
"alwaysOutputData": false,
"executeOnce": false
},
{
"parameters": {
"operation": "append",
"sheetId": "10rtXQerHJaSx7CMtopcV*****************",
"range": "A:E",
"options": {
"valueInputMode": "USER_ENTERED"
}
},
"name": "Google Sheets1",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 1,
"position": [
1050,
250
],
"alwaysOutputData": true,
"credentials": {
"googleApi": "Google Services"
}
},
{
"parameters": {
"sheetId": "10rtXQerHJaSx7CMtopcV*****************",
"range": "A:E",
"options": {
"valueRenderMode": "FORMATTED_VALUE"
}
},
"name": "Google Sheets2",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 1,
"position": [
650,
150
],
"alwaysOutputData": false,
"notesInFlow": true,
"credentials": {
"googleApi": "Google Services"
},
"notes": "Google Sheet Read"
}
],
"connections": {
"Start": {
"main": [
[
{
"node": "MySQL",
"type": "main",
"index": 0
}
]
]
},
"MySQL": {
"main": [
[
{
"node": "Date & Time",
"type": "main",
"index": 0
}
]
]
},
"Date & Time": {
"main": [
[
{
"node": "Google Sheets2",
"type": "main",
"index": 0
}
]
]
},
"IF": {
"main": [
[
{
"node": "Google Sheets",
"type": "main",
"index": 0
}
],
[
{
"node": "Google Sheets1",
"type": "main",
"index": 0
}
]
]
},
"Google Sheets2": {
"main": [
[
{
"node": "IF",
"type": "main",
"index": 0
}
]
]
}
},
"active": false,
"settings": {
"timezone": "Europe/Warsaw"
},
"id": "1"
}