API Connector Documentation

Print

Include Request Bodies

Last Updated OnOctober 8, 2023
byAna
< Knowledge Base

This article describes how to add a request body to a POST, PUT, or PATCH request using the API Connector extension for Google Sheets.

For more general information about request bodies, please see this article: What is an API Request Body?

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Choose the POST, PUT, or PATCH Method

API Connector defaults to the GET method, which doesn't contain a request body. To make a POST, PUT, or PATCH request, choose that method from the drop-down menu:

requestbody-method

Enter a Request Body

As part of a POST, PUT, or PATCH request, a data payload can be sent to the server in the body of the request. When you select one of those methods from the method drop-down button, the API Connector form changes to display an input field for the request body.

requestbody-body

Request body format

API Connector supports three formats:

The format can be selected via the drop-down format selector, where JSON and XML can be automatically detected via the "auto" option:
requestbody-formatdropdown

JSON

JSON should be entered as a standard JSON object, e.g.

{"id":123, "firstName":"John", "lastName":"Doe" }

XML

XML should be entered a standard XML object, e.g.

<employee id="123"> <field id="firstName">John</field> <field id="lastName">Doe</field> </employee>

URL encoded (x-www-form-urlencoded)

To submit url encoded data in API Connector, enter key/value pairs as a JSON object. So again it will look like this:

{"id":123, "firstName":"John", "lastName":"Doe" }

Note that other API clients usually provide a key/value table to enter data that should be URL encoded. If you are copying request configurations over from a tool like Postman, you'll need to convert the table-formatted key/value pairs to JSON-formatted key/value pairs:

Set Content-Type Headers

A Content-Type header describes the object's format, so the server knows how to parse it. Selecting a request body format will automatically set Content-Type headers as follows:

  • json: application/json
  • xml: application/xml
  • url encoded: application/x-www-form-urlencoded
  • auto: JSON-formatted text will default to application/x-www-form-urlencoded, while XML-formatted text will default to application/xml

To override these content types, manually set a new content type in the Headers table, like this.
post-requests-img3

Only application/json, application/xml, application/x-www-form-urlencoded, and application/vnd.api+json content types are currently supported.

GraphQL Request Bodies

GraphQL request bodies should be entered as JSON. Use this handy converter tool to convert a GraphQL query to a JSON request body.

Troubleshooting

  • If your POST request isn't working, a likely cause is a mismatched content type. Try explicitly selecting the format from the dropdown format selector, or setting a content-type header.
  • If you need to send an empty request body, enter {}
  • url encoded request bodies should be entered as JSON.

18 thoughts on “Include Request Bodies”

  1. Great tool! 🙂
    I managed to connect to my service successfully.
    1. I encoded the user ID and password in Bas64.
    2. I called the API URL with Authorization Basic and added the bace64-Code.
    3. I added {"grant_type":"client_credentials" ,} to the POST body.

    I then get a token written in the sheet.

    To extract data from the API I built another (GET) request:
    I called the API URL with Authorization Bearer and added the token i got from the other request.

    That also worked fine. But it is not very convenient to always run both requests and copy the restult of the first (the token) into the second.

    How can I combine these two request to one?

    I would appreciate your help very much! 🙂

    Reply

    • Hi Philipp, it sounds like you're running an OAuth2 process manually, so I think the best solution is just to use API Connector's OAuth2 manager (info). That way you won't need to encode to base64 and keep manually refreshing your token.

      However if you'd like to continue with this method, I think you can semi-automate your process by referencing your token in its cell (info) instead of manually copying it in. That way you'd have, say, request #1 sending to a sheet called Token, then request #2 would include Authorization Bearer +++Token!A5+++. Then you just need to click Run twice in a row to retrieve and use the new token.

      Just let me know if I can clarify further.

      Reply

  3. Ana, I need some custom work for a few stores that is beyond my abilities. Do you do that or recommend someone with good knowledge of the API connector?

    Reply

    • Hi Rob, thank you for the message. I don't know anyone offhand, but I've seen people post this type of request on upwork.com. You could also try on the API Connector user forum: https://www.reddit.com/r/api_connector.
      Of course if you just have questions about setting up your requests, I'll be happy to assist you myself.

      Reply

    • If it says invalid POST body data that means it isn't valid JSON. Maybe you've inadvertently copied in some extra characters, or maybe I wrote it wrong to start with, I can't really say much without seeing it. Can you please paste your JSON here? I can check it out.

      Reply

    • If you'd like to add multiple request bodies you need to add them one after the other with :::BREAK::: in between. So there's no way to do something like +++Sheet!K23:K25+++ in the request body, if that's what you mean.

      Reply

  6. Regards,
    I have this code in my request body:

    {
    "startdate": "2022-01-01",
    "enddate": "2022-02-08",
    "status": 1,
    "rows": 500,
    "page": 1,
    "lasttransactionid": null
    }

    But it send me a mesage "Validation failed for parameter 'pStartDate'. Invalid date."

    I tried to use different date formats, but I always get the same message

    Reply

    • That's an error message from the API, not API Connector, so I can't really say. Maybe they require a different date format? If you let me know which API it is, I can help you check their documentation to find out the problem.

      Reply

  7. This looks like a great tool! My use case is this: the users download the data using an API call to sheet, make changes to the values (so no new records added) on the sheet, and load it back.

    How would I build the PATCH request for this case?

    Reply

    • Hi Marianna, there's no generic answer since it depends on the syntax required by the API, but generally you'd use cell references for this, e.g. {"name":"+++Sheet1!A1+++","address":"+++Sheet1!A2+++","date":"+++Sheet1!A3+++"}, such that the updated cells automatically populate your API request. Please feel free to link some documentation or contact support if you'd like more specific feedback based on the API's requirements.

      Reply

  8. Hi! Can we send a list of entries (rows) within a post/put body? The idea would be sending all the different rows on a sheet to a webhook.

    So instead of sending something like this (one row):
    {
    "date":"+++Volume!A2+++",
    "value":"+++Volume!B2+++"
    }

    I'd like to send all the rows in a dynamic way. How can I do it? Thanks a lot!

    Reply

    • Sure, you can run through a list of multiple request bodies with the :::BREAK::: delimiter, e.g.
      {"date":"+++Volume!A2+++","value":"+++Volume!B2+++"}
      :::BREAK:::
      {"date":"+++Volume!A3+++","value":"+++Volume!B3+++"}
      :::BREAK:::
      {"date":"+++Volume!A4+++","value":"+++Volume!B4+++"}

      You can can semi-automate it by constructing that entire block in your sheet. Please see this article for more info and a sample sheet showing how that could work.

      Reply

  9. {"attachments":[{"attachmentDocumentURL":"redacted.pdf","attachmentDocumentPassword":"558I"},{"attachmentDocumentURL":"redacted","attachmentDocumentPassword":"558I"}],"messageTemplateId":"43CA1F69BE56530F","messageattributes":{"attribute":[{"name":"M1_NICKNAME","value":"redacted"},{"name":"M1_CONTACT_NO","value":"redacted"},{"name":"M1_DELIVERY_ADDRESS","value":"redacted"},{"name":"M1_DELIVERY_DATE","value":"02 August 2023"},{"name":"M1_ORDER_ID","value":"01356731"},{"name":"M1_DELIVERY_TIME","value":"10:00 - 12:00"},{"name":"M1_F1","value":"iPhone 14 "},{"name":"M1_F2","value":"redacted"},{"name":"M1_F3","value":"98460728"}]},"notificationDetails":{"serviceId":"98460728","notificationType":"In-Service","notificationCategory":"Others","initiatingSystem":"redacted","serviceExtId":"redacted","custExtId":"redacted","custAcctId":"redacted","ban":"redacted"},"to":"redacted","customerIdType":"NRIC"}

    How to convert this in to simple request

    Reply

    • Hey there, I replaced private-looking values with the word "redacted" and re-posted your comment. In any case, what do you mean by converting to a simple request? You can remove the comments (everything starting with "//") and paste the entire block into the request body input of any API client, does that answer your question?

      Reply

Leave a Comment

Jump To