How do I bulk update via API if endpoint needs id from source?

Hey there!

I'm trying to build a mass update feature where I send in PATCH updates to our API from a Google Sheet.

I'm using the Bulk API Query template, but there's one difference I can't seem to overcome:

Our API requires the resources ID in the URL, in the instance it's an endpoint called Employee employee/{{id}}.

Each row in the source (a Google Sheet) has the id. So for each row the API URL needs to change.

Example:

Row 1 = 123, John Doe, TRUE
Row 2 = 321, Jane Doe, FALSE

API call for Row 1 = api.com/employee/123

h {John Doe, TRUE}

API call for Row 2 = api.com/employee/321

h {Jane Doe, FALSE}

Using {{query1.data[i].id}} in the Resource doesn't seem to work:

It does how ever seem to work in the body:

@MaxWillEj Welcome to the forum!

Maybe use i instead of 0 in {{query1.data[i].id}} instead? I don't have the full context so that's my first guess...

Thanks @ScottR!

So that works in the request body, but for some reason not in the URL. In the URL it returns "NULL".

URL:

Body:

Ahhh wait. - OK So the JSON field is employeeNumber..... so use {{emplyoeeNumber.value}} in the PATCH

That's just me being lazy giving an example. The body is supposed to contains employeeNumber, division and organization taken from query1. The URL is supposed to contain the id (also from query1). Just wanted to show that I can in fact see data when doing the exact same thing in the resource's "body"-section but NOT in the "action type".

hmmm, seems like a bug....maybe.... change the topic to support and hopefully they can provide a solution....

Hey @MaxWillEj!

Would you mind posting examples of the requests themselves from when the query is actually triggered with the bulkSubmit script (you may need to go to the networking tab through your browser console for this)? Variables passed with additional scope aren't actually populated in the preview of the query, only when the call itself happens, so I think looking at the actual requests might help for context here.

Regardless, the fact that the transformer is previewing differently in the JSON body and the URL is certainly odd :thinking:

Hi @Kabirdas!

Debugging I've found that the method I use doesn't work for either cases (it doesn't populate the JSON body or the URL).

request: {url: "https://api.alexishr.com/v1/employee/null", method: "PATCH",…}
body: "{"employeeNumber":null,"division":null,"organization":null}"

But then my question becomes: how DO I do this?

Because the thing that really throws me off here is that if I hardcode the value 1 the body works but not the URL:

"request":{"url":"https://api.alexishr.com/v1/employee/undefined","method":"PATCH",
"body":"{"employeeNumber":"Q1A112","division":"division Q11-2","organization":"organization Q11-2"}"

Meaning I should either change method completely or I'm missing something obvious here :sweat_smile:

And if you hover over {{ query1.data[1].id.value }} is it showing the correct value? I'm curious, if you hardcode just that value in there using double curlies (e.g. {{ "someIdString" }}) does it work?