I am trying to create a one way sync from ClickUp to Google Sheets. When a task is created or updated in ClickUp I would like the sheet to be updated accordingly (rows updated or added).
When mapping my fields in CU to columns in GS most of my custom fields are not available to select. I’m not sure what steps to take to find these custom fields (very new to make.com).
Thanks in advance!
Hi,
You need to use the list all custom fields module that click up has.
Once you do that find the custom field you want to map in google sheets and find the id it is highlighted. Then you can paste it into the formula below your_custom_field_ID
It should look like the below in the google sheet module. blacked out is where your custom field id should be
{{get(get(get(map(2.custom_fields_original; “type_config”; “id”; “your_custom_field_ID”); 1); “options”); (get(map(2.custom_fields_original; “value”; “id”; “your_custom_field_ID”); 1) + 1) + “.name”)}}
Let me know if this helps!
I just did something very much like this, and the first thing you want to do is go into your Scenario settings and set “Sequential processing” to “Yes”. Without doing this, you will get multiple duplicate rows because ClickUp has a tendency to fire multiple events for a single change and Make can execute multiple scenario runs at once. (You can mitigate this by setting your trigger to only watch two events: Task Updated (taskUpdated) and Task Deleted (taskDeleted).)
Also, if you want to process the backlog once you’re finished making the scenario, you’ll want to add a mechanism to stop multiple runs from the same task. So your whole scenario will look something like this:
The ClickUp – Get a Task module will return a value for every populated custom field from the task. They’re mostly usable as-is, but for dropdown fields, you’ll need Mr.Make’s formula.
Below is my version of the formula to get the name value (as opposed to the numeric value) of dropdown custom fields, spaced out for legibility (the code formatting also preserves normal/non-curly quotes which can affect copy-and-pasting):
{{
first( map(
get(
first( map(
3.custom_fields_original;
"type_config";
"id";
"[ClickUp Field ID]"
));
"options"
);
"name";
"orderindex";
first( map(
3.custom_fields_original;
"value";
"id";
"[ClickUp Field ID]"
))
))
}}
If you want to minimize the number of modules you use (and thus maximize your operations quota), you’ll want to hardcode things as much as possible (rather than use an iterator) and make good use of filters. Here’s how that workflow I screenshotted works (numbered based on the module numbers):
“1”. ClickUp – Watch Tasks: Set up a webhook to watch taskUpdated and taskDeleted events, with the following filter between it and the next module:
“2”. Data store – Get a record: Add a data store (on the free plan, you’ll only have space for one, so you can skip this part if you need it for something else (or replace it with a Google sheets call)), using the ClickUp Task ID ( {{1.task_id}} ) as the key and adding one field for the timestamp. (I called mine “timestamp”.) Add the following filter between it and the next module (I’m using a 30-second delay, but you can set whatever delay you like, just remember the duration for later):
“101”. Router: Make’s interesting in that its routes are not exclusive – that is, when processing reaches the end of one branch, it will continue to the following branch unless explicitly stopped. So, the first branch is where I’ve put in a delay, the second branch is for taskUpdated events, and the third branch is for taskDeleted events. When a taskUpdated event, it will go to the first branch and then the second branch.
Below are screenshots of the filters from top to bottom:
“201”. Data store – Add/replace a record: This is the first module of the first branch. This is where we log the execution time for the earlier filter. Make sure you set “Overwrite an existing record” to Yes. The variable {{var.scenario.executionStartedAt}} is built into Make and you can find it and others related to the Make environment/run in the variable picker if you go to the last tab (the one that looks like {}).
“301”. Tools – Sleep: Add a delay of your choice. I did this because usually when you create a record, you’re also filling in a few fields. Each action you take will trigger one or more webhook events, so this is an essential step to conserve operations. Enter the delay that you set in the filter after module “2”. I chose 30 seconds because for me it’s a good balance between responsiveness and quota usage.
“3”. ClickUp – Get a Task: This is the first module of the second branch. Use the Task ID from the trigger. (The response payload is surprisingly verbose – it includes every option for a dropdown custom field rather than just the one that’s been selected.)
“82”. Google Sheets – Search Rows: Select your spreadsheet and filter by your Task ID. You can set your column range to a variety sizes, though I’d recommend you set it to the minimum for your data, since it will display all the column values (even if they’re empty or null) in the variable picker. Also, set up your sheet with headers, since they will show up in later modules and make your life easier. (This is an interesting module because the Google Sheets API v4 doesn’t actually offer this exact function.)
“105”. Router: Top path to add a row if nothing returned, bottom to update the row that’s returned (you may want to add a third path to email yourself if more than one row is returned).
“83”. Google Sheets – Add a Row: If you have headers in your sheet, only columns with headers will show up here. If you want to populate columns without headers, either turn off the headers in module “82” or use the “Map” option.
You can reference fields that are not in the test payload data by their name in ClickUp (if the name has spaces, surround it with backticks ( ` ), e.g. the variable below can be entered as {{3.custom_fields.`Publication Date`}}.
Also note that Make doesn’t automatically interpret dates in custom fields (unlike Start Date and Due Date), so if you want it to show up properly in Google Sheets, you’ll either want to use something like {{formatDate(3.custom_fields.`Publication Date`; "YYYY-MM-DDTHH:mm:ss\Z")}} to pass a date string.
You’ll need to convert objects to simple arrays, otherwise you’ll just get [Collection] in Google Sheets.
“84”. Google Sheets – Update a Row: Same as “83”
“11”. Data store – Delete a record: This module is technically optional, if you don’t think you’ll run up against the storage limit. In my scenario, I actually have Add/replace a record (which you might have noticed in earlier screenshots) because instead of deleting the record for a deleted task, I have a boolean field in the data structure for whether the task has been deleted or not.
“12”. Google Sheets – Search Rows: Same as “82”. Filter using the Task ID from the trigger. For this step, you can set the returned range to the minimum (“A-Z”), since all we’re looking for is the row number.
“13”. Google Sheets – Delete a Row: Again, because I track deleted items, I actually have an “Update a Row” module that sets a “Deleted” column to TRUE. I’ve used the “Enter manually” option since we have the Spreadsheet ID and Sheet number from the previous step.
I’ve attached a blueprint to help you get started. (Note that this whole workflow doesn’t include comments. If you want those, you’ll want to create a separate branch for them, e.g.:
Thank you so much for taking the time to respond. This is great! I’ll spend some time with this over the weekend!
This is super helpful! Thank you so much! I’ll report back with how it works out!
Hi did this work @smack?
Please set as solution if it did thank you!
