FR - Problématiques rencontrées

Partagez les problématiques que vous avez rencontrées sur n8n. Nous trouverons ensemble les réponses.

Suite d’un précédent thread pour plus de clarté. cf sujet commencé ici Communauté française - n8n

Mon use case :

  • je reçois une ligne de données via un webhook (ex: customer id, price)
  • je souhaite le rajouter dans toutes les lignes de mon Google Sheet

N’ayant pas trouvé la solution, j’ai triché en ne rajoutant ma donnée que sur la première ligne du Google Sheet et en laissant dans le GGsheet une formule “= ligne du dessus”

Mon flow :

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -540,
        -30
      ],
      "disabled": true
    },
    {
      "parameters": {
        "requestMethod": "POST",
        "url": "HIDDEN",
        "options": {
          "batchInterval": 200
        },
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "id_watch",
              "value": "={{$json[\"id_watch\"]}}"
            },
            {
              "name": "grade",
              "value": "={{$json[\"grade\"]}}"
            },
            {
              "name": "url",
              "value": "={{$json[\"url\"]}}"
            },
            {
              "name": "brand",
              "value": "={{$json[\"watch_brand\"]}}"
            },
            {
              "name": "name",
              "value": "={{$json[\"name\"]}}"
            },
            {
              "name": "collection",
              "value": "={{$json[\"collection\"]}}"
            },
            {
              "name": "user_id",
              "value": "={{$json[\"user_id\"]}}"
            },
            {
              "name": "id_user_watch",
              "value": "={{$json[\"id_user_watch\"]}}"
            },
            {
              "name": "mat",
              "value": "={{$json[\"mat\"]}}"
            },
            {
              "name": "mat2",
              "value": "={{$json[\"mat2\"]}}"
            },
            {
              "name": "mat3",
              "value": "={{$json[\"mat3\"]}}"
            },
            {
              "name": "mat4",
              "value": "={{$json[\"mat4\"]}}"
            },
            {
              "name": "price",
              "value": "={{$json[\"price\"]}}"
            },
            {
              "name": "ref",
              "value": "={{$json[\"ref\"]}}"
            },
            {
              "name": "search",
              "value": "={{$json[\"search\"]}}"
            },
            {
              "name": "top",
              "value": "={{$json[\"top\"]}}"
            }
          ]
        },
        "headerParametersUi": {
          "parameter": []
        },
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "HTTP Request1",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        630,
        170
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "update",
        "sheetId": "={{$node[\"Webhook\"].json[\"body\"][\"file_id\"]}}",
        "range": "A1:I2",
        "key": "id_watch",
        "options": {
          "valueInputMode": "USER_ENTERED",
          "valueRenderMode": "UNFORMATTED_VALUE"
        }
      },
      "name": "Update data Google Sheet2",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        -260,
        190
      ],
      "credentials": {
        "googleSheetsOAuth2Api": "WhichWatch"
      }
    },
    {
      "parameters": {
        "httpMethod": "POST",
        "path": "specificpath",
        "options": {}
      },
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 1,
      "position": [
        -960,
        190
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "sheetId": "={{$json[\"body\"][\"file_id\"]}}",
        "range": "Data!A1:I2",
        "options": {
          "valueRenderMode": "FORMULA"
        }
      },
      "name": "1 - Read Google Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        -760,
        190
      ],
      "credentials": {
        "googleSheetsOAuth2Api": "WhichWatch"
      }
    },
    {
      "parameters": {
        "functionCode": "item.user_id = $evaluateExpression($node[\"Webhook\"].json[\"body\"][\"user_id\"],1);\nitem.style = $evaluateExpression($node[\"Webhook\"].json[\"body\"][\"style\"],1);\nitem.genre_sex = $evaluateExpression($node[\"Webhook\"].json[\"body\"][\"genre\"],1);\nitem.price_min = $evaluateExpression($node[\"Webhook\"].json[\"body\"][\"price_min\"],1);\nitem.price_max = $evaluateExpression($node[\"Webhook\"].json[\"body\"][\"price_max\"],1);\nitem.id_user_watch = $evaluateExpression($node[\"Webhook\"].json[\"body\"][\"user_id\"],1) + \"_\" + $evaluateExpression($node[\"1 - Read Google Sheet\"].json[\"id_watch\"]);\n"
      },
      "name": "FunctionItem",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        -590,
        190
      ]
    },
    {
      "parameters": {
        "values": {
          "number": [
            {
              "name": "price_min",
              "value": "={{$node[\"FunctionItem\"].json[\"price_min\"]}}"
            },
            {
              "name": "price_max",
              "value": "={{$node[\"FunctionItem\"].json[\"price_max\"]}}"
            }
          ],
          "string": [
            {
              "name": "style",
              "value": "={{$node[\"FunctionItem\"].json[\"style\"]}}"
            },
            {
              "name": "genre_sex",
              "value": "={{$node[\"FunctionItem\"].json[\"genre_sex\"]}}"
            },
            {
              "name": "user_id",
              "value": "={{$node[\"FunctionItem\"].json[\"user_id\"]}}"
            },
            {
              "name": "id_user_watch",
              "value": "={{$node[\"FunctionItem\"].json[\"id_user_watch\"]}}"
            },
            {
              "name": "grade",
              "value": "={{$node[\"FunctionItem\"].json[\"grade\"]}}"
            },
            {
              "name": "rank",
              "value": "={{$node[\"FunctionItem\"].json[\"rank\"]}}"
            }
          ],
          "boolean": []
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        -420,
        190
      ]
    },
    {
      "parameters": {
        "functionCode": "const waitTimeSeconds = 30;\n\nreturn new Promise((resolve) => {\n  setTimeout(() => {\n    resolve(items);\n  }, waitTimeSeconds * 1000);\n});\n\n"
      },
      "name": "Wait",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        -100,
        190
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "sheetId": "={{$node[\"Webhook\"].json[\"body\"][\"file_id\"]}}",
        "range": "Data!A:AD",
        "options": {
          "valueRenderMode": "FORMATTED_VALUE"
        }
      },
      "name": "2 - Read Google Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        70,
        190
      ],
      "credentials": {
        "googleSheetsOAuth2Api": "WhichWatch"
      }
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$json[\"rank\"]}}",
              "value2": 100
            }
          ],
          "boolean": []
        }
      },
      "name": "IF grade",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        240,
        190
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {
          "reset": false
        }
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        430,
        170
      ]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [
            {
              "value1": "={{$node[\"SplitInBatches\"].context[\"noItemsLeft\"]}}",
              "value2": true
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        430,
        420
      ]
    }
  ],
  "connections": {
    "Update data Google Sheet2": {
      "main": [
        [
          {
            "node": "Wait",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Webhook": {
      "main": [
        [
          {
            "node": "1 - Read Google Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1 - Read Google Sheet": {
      "main": [
        [
          {
            "node": "FunctionItem",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "FunctionItem": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Update data Google Sheet2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Wait": {
      "main": [
        [
          {
            "node": "2 - Read Google Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2 - Read Google Sheet": {
      "main": [
        [
          {
            "node": "IF grade",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF grade": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "HTTP Request1",
            "type": "main",
            "index": 0
          },
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        null,
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Via ce flow, j’intégre les données du webhook sur toutes les lignes de mon Google Sheet. Cela met à jour des formules dans GGsheet et j’utilise ensuite le résultat de ces formules pour les renvoyer à Bubble

I receive a line of data via a webhook (ex: customer id, price)
I want to add it in all the lines of my Google Sheet

Not sure if I understand what you want to do.

Let’s say your sheets have the following structure:

first_name last_name
Ricardo Espinoza
Pedro Perez

And then you receive at the webhook the properties: customer_id and price. Should then the google sheets look like below?

first_name last_name customer_id price
Ricardo Espinoza 1 20
Pedro Perez 2 10

Or is it that you want to update all the records that have a specific customer_id?

English (Français ci-dessous) :
That would be the most common idea.
But in my workflow I do something different. As I duplicate the main database for each customer I want the webhook data to be filled in in each row.

product id parameter from webhook (ex: price) formula
1 none not yet calculated
2 none not yet calculated

the webhook then sends parameters (ex: price = 12) and the database then becomes

product id parameter from webhook (ex: price) formula
1 12 123
2 12 126

Français:
Ca serait l’idée la plus courante.
Mais dans mon flow je fais quelque chose de différent. Comme je duplique la base de données principale pour chaque client je veux que le webhook remplisse les données identiques dans chaque ligne.

product id parametre provenant du webhook (ex: prix) formule
1 aucun pas encore calculées
2 aucun pas encore calculée

Le webhook envoie ensuite les paramètres (par exemple prix = 12) et la base de données devient alors :

product id parametre provenant du webhook (ex: prix) formule
1 12 123
2 12 126

@RicardoE105 I know that you have been busy but when you have time do not hesitate to help me on this topic :wink:

Sorry for the late response. The example below should do it. However, depending on the amount of data you have, you might hit the API’s rate limit. If that is the case, you need to use the split batches node with a delay.

For reference, the sheet that I used for testing looks as shown below.

Two things are needed to make the workflow work:

  • The property you receive in the webhook has to match the column’s name that you want to update (case sensitive). Note: To mockup the webhook data, I used the set node.

  • One column needs to be a unique id. In my example, it’s the email.

Example workflow
{
      "nodes": [
        {
          "parameters": {},
          "name": "Start",
          "type": "n8n-nodes-base.start",
          "typeVersion": 1,
          "position": [
            250,
            300
          ]
        },
        {
          "parameters": {
            "values": {
              "string": [
                {
                  "name": "Price",
                  "value": "13"
                }
              ]
            },
            "options": {}
          },
          "name": "Set",
          "type": "n8n-nodes-base.set",
          "typeVersion": 1,
          "position": [
            500,
            300
          ],
          "notesInFlow": true,
          "notes": "Webhook Mockup"
        },
        {
          "parameters": {
            "authentication": "oAuth2",
            "operation": "update",
            "sheetId": "1I_jvGUOcEN1-2TSmg42pM57jF6GVLixvb-KPCoeXniw",
            "keyRow": "={{$node[\"Function\"].json[\"index\"]}}",
            "key": "Email",
            "options": {}
          },
          "name": "Google Sheets1",
          "type": "n8n-nodes-base.googleSheets",
          "typeVersion": 1,
          "position": [
            1240,
            240
          ],
          "credentials": {
            "googleSheetsOAuth2Api": "asasasasasas"
          }
        },
        {
          "parameters": {
            "authentication": "oAuth2",
            "sheetId": "1I_jvGUOcEN1-2TSmg42pM57jF6GVLixvb-KPCoeXniw",
            "options": {}
          },
          "name": "Google Sheets2",
          "type": "n8n-nodes-base.googleSheets",
          "typeVersion": 1,
          "position": [
            500,
            130
          ],
          "credentials": {
            "googleSheetsOAuth2Api": "asasasasasas"
          }
        },
        {
          "parameters": {
            "mode": "multiplex"
          },
          "name": "Merge",
          "type": "n8n-nodes-base.merge",
          "typeVersion": 1,
          "position": [
            760,
            240
          ]
        },
        {
          "parameters": {
            "functionCode": "for (let i = 0; i < items.length; i++) {\n  items[i].json['index'] = i\n}\n\nreturn items;"
          },
          "name": "Function",
          "type": "n8n-nodes-base.function",
          "typeVersion": 1,
          "position": [
            970,
            240
          ]
        }
      ],
      "connections": {
        "Start": {
          "main": [
            [
              {
                "node": "Set",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "Set": {
          "main": [
            [
              {
                "node": "Merge",
                "type": "main",
                "index": 1
              }
            ]
          ]
        },
        "Google Sheets2": {
          "main": [
            [
              {
                "node": "Merge",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "Merge": {
          "main": [
            [
              {
                "node": "Function",
                "type": "main",
                "index": 0
              }
            ]
          ]
        },
        "Function": {
          "main": [
            [
              {
                "node": "Google Sheets1",
                "type": "main",
                "index": 0
              }
            ]
          ]
        }
      }
    }

Ok nice !
Thanks a lot, I couldn’t have come to this solution by myself.

Will definitely implement it