Pulling data from updated google sheets row and putting into clickup

[Kyle_Fleming]

## Describe the problem/error/question
Im trying to pull newly added data from 3 columns in google sheets from the new row thats added. I have a trigger setup for this which does not error. My clickup trigger seems to be setup fine. My issue is between the google sheets trigger, and the clickup trigger I believe. I am not sure what node should go there and how to set it correctly.

The google sheets doc I am monitoring has numbers sequentially filled out in column A ( about 100 ). These numbers are our job #'s. Each new job that comes in, we fill in columns B and C in that row with the job name and customer name.

How can I monitor the rows that have no yet been filled out yet, and when it does have new data entered, what node would I use to “translate” it for the clickup node? Thank you and sorry if this is a very basic question!

{
  "meta": {
    "instanceId": "b60c7000ddfc73afd23dba37589f2f93f0e3f870f3120a88d32d4b506c90fce6"
  },
  "nodes": [
    {
      "parameters": {
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "documentId": {
          "__rl": true,
          "value": "https://docs.google.com/spreadsheets/d/1FDMdWZATbn99DyTEV4NpW5Uscc4TyIUk9tVN0BsvsRo/edit#gid=0",
          "mode": "url"
        },
        "sheetName": {
          "__rl": true,
          "value": "gid=0",
          "mode": "list",
          "cachedResultName": "JOB LOG",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1FDMdWZATbn99DyTEV4NpW5Uscc4TyIUk9tVN0BsvsRo/edit#gid=0"
        },
        "event": "rowUpdate",
        "options": {
          "columnsToWatch": [
            "Client"
          ],
          "dataLocationOnSheet": {
            "values": {
              "rangeDefinition": "specifyRangeA1",
              "range": "A16925:C17000"
            }
          }
        }
      },
      "id": "5b4913f5-337a-4d34-9ffd-be9d09d5925d",
      "name": "Google Sheets Trigger",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "typeVersion": 1,
      "position": [
        0,
        80
      ],
      "credentials": {
        "googleSheetsTriggerOAuth2Api": {
          "id": "s1ThZM5Q1gawbHv8",
          "name": "Google Sheets Trigger account"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "\n"
      },
      "id": "4c315d12-c589-49f3-92a3-cf39ea264421",
      "name": "Code",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        180,
        80
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "team": "9011142262",
        "space": "90110365304",
        "folderless": true,
        "list": "901101702225",
        "name": "= {{ $json.26642 }",
        "additionalFields": {}
      },
      "id": "f5b7e004-743b-4189-9f5a-8446b58ca7e9",
      "name": "ClickUp",
      "type": "n8n-nodes-base.clickUp",
      "typeVersion": 1,
      "position": [
        360,
        80
      ],
      "credentials": {
        "clickUpOAuth2Api": {
          "id": "6v7Rbd0DqXMWxSbp",
          "name": "ClickUp account"
        }
      }
    }
  ],
  "connections": {
    "Google Sheets Trigger": {
      "main": [
        [
          {
            "node": "Code",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Code": {
      "main": [
        [
          {
            "node": "ClickUp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "pinData": {}
}```


## Share the output returned by the last node
<!-- If you need help with data transformations, please also share your expected output. -->

## Information on your n8n setup
- **n8n version:**
- **Database (default: SQLite):**
- **n8n EXECUTIONS_PROCESS setting (default: own, main):**
- **Running n8n via n8n cloud
- **Operating system:** Ventura 13.4

[n8n]

It looks like your topic is missing some important information. Could you provide the following if applicable.

• n8n version:
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app):
• Operating system:

[ihortom]

@Kyle_Fleming , you don’t really need another node in between the GSheet trigger and ClickUp. Let’s assume your spreadsheet columns are called “job_number”, “job_name”, and “customer_name”. Once the trigger has fired, it would output something like this (with “New version” option)

{
  "row_number": 2,
  "job_number": 123,
  "job_name": "Happy days",
  "customer_name": "Sunshine"
}

Then in ClikUp connected directly to the trigger, you would reference “job_number” as following, {{ $json.job_number }}:

How can I monitor the rows that have no yet been filled out

That would required extending your workflow a bit. If you need to know the value of those rows you would have to add Google Sheet node to pull all the rows and compare it with the one that is produced by the trigger. The difference will show you the remaining unchanged records.

Here’s the extended version (I did not fill most of the fields as I do not know the structure of your spreadsheet):

The important part here is the usage of “Merge” node. When set up the way I did, it should output only the records that have not changed yet. Also, you need to make sure that “Google Sheets1” executes only once as your trigger could have more than one item returned. This is done by toggling “Execute once”.

[Kyle_Fleming]

Thank you for the reply. So the workflow partially worked. I ended up sending over 10,000 rows to clickup instead of just 1 new row .

Heres a screenshot of the spreadsheet.

[ihortom]

@Kyle_Fleming , the number of records depends on the trigger settings. If those 10,000 rows were updated within the timeframe you specified then that is what you get.

If your trigger returned only 1 record as you expected but there were 10,000 updates done to ClickUp then it is an issue with the workflow. Please, share your final workflow in this case and provide the data that the trigger produced.

[Kyle_Fleming]

@ihortom sorry, forgot to tag you

[ihortom]

@Kyle_Fleming , so, what was the output of the trigger node in the execution that created 10,000 tasks (hopefully you have the history of that workflow run)?

As a reminder, my question to you was

Please, share your final workflow in this case and provide the data that the trigger produced.

[Kyle_Fleming]

@ihortom Sorry, I thought what I posted above was the final workflow. Im not sure how to show it here so ill post screenshots if that helps.

Based on what Im seeing, it looks like GoogleSheets1 did not do anything. I also made sure to have it only execute once like you said.

[ihortom]

@Kyle_Fleming , the trigger produced 16991 items. Hence you have that number of tasks created in ClickUp.

Google Sheet1 produced nothing as all the tasks were considered new (no unchanged records).

If you try updating only one record in the spreadsheet it hopefully works as expected.

On a side note with regard to

Im not sure how to show it here

It is done by copy/paste between the pair of triple backticks as shown below

[Kyle_Fleming]

@ihortom I am confused. I did show the workflow here. ( right above where I said I forgot to tag you ) Were you referring to something else?

[Kyle_Fleming]

@ihortom So from what I’m gathering… you don’t see anything wrong with the settings and it essentially had to run one time first before knowing what an “updated” row is? OR, maybe it’s because I manually triggered it…

[ihortom]

@Kyle_Fleming , perhaps I was confused. By “it” you meant the output of the trigger I presume. However, it is done in the same way - copy output and paste in between the pair of triple backticks.

Anyway, I do not need it any more. You showed the output contained 16991 items. The workflow worked as expected in my opinion. You just need to figure out why you had 16K updates in the spreadsheet.

[Kyle_Fleming]

@ihortom Would a manual trigger cause that?

[ihortom]

@Kyle_Fleming , how does your spreadsheet get populated? Is it done by another job that perhaps overrides the whole spreadsheet? In that case, every row will be considered as updated.

As I mentioned, try to updated just one row manually and see what happens or use another sheet for the test.When I was testing with manually populated spreadsheet the trigger worked as expected.

[Kyle_Fleming]

@ihortom That was a good suggestion to test it on a different sheet. I made a new one, and it worked. I changed it from on updated row, to add or updated row. That seemed to help. Thanks again!

[Kyle_Fleming]

@ihortom correction… Its pulling the entire list everytime I update a row. The part that I dont understand is whats determining it to only output the updated row? Thats the trigger, and the output is “NEW VERSION”. To me, that sounds like the entire spreadsheet. I guess I am missing something

[ihortom]

@Kyle_Fleming , “NEW VERSION” implies that the trigger will present the new values for updated row(s). That is it does not indicate that the whole sheet will be returned, only the type of the value - whether updated (new values), old (previous values), or both.

That is,

• “New version” returns the values to which the row in question changed
• “Old version” will return the values prior to being changed
• “Both versions” will include both old and new values for the updated rows.

For better understanding, here’s how the data could look like with “Both versions” option on

[
  {
    "previous": {
      "row_number": 4,
      "Header1": "Country",
      "Header2": "Country1"
    },
    "current": {
      "row_number": 4,
      "Header1": "Country",
      "Header2": "Country2"
    },
    "differences": {
      "row_number": 4,
      "Header2": {
        "previous": "Country1",
        "current": "Country2"
      }
    }
  }
]

The output indicates that row 4 was updated and it contains the old and the new values for that row (row #4).

If all the rows are returned then all of them one way or another changed. That is why I asked you how the spreadsheet gets populated in the first place.

[Kyle_Fleming]

@ihortom ah, ok that makes sense. So its comparing them behind the scenes. So in order for it to know what the original was vs an updated row, it has to run the first time right? Do I need to specifiy the starting row? I did not set a range, and it ran once ok, but each time I update a row, it seems to have issues with the comparison aspect of it. I apologize for the questions, and I am very grateful for the amount of effort you are putting into this. Thank you again.

Regarding the input…It gets manually entered, no automations.

[ihortom]

@Kyle_Fleming ,

Do I need to specifiy the starting row?

If you need to limit the range then yes. Otherwise, no need.

If you use “Row added or updated” trigger option then the trigger will fire on new row added too. Setting the range could cause issues if the number of rows added will go beyond that range.

I think the important thing is to make sure your have the header. The first row will be considered as the header. That row should not be touched.