Search API Connector Documentation
Create a Custom Request
In this article, we'll walk through how to create a custom request using the API Connector add-on for Google Sheets.
If you like videos, check out this video guide to using API Connector.
Contents
- Before You Begin
- Why Create a Custom Request?
- Step 1: Open the Sidebar
- Step 2: Add a New Request
- Step 3: Choose Custom Application
- Step 4: Enter a Request URL
- Step 5: Set Authentication
- Step 6: Set a Destination Sheet
- Step 7: Choose Output Options
- Step 8: Edit Fields
- Step 9: Name and Run
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)
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.
Step 3: Choose Custom Application
Make sure Custom is selected in the Application menu.
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
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.
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. 
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. 
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.
Questions?
Please leave a comment below, contact support, or leave a message in the API Connector user community. You'll receive a response shortly.
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!
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"). There's some info on this in the troubleshooting documentation, but 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.
Thank tou very much!
I will check that =]
How does it works with GraphQL API?
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.
Do I need to create each API URL or is there somewhere they are listed?
You need to create them based on the API documentation provided by each website or platform.
Thanks, so no-one has compiled a fully created list yet that you know of?
There are a few API directories, e.g. https://apilist.fun/ and https://github.com/public-apis/public-apis.
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!
By default Facebook limits the number of results in a single response as described here: https://developers.facebook.com/docs/graph-api/using-graph-api/#paging. To get more records, you can page through the results as described and/or add '&limit=1000' (or some other number) to the end of your URL.
Thank you - this did the trick.
i use the Url: https://www.simcompanies.com/api/v2/players/1689309/buildings/11386268/sales-orders/
the results isnt a table.
try with json2table, everything goes right.
When I try that URL I get a
{"message": "Building not found"}error, so I can't really say much about this.I tried one of their other URLs (https://www.simcompanies.com/api/v1/market-ticker/2022-02-24T09:04:24.196Z/) and it seemed to work fine.
Have you tried a different report style? Or if you can share the JSON I can take a look and see what's happening.
ah thanks, U are right. I found the problem. This Url is not outgoing, must be opened by myself with the account.
How can i import the Api which is private.
Normally APIs provide an API key for APIs that are private. If this API doesn't provide an API key then there wouldn't be a way to access the data through Sheets, since it runs in a sandbox separate from the browser. Is there any documentation you can check?
the url is not public*
How does this work with Wise?
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.
Hello,
I am trying to pull data, but I do not know if the code I have is compatible with your API conector.
Here is the code :
(from https://developer.rentalsunited.com/?xml#list-reservations)
[UserName]
[Password]
2013-08-17 14:00:00
2013-08-17 14:20:00
0
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!
Thank you for the quick answer
Hello,
is it possible to get the request url from a field in the sheet rather than typing it in every time?
Sure, you can use cells in your request like this:
+++Sheet1!A1+++Please check this article for more info: https://mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/
Hi,
I am trying to fetch freshdesk tickets, however, showing a 401 error that credentials invalid. Freshdesk is logged in but with company's common Microsoft account, however, I am using my gmail account.
How to solve this?
Response:
We received an error from freshdesk.com (401)
{"code":"invalid_credentials","message":"You have to be logged in to perform this action."}
You don't actually have to be logged in to Freshdesk to use their API, so I think this is just a poor error message. The real problem is shown in the "code" value, which shows you have entered invalid credentials. Please doublecheck the token you've entered in the Headers section, and make sure you've correctly encoded it as shown here.
Hi I am trying to connect my mortgage software system API to google sheets to create KPI's. I don't see anywhere to enter my API Key?
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.
Hi,
I want to get monthly matomo data automatically importet per API.
The API is working but I don´t get my data set automatically for the last month.
Are there any ideas how to get it working. Thank a lot in advanced.
The export url looks like this:
module=API&format=CSV&idSite=1&period=month&date=2022-07-01&method=Actions.getPageUrls&flat=1&translateColumnNames=1&language=de&token_auth=xxxxxxxxx&filter_limit=-1&convertToUnicode=0Hi there, sorry, I didn't totally understand the question. Are you getting the wrong data when you run your request, or are you asking how to get your data automatically?
If you want to run requests automatically, you can set up scheduling as shown in this article: https://mixedanalytics.com/knowledge-base/api-connector-scheduling/. Let me know if that answers your question, or please elaborate further and I'll be happy to answer here.
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?
Is this what you're looking for? If so you can use a URL like
https://your-development-store.myshopify.com/admin/api/2022-07/tender_transactions.jsonis it posible to run an Api connector request on a app sheet so I can have my spreadsheet data updated via my app?
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.
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?
Parameters go on the end of URLs, so you would just add them into the URL field, e.g.
https://api.com?parameter1=value¶meter2=valueThis was very helpful, but I am struggling to find a solution for my case. I am trying to fetch the GET project/projectactivity(ID) from the Tripltex API (https://tripletex.no/v2-docs/). What should I put in the request url box?
Thank you!
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 🙂
My Zoho salesIQ API is asking for a javascript domain URI - what do I put?
A domain URL would mean the domain associated with a URL, e.g. if a URL is
site.com/directory/12345, the domain URL issite.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.
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!
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)
Hi, I'm trying to figure out how to get all data/fields in Freshdesk that match a condition in nested object, but the main condition is an array. Condition: All fields with Type: Event Planning if cf_done == 'true'.
I'm able to get the data for the below code using [?type== 'Event Planning'] but the hyphens in the top level and nested objects are stumping me in setting up the query. Thanks in advance!
[
{
"cc_emails": [],
"fwd_emails": [],
"reply_cc_emails": [],
"ticket_cc_emails": [],
"type": "Event Planning”,
"custom_fields": {
"cf_done": true,
"cf_location": “NY”,
"cf_point_of_contact": null
},
}
]
I actually believe I found the answer after all
[?type == 'Event Planning' && custom_fields.cf_done == `true`]
That looks right to me! Just let me know if anything isn't working as expected.
Hi there
I am using a simple API from Dexscreener to retrieve some pricing pairs.
Do you know if there is a way I can sort the data once it is retrieved? There doesn't seem to be a way to sort in the API itself so I wondered if there were other options?
https://api.dexscreener.com/latest/dex/pairs/ethereum/0xA43fe16908251ee70EF74718545e4FE6C5cCEc9f,0x950a24B86aCa5C625fC1D88d2C78EaDf8eF769DC
Thanks!
API Connector doesn't currently have any sort functionality itself, it 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), or use the Sheets SORT function in a second sheet.