API Connector Documentation

Print

Create a Custom Request

Last Updated OnOctober 26, 2023
byAna
< Knowledge Base

In this article, we'll walk through how to create a custom request using the API Connector add-on for Google Sheets.

If you'd prefer to create a request using API Connector's built-in API directory, please check this article: Create a Request

If you like videos, check out this video guide to using API Connector.

Contents

Before You Begin

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

Why Create a Custom Request?

  • access APIs not available in the preset list of integrations
  • access advanced API Connector features only available through custom requests, e.g. multi-query functionality.
  • import example cURL or Postman requests (many APIs provide these in their documentation)
To easily convert a preset request to a custom request, add the request metadata before running the preset request. From there you can easily copy/paste it from the response sheet into the request URL and/or request body of a custom request.

Step 1: Open the Sidebar

After installing the plugin in Google Sheets, click Extensions > API Connector > Open

Step 2: Add a New Request

Once the sidebar opens, click Create request to start a new request.

quickstart-create-request

Step 3: Choose Custom Application

Make sure Custom is selected in the Application menu.
customrequest-customapplication

Step 4: Enter a Request URL

Enter a request URL (or multiple request URLs). Available request URLs should be provided by your application's API documentation, or you can check our list of guides to many popular APIs. For example, enter this URL to fetch data about Radiohead from the iTunes API:

https://itunes.apple.com/search?term=radiohead

Request parameters are added as key-value pairs, e.g. https://api.com?key1=value1&key2=value2.

Step 5: Set Authentication

Each application's API documentation will tell you whether authentication is required, and, if so, how to apply it. Most commonly, they'll ask you to include an API key or token in the request URL or header of the request.

For more information on authentication, see here: https://mixedanalytics.com/knowledge-base/api-authentication-explained/

Step 6: Set a Destination Sheet

Set where you'd like to send the response data. Type in a sheet name or click Set current to set the destination to your currently focused cell.
customrequest-destination

Step 7: Choose Output Options

(optional) Click the Output options button and customize your request. Options include setting the output mode to overwrite vs. append, choosing a report style, applying pagination, and more.
customrequest-options

Step 8: Edit Fields

(optional) Click the Edit Fields button to preview your request. This will bring up a preview window where you can map fields to specific columns and choose which fields to display in your sheet.
customrequest-editfields

For more information on the visual field editor, see here: Edit Fields

Step 9: Name and Run

Give your request a name and click Run. You should see the response data from your API request in your sheet.
customrequest-response

Questions?

Please leave a comment below, contact support, or leave a message in the API Connector user community. You'll receive a response shortly.

51 thoughts on “Create a Custom Request”

  1. hello there.
    I'm using an API in a site called Bling. I'm trying to get all my products on google sheets.
    The resquest runs without any error, but it only gets the first 100 products (1st page on the system website)
    There is something I can do about it?

    Thanks!

    Reply

    • Hi Urian, API Connector displays all records returned from the API. Most likely your API limits the number of records in a single response (this is called "pagination"). Basically you need to check Bling's API documentation for how to receive additional records. There's probably some query string you need to adjust to receive the next 100.

      Reply

    • When you work with GraphQL APIs, you add parameters to a "query" query string (documentation). For example if we're pulling from the Countries GraphQL API, the graphQL query looks like this:
      { country(code: "BR") {
      name
      native
      emoji
      currency
      languages {
      code
      name
      } } }

      You can run this in API Connector like this:
      1) Add that whole block into your sheet, say cell A1 of sheet called Input
      2) Now in cell A2, add the following formula: =ENCODEURL(A1). This will encode the whole query so we can add it to the end of the URL.
      3) Now in API Connector, enter the following API Request URL: https://countries.trevorblades.com/?query=+++Input!A2+++
      4) Click Run and you'll see the results populate your sheet.

      Reply

  5. Thank you for this, this was so useful.
    When I run my request, no matter the configuration, it only produces maximum 26 rows of data - significantly less than there actually are in Facebook. Could you please help? Thank you!

    Reply

    • Hey Matt, their documentation says that this API only allows access through OAuth for approved partners. So unfortunately you can't simply set up your own custom OAuth connection as with other APIs, you'll need to get approved first.

      Reply

    • I don’t think this will work as we only enable sending JSON in request bodies, while it looks like this API requires an XML request body. Sorry for the inconvenience, will look into adding support for this in the future!
      Update: API Connector now supports XML request bodies.

      Reply

    • Usually you include your API key in the URL or as a Header, but it could be in the request body. There’s no generic input field for API keys, since every API is different. The API’s documentation will tell you where to include it. If you can share the name of the API, I'll be happy to check it out for you and give you more specific instructions.

      Reply

  11. How can I have all Shopify transactions show up on Google Sheet. I see Order data but I want transactions? It seems like it can be achieve via Custom request. Below is the endpoint which the documentation shows and it seems like entering an order ID mandatory:
    /admin/api/2021-10/orders/{order_id}/transactions.json

    How can I have ALL ORDER TRANSACTIONS on Google Sheet without having to run and export?

    Reply

    • I'm not really familiar with AppSheet other than knowing it's an extension for Sheets that lets you build an app. But assuming AppSheet runs off your spreadsheet, then you should be able to update your Sheet via API Connector, set it to refresh regularly, and feed that sheet into AppSheets to create an automatically-updated app.

      Reply

  13. Hi,

    I'm trying to connect to a custom API. However, to connect to the API I need to add parameters... With the custom request you can't add parameters. How do I fix this?

    Reply

    • Parameters go on the end of URLs, so you would just add them into the URL field, e.g. https://api.com?parameter1=value&parameter2=value

      Reply

    • You should be able to enter it like this: https://tripletex.no/v2/project/projectActivity/100, where you substitute in your own ID where it says 100.
      Btw if you scroll down the page there's a section where you can click "Try it out" and it produces a URL you can copy and paste into API Connector. Please check if that works for you 🙂

      Reply

    • A domain URL would mean the domain associated with a URL, e.g. if a URL is site.com/directory/12345, the domain URL is site.com.
      But I think we were messaging about this on Reddit, and if so I suggested trying the server-based form which doesn't require a domain URL.

      Reply

  16. I used API Connector to GET data from an API on a daily schedule and Append it to the destination sheet. Sometimes the data that is pulled from the API contains data that is duplicate of data that is already in the sheet. Do you have a recommendation on how to automatically purge the duplicate data from the destination sheet after every scheduled API request? Thanks for your help!

    Reply

    • Generally each API provides some date or ID-based parameters you can use to ensure that you're fetching only new data (see here for an example showing how that might work).
      Besides that, this question comes at a good time, as we're actually planning to add a new "merge data" option soon to better handle duplicate data. Is there ever a case where you want to update an existing row in your sheet (e.g. if one of the values changes depending on when you fetch the data), or do you just need to avoid printing the exact same data twice?
      Update: merge mode is now available (info)

      Reply

    • API Connector just returns data the way it comes back from the API. So you would need to check if the API has a sort parameter (many do, but from what I can tell, this one does not), use the Sheets SORT function in a second sheet, or use the JMESPath sort_by function.

      Reply

  18. Hello,
    I am learning to use APIs. When creating a Custom API "GET" Request API Connector, I do not see any option to add Parameters. How does one add Parameters.

    To clarify: I have the option to add Headers, which allows me to connect with the Custom API I am using via my api key, but I need to add in Parameters to localize the search/request by zipcode. Any advice? Thank you!

    Reply

  19. Hello,
    Thanks for this connector! I am trying to connect to a custom API that receives Basic Authentication and if the request is authorized it responds with a redirection to a S3 pre-signed URL. I am getting an error from S3 saying that only one authorization method is allowed, I think this happens because the Authorization header of the original request is being passed along with the redirection + the pre-signed URL has its own authentication method validated by S3.

    So, my question is: is the Custom API Connector copying the original request headers and passing them to the consequent redirection request? If so, is there any workaround to avoid this behaviour?

    Thank you!

    Reply

    • Hey Jhonny, yes, the URL fetch process would the same Authorization headers to the redirected URL. Unfortunately we don't have any workaround available at the moment. I'm actually not really sure how one would work, maybe some option to not set headers in the case of a redirect? For now, though I'm afraid you'll need to find another method -- or is there a way to just get the URL returned in the response? I've seen that before with Amazon AWS, where a basic auth request returns a pre-signed URL, which you can then run a second request to for fetching the data.

      Reply

  20. Hi, I'm have a business connector account setup with you and I'm trying to build a custom request for Linkly (we're moving away from Bitly) and I'm struggling with building the custom OAuth. Linkly says to send post requests to https://app.linklyhq.com/api/v1/link but the only provide an API key and a Workspce ID. I'm not sure where I can fill in the rest of the open fields to create a Custom OAuth.

    Could you provide some direction on what inputs I need to use to create this connection?
    Thank you.

    Reply

    • Hey Bryan, Linkly provides full curl examples in their Swagger documentation, so you can just copy and paste those into API Connector's curl import tool. But if you're doing it manually, looks like the request setup should be like this. Substitute in your own API key, URL, and workspace ID:
      Application: Custom
      Method: POST
      Request URL: https://app.linklyhq.com/api/v1/link?api_key=12345
      Request body: {url: "www.somelink.com", workspace_id: 33}
      Please check if that works for you!

      Reply

      • Wow, that worked very well! I didn't know you guys had a cURL import like that. Now I just need to figure out how to sort it by link clicks today over zero and I should be set.

  21. Hi! Tell me if it is possible to implement this: in the link, i must specify the "Date" parameter from which to upload data. How can I automatically insert the current date into a query so that this query can work on a daily trigger without manually editing the date in the link?

    Reply

Leave a Comment

Jump To