Excel PUT to API endpoint failing with bad request

Question

I'm trying to duplicate an Insomnia POST request to an API endpoint in Excel. I believe I'm close but I'm obviously missing something in the Excel Power Query request.

Here is the working CURL request from Insomnia:

curl --request POST \
  --url https://url.... \
  --header 'Authorization: Bearer <<TOKEN VALUE>>' \
  --header 'Content-Type: application/json' \
  --data '{ "start":"2023-11-29", "end":"2023-11-29" }

And here is the Power Query request. Note that I'm pulling value for the data and token from named ranges in the workbook.

let
    Token = Excel.CurrentWorkbook(){[Name="Token"]}[Content]{0}[Column1],
    FromDate = Excel.CurrentWorkbook(){[Name="FromDate"]}[Content]{0}[Column1],
    ToDate = Excel.CurrentWorkbook(){[Name="ToDate"]}[Content]{0}[Column1],
    headers = [#"Authorization"="Bearer "&Token,#"Content-Type"="application/json"],
    postData = Json.FromValue([start = FromDate, end = ToDate]),
    Source = Json.Document(Web.Contents("https://url...", [Headers=headers, Content=postData]))
in 
    Source

Sam Nseir · Accepted Answer · 2023-12-02 14:56:02Z

1

PowerQuery can only do a GET or a POST. When Content is provided it will be POST, otherwise GET.

Try updating this line:

postData = Json.FromValue([start = FromDate, end = ToDate]),

To:

postData = Text.FromBinary(Json.FromValue([start = FromDate, end = ToDate])),

Json.FromValue returns a JSON object, Text.FromBinary will convert it to a string.

edited Dec 2, 2023 at 14:56

answered Dec 1, 2023 at 2:15

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dawg99 Over a year ago

Thanks Sam. I'm including the Content data to shift it to a POST vs. GET. However, I believe there is some other issue with the definition of the request/payload in Power Query vs. the CURL request. Any ideas appreciated.

Sam Nseir Over a year ago

Apologies, your question title threw me with PUT and I missed the POST in your curl example. Updated answer above to wrap Text.FromBinary(...) around your Json.FromValue(...).

Dawg99 Over a year ago

Thanks again Sam. I see the issue with my original message and changed the PUT to POST.

Collectives™ on Stack Overflow

Excel PUT to API endpoint failing with bad request

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related