How to Update a Specific Cell in Google Sheets for calculating leads payout

Questions
1

Help Needed: How to Update a Specific Cell in Google Sheets

Hello everyone,

I’m working on automating a workflow in n8n and need some assistance with the Google Sheets integration. Here’s what I’m trying to achieve:

In a Google Drive account, there are multiple sheets, each corresponding to a media buyer. After identifying the correct sheet using the media_buyer_id, we will use the current date to locate the appropriate row (each row corresponds to a day of the month). Once the row is identified, we will update the Payout column if the lead is pending, or the Netto column if the lead is confirmed or approved (by update I mean, adding the value of the lead payout to the already existing value in the correct cell). The update will be made under the correct offer, identified by the offer_id. Additionally, I will modify the column containing the product name to include the offer_id.

I am receiving postback data from TrafficManager via a Webhook that includes the following details:

  • lead_id
  • offer_id
  • media_buyer_id
  • status (approved, pending, confirmed)
  • payout

The goal is to update specific cells in a Google Sheets document, where each row corresponds to a day of the month (e.g., row 9 for 1-iun, row 10 for 2-iun, etc.)(iun stands for June), and the column to update is either Netto or Payout based on the lead’s status.

image
image1143×641 50 KB

image
image1017×494 24.4 KB

The Problem:

  • The challenge now is to dynamically identify the correct row for today’s date, identify the correct product column based on the offer_id, and then update the Netto or Payout column accordingly.

Specifically:

  1. How to match the row dynamically based on the current date, now that the Daily Report column is no longer used.
  2. How to identify the correct product column based on the offer_id, and then update either the Netto or Payout column in the right row:
  • If the status is approved or confirmed, update the Netto column for the matching offer_id.

What I Need Help With:

  1. How to match the row dynamically based on today’s date

  2. How to dynamically match rows** based on today’s date and update the corresponding Netto or Payout columns.

  3. How to determine the correct column** for Netto or Payout based on the offer_id so that the correct product column is updated.

I would greatly appreciate any suggestions or advice on how to achieve this in n8n.

If you think there are better ways to this, please let me know!
This is my first time using n8n, so sorry if the post is not detailed enough ':D.
Thanks in advance for your help!

Workflow

({
  "nodes": [
    {
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "caseSensitive": true,
                  "leftValue": "",
                  "typeValidation": "strict",
                  "version": 2
                },
                "conditions": [
                  {
                    "leftValue": "media_buyer_id",
                    "rightValue": "Clau",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "id": "93b11aba-4da9-4fb7-bc6c-b500998dd6d6"
                  }
                ],
                "combinator": "and"
              },
              "renameOutput": true,
              "outputKey": "Clau"
            },
            {
              "conditions": {
                "options": {
                  "caseSensitive": true,
                  "leftValue": "",
                  "typeValidation": "strict",
                  "version": 2
                },
                "conditions": [
                  {
                    "id": "793b1b9d-0cbf-4fcb-9f27-7638c49a8675",
                    "leftValue": "media_buyer_id",
                    "rightValue": "Alex",
                    "operator": {
                      "type": "string",
                      "operation": "equals",
                      "name": "filter.operator.equals"
                    }
                  }
                ],
                "combinator": "and"
              },
              "renameOutput": true,
              "outputKey": "=Alex"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.switch",
      "typeVersion": 3.2,
      "position": [
        440,
        0
      ],
      "id": "bf0da761-7459-40ea-984a-877e6367bf8a",
      "name": "Switch"
    },
    {
      "parameters": {
        "operation": "update",
        "documentId": {
          "__rl": true,
          "value": "1ZI0tGgsKO4nO0i0ksL1Qt6KHCRqlUjUWugB8crkOrig",
          "mode": "list",
          "cachedResultName": "RandomSheetForWork",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ZI0tGgsKO4nO0i0ksL1Qt6KHCRqlUjUWugB8crkOrig/edit?usp=drivesdk"
        },
        "sheetName": {
          "__rl": true,
          "value": "gid=0",
          "mode": "list",
          "cachedResultName": "Foaie1",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ZI0tGgsKO4nO0i0ksL1Qt6KHCRqlUjUWugB8crkOrig/edit#gid=0"
        },
        "columns": {
          "mappingMode": "defineBelow",
          "value": {
            "row_number": "="
          },
          "matchingColumns": [
            "row_number"
          ],
          "schema": [
            {
              "id": "row_number",
              "displayName": "row_number",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": true,
              "readOnly": true,
              "removed": false
            }
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "cellFormat": "USER_ENTERED",
          "locationDefine": {
            "values": {
              "headerRow": "={{ 1 }}",
              "firstDataRow": "={{ 2 }}"
            }
          }
        }
      },
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4.6,
      "position": [
        720,
        80
      ],
      "id": "27d7dd8e-9cca-48f9-a1b5-5db9487b9c58",
      "name": "AlexSheet",
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "BVKTs1iUtPbdcxu0",
          "name": "AlexSheet"
        }
      }
    },
    {
      "parameters": {
        "operation": "update",
        "documentId": {
          "__rl": true,
          "value": "1ZI0tGgsKO4nO0i0ksL1Qt6KHCRqlUjUWugB8crkOrig",
          "mode": "list",
          "cachedResultName": "RandomSheetForWork",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ZI0tGgsKO4nO0i0ksL1Qt6KHCRqlUjUWugB8crkOrig/edit?usp=drivesdk"
        },
        "sheetName": {
          "__rl": true,
          "value": "gid=0",
          "mode": "list",
          "cachedResultName": "Foaie1",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1ZI0tGgsKO4nO0i0ksL1Qt6KHCRqlUjUWugB8crkOrig/edit#gid=0"
        },
        "columns": {
          "mappingMode": "defineBelow",
          "value": {
            "row_number": "="
          },
          "matchingColumns": [
            "row_number"
          ],
          "schema": [
            {
              "id": "row_number",
              "displayName": "row_number",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": true,
              "readOnly": true,
              "removed": false
            }
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "cellFormat": "USER_ENTERED",
          "locationDefine": {
            "values": {
              "headerRow": "={{ 1 }}",
              "firstDataRow": "={{ 2 }}"
            }
          }
        }
      },
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4.6,
      "position": [
        720,
        -160
      ],
      "id": "5110fb2b-3336-497b-867f-a317f6cd1162",
      "name": "ClauSheet",
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "DgP0oybPb8inpZu6",
          "name": "ClauSheet"
        }
      }
    },
    {
      "parameters": {
        "assignments": {
          "assignments": [
            {
              "id": "ffb25475-d009-44cb-b1dc-c2bc76ce80f4",
              "name": "lead_id",
              "value": "={{ $json.query.lead_id }}",
              "type": "string"
            },
            {
              "id": "e1c8cb6a-ecba-4ff4-a6f6-9df4a6c443f7",
              "name": "offer_id",
              "value": "={{ $json.query.offer_id }}",
              "type": "string"
            },
            {
              "id": "d403e69d-bb24-4f0a-bd5a-d9444cbc8803",
              "name": "media_buyer_id",
              "value": "={{ $json.query.media_buyer_id }}",
              "type": "string"
            },
            {
              "id": "963d423e-356d-4525-8dfe-e57d02b48c24",
              "name": "status",
              "value": "={{ $json.query.status }}",
              "type": "string"
            },
            {
              "id": "d48f2522-b112-42be-b34d-4cf944418ca4",
              "name": "payout",
              "value": "={{ $json.query.payout }}",
              "type": "string"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.set",
      "typeVersion": 3.4,
      "position": [
        220,
        0
      ],
      "id": "d012af2c-4f9c-4436-b704-14b02a5bffa8",
      "name": "Variables",
      "onError": "continueRegularOutput"
    },
    {
      "parameters": {
        "path": "5f08ab4e-364a-4aad-9151-31bfc747cc4b",
        "options": {}
      },
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 2,
      "position": [
        0,
        0
      ],
      "id": "0007c07b-c21e-46cf-b097-94835bf49129",
      "name": "TrafficManagerWebhook",
      "webhookId": "5f08ab4e-364a-4aad-9151-31bfc747cc4b"
    }
  ],
  "connections": {
    "Switch": {
      "main": [
        [
          {
            "node": "ClauSheet",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "AlexSheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Variables": {
      "main": [
        [
          {
            "node": "Switch",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "TrafficManagerWebhook": {
      "main": [
        [
          {
            "node": "Variables",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "pinData": {
    "TrafficManagerWebhook": [
      {
        "headers": {
          "host": "clicknorder.app.n8n.cloud",
          "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/137.0.0.0 Safari/537.36",
          "accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
          "accept-encoding": "gzip, br",
          "accept-language": "ro,en-US;q=0.9,en;q=0.8,it;q=0.7",
          "cdn-loop": "cloudflare; loops=1; subreqs=1",
          "cf-connecting-ip": "82.77.225.46",
          "cf-ew-via": "15",
          "cf-ipcountry": "RO",
          "cf-ray": "94b88cf374c8623d-OTP",
          "cf-visitor": "{\"scheme\":\"https\"}",
          "cf-worker": "n8n.cloud",
          "cookie": "rl_page_init_referrer=RudderEncrypt%3AU2FsdGVkX1%2BPMc0fHC1%2B6WGcHdsOz3337ZCdrKOLcHs%3D; rl_page_init_referring_domain=RudderEncrypt%3AU2FsdGVkX18so6JnptOh0DfZ%2FyWeXPnKPAqmhUnatQM%3D; _fbp=fb.1.1747474254824.912622659762526511; _gcl_au=1.1.188260517.1747474246; _gid=GA1.2.1850268195.1749203361; n8n-auth=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjhkYWQwYjliLWYyN2EtNGIwYy05Zjk1LThlODIwM2RjNjdmMiIsImhhc2giOiJGQlEycjl4aWdOIiwiaWF0IjoxNzQ5MjEwMzA4LCJleHAiOjE3NDk4MTUxMDh9.jLRaipIHxhyVrR03xc21Tm8ORiqFrqVuW5KNTT3Y6Wk; n8n_anonymous_id=54ac23c9-0c3b-477c-8754-62d56aee30b7; _ga=GA1.1.2114063149.1747474348; _ga_1EB8LCPG5B=GS2.2.s1749211816$o4$g0$t1749211816$j60$l0$h0; rl_anonymous_id=RudderEncrypt%3AU2FsdGVkX1%2BPAMRMzGXWeL1bSRxrXA58kv6gxBHAssFCzOI6Dwffzg%2FasCES2PE7ZaA%2FezoBZRMjpklkuYCzaA%3D%3D; rl_user_id=RudderEncrypt%3AU2FsdGVkX1%2FOVnyLl02k0cF%2FF3i%2Fcsr4Od1Vcr3O1z%2FoqAzoK8cPpwNMJT%2FiDtJxZThFCc1i7oRc5tq%2BoRqKpfs4dWbvDgr1r62cDac2xNNPLSOMuJrorRYrbYMwKFTmelAQ4fhUFAAP9hJhX%2FqP%2FPC7x7EGgV%2FM%2Faxp729nkKA%3D; rl_trait=RudderEncrypt%3AU2FsdGVkX182yhwvVEquYg5JFL%2Bv0IlBR0elW%2FHC%2B0cuCJZZOxRyGo%2BFv2lx20UxjCP8hwuOwj50OZturjt8rx0A1o7WJlvwv0KJ%2F1Fb3LQU9FCmsBUKHnrOnUP2sRpGNwyGB0iM1kE7YQEUxOtqF1hUA9K9I43TgFy%2FvWfR%2Fas%3D; _ga_0SC4FF2FH9=GS2.1.s1749209985$o4$g1$t1749212415$j60$l0$h0; rl_session=RudderEncrypt%3AU2FsdGVkX19cGRU8m9PH6Q4wfB0N0qYFG21ynRyasupa4xA3FS0Hl5grxUK3fBJwLkhSoQd%2FaZ68Ru23zlm23M8HgjGsKG2s9YQIaVc7YMZpxyJfVG0ZxlBuq3vIl2w9fUP5N6Xa5%2BVriz%2BSIPpVhw%3D%3D; ph_phc_4URIAm1uYfJO7j8kWSe0J8lc8IqnstRLS7Jx8NcakHo_posthog=%7B%22distinct_id%22%3A%22c8f23d938b7342f54adee369c51f7f0f12c25efc4d104a6932a966c163c20703%238dad0b9b-f27a-4b0c-9f95-8e8203dc67f2%22%2C%22%24sesid%22%3A%5B1749219621870%2C%220197450f-2ccb-7015-ba81-c302d007a52c%22%2C1749210311883%5D%2C%22%24epp%22%3Atrue%2C%22%24initial_person_info%22%3A%7B%22r%22%3A%22%24direct%22%2C%22u%22%3A%22https%3A%2F%2Fmanyleads.app.n8n.cloud%2Fsignup%3FinviterId%3D5f6015f0-ea6f-41b6-bc39-4bf489b51102%26inviteeId%3D80b1d20d-c62a-4ab9-baec-d53072fcb11b%22%7D%7D",
          "priority": "u=0, i",
          "referer": "https://offers.manyleads.it/",
          "sec-ch-ua": "\"Google Chrome\";v=\"137\", \"Chromium\";v=\"137\", \"Not/A)Brand\";v=\"24\"",
          "sec-ch-ua-mobile": "?0",
          "sec-ch-ua-platform": "\"Windows\"",
          "sec-fetch-dest": "document",
          "sec-fetch-mode": "navigate",
          "sec-fetch-site": "cross-site",
          "sec-fetch-user": "?1",
          "upgrade-insecure-requests": "1",
          "x-forwarded-for": "82.77.225.46, 162.158.19.220",
          "x-forwarded-host": "clicknorder.app.n8n.cloud",
          "x-forwarded-port": "443",
          "x-forwarded-proto": "https",
          "x-forwarded-server": "traefik-prod-users-gwc-56-6f4b847c49-jn6k4",
          "x-is-trusted": "yes",
          "x-real-ip": "82.77.225.46"
        },
        "params": {},
        "query": {
          "lead_id": "{leadId}",
          "offer_id": "{offerId}",
          "media_buyer_id": "{subid}",
          "status": "{status}",
          "payout": "{payout}"
        },
        "body": {},
        "webhookUrl": "https://clicknorder.app.n8n.cloud/webhook-test/5f08ab4e-364a-4aad-9151-31bfc747cc4b",
        "executionMode": "test"
      }
    ]
  },
  "meta": {
    "templateCredsSetupCompleted": true,
    "instanceId": "c8f23d938b7342f54adee369c51f7f0f12c25efc4d104a6932a966c163c20703"
  }
})

Information on your n8n setup

  • **n8n version: 1.95.3

  • Database: SQLite

  • n8n EXECUTIONS_PROCESS setting: own

  • Running n8n via: n8n cloud

  • Operating system: Windows 10

2

Hello @Alexandru_Constantin,
Your post is super detailed, so thanks for the effort you put into it.

I get some ideas, please evaluate these and pick up only the good ones :slight_smile:

Since each row corresponds to a day of the month you can calculate the row number dynamically using the current date, right? This could give you the row number to update based on the current date.

You can use the Get All operation in the Google Sheets node to retrieve the header row. Search for the column that matches the offer_id and the corresponding status and store the column letter or index for use in the update operation.

To update the specific cell you can retrieve the entire row data using the Get Row operation, modify the specific cell value in the retrieved data and use the Update Row operation to write back the modified row.

Idk if I can get the entire schema or problem, let’s discuss of course