Troubleshooting

While using the API Connector add-on for Google Sheets, there may be cases where your API request doesn't work, or returns an error message. This article will describe some known issues and ways to troubleshoot issues with your API requests.

Contents

• Add-on Loading Continuously
• Add-on Menus Don't Work
• Authorization Errors
• Cookies
• Duplicate Data
• Error Messages
• Extensions Menu Greyed Out
• Formulas Overwritten
• Inconsistent Column Order
• Inconsistent Row Order
• Incorrect Content-Type
• Missing Fields
• Missing Records (Pagination)
• Non-JSON/CSV/XML Response Types
• Paid Account Not Recognized
• Quotas and Limits
• Rate Limits
• Request Syntax Errors
• Requests Timing Out
• Restricted User Agent
• Restricted IP Address
• Scheduling Issues
• Sidebar Doesn't Scroll
• Wrong Format
• Wrong Format (Currency)
• Other

Add-on Stuck at Loading

If you see a screen like this after hitting Edit or Create request, it's likely because Google doesn't support multi-login (being logged into multiple Google Accounts at once) for Sheets extensions. To resolve, log out from all Google accounts and log back in first with the account you're using with Google Sheets (you can log back into your other accounts after) or re-open Sheets + API Connector in a new incognito window.

If you're using Safari, this same issue may cause the entire add-on interface to be blank white instead.

Add-on Buttons & Menus Don't Work

Issues with unresponsive or frozen buttons and menus indicate a problem with your browser. We've seen problems related to Brave browser, aggressive pop-up blockers, and other browser extensions that produce conflicts or block various functionality.

Solution

To address, run through the following suggestions. The exact cause of the issue depends on your browser, so you may need to try multiple suggestions from this list.

1. Run API Connector in incognito/private browser mode, or a separate Chrome profile
2. Run the add-on in another browser
3. Disable pop-up blocking in the browser
   • Chrome: https://support.google.com/chrome/answer/95472
   • Firefox: https://support.mozilla.org/en-US/kb/pop-blocker-settings-exceptions-troubleshooting
4. Disable all extensions and re-run. In Chrome, you can view all your browser extensions by clicking the three-dot menu in the top right of your browser and navigating to More tools > Extensions.
5. Clear cache and cookies
   • Chrome: click here 
   • Firefox: click here 
   • Edge: click here 
   • Safari: click here 

Authorization Errors

Make sure you've entered any required authorization credentials into the Headers table, and that you've encoded them to Base 64 if basic access authentication is required.

Cookies

API Connector can't access and attach cookies to your request. This means that API Connector will not work with any API that requires cookies.

Duplicate Data

There are a few configuration issues that can lead to duplicate data in your report:

• Multiple requests running and printing data into the same sheet. Double check requests and triggers to make sure only one request is sending data to the report.
• Re-fetching data that already exists in the sheet. When using append mode, make sure the request is configured so that it doesn't pull in duplicate data. More information >
• Merge mode may produce duplicate records if the merge key hasn't been set correctly. More information >

You may also toggle on the "Remove duplicate rows" option under Output options. This process will remove any duplicate rows within the current response set. Note that it doesn't compare and dedupe the response to data already in the sheet.

Error Messages

Pay attention to any error details as they will alert you to specific fields that need to be addressed. This article on Error Messages provides more details on each message.

Extensions Menu Greyed Out

The Extensions menu may be inaccessible / greyed out in Google Sheets. The most common reason is because the user does not have high enough access to the sheet. To resolve, grant Edit access to the affected user.

Formulas Overwritten

By default, all data will be cleared to the right and below the starting cell.

If you are including calculations and formulas in your sheet, we recommend adding them into columns on the left. Then you can set a destination cell to the right of those formulas, such that they don't get overwritten.

Inconsistent Column Order

In some cases, an API returns data in a different order, or doesn't send keys that aren't populated with values, resulting in mismatched column orders between pulls.

The easiest way to keep columns in place is to click Edit fields after creating your request. This will open the visual field editor, where you can choose the fields you'd like to display in your sheet and map them to a specific column.

Alternative methods include:

• re-arrange data with a JMESPath expression
• query individual columns by name into a second sheet using this Sheets function, replacing data with your sheet name, and field_name with your field name: =QUERY(data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("field_name",data!$1:$1,0),4),1,""),1)

Inconsistent Row Order

Most APIs don't send row data back in a fixed order. They may apply a default sort order, for example alphabetical, or perhaps largest to smallest, in which case new values will be inserted into the response data as the data set updates. Other APIs send data back in an entirely random order.

By default, API Connector prints out the data as it gets returned, so if the record order changes between API fetches, the row order will change as well.

If you would like to create a fixed table in which the row order doesn't change, there are 2 main approaches:

1. Pull the API response data into a raw data sheet, and then create your table in a second tab. Use native Google Sheets functions like VLOOKUP, XLOOKUP, or MATCH to fetch data from the raw data sheet into your data tab. This way it doesn't matter how the API sends back data, or if the rows get rearranged, since data will be ordered based on the lookup value.
2. Use API Connector's merge mode to update your table. When you use merge mode, values in your sheet will retain their position. However, any new values will be appended to the end of your sheet, which may not be what you're looking for.

VLOOKUP is probably the most common and easily applied solution to bring data from one table to another, while keeping the row order the same. It also allows for a separation between the data layer and the presentation layer, as you can create a nicely formatted table that doesn't get overwritten. Here is Google's official documentation on VLOOKUP; there are many online guides and videos as well.

Incorrect Content-Type

When making POST requests, we tell the server how to parse the content using a content-type header that describes the object’s format. For example, API Connector may automatically set the content type to application/x-www-form-urlencoded, so you don't have to set it yourself. If your server expects a different content type, you can manually set the content type to override it, by adding key/value pairs to the Headers table.

Missing Fields

API Connector is a pipeline connecting APIs to Google Sheets and in general prints out whatever the API returns. Therefore, if you're not seeing fields that you expect, first check the raw API response and confirm that the API has indeed sent back the fields you're looking for. If not, you will need to check the API documentation and adjust your API request configuration.

If you see fields in the raw response but not in your sheet, you may have inadvertently applied a JMESPath filter or a field mapping that filters them out. To remove these, check the following:

1. Click Output options > JMESPath and remove any JMESPath expression
2. Click Edit Fields > Refresh Fields to reset any field mapping

Missing Records (Pagination)

By default, many API servers return only a limited number of rows in each response. This is known as "pagination". To retrieve data you need to fetch data page by page.

In API Connector, you can automatically loop through paginated responses using pagination handling.

Non-JSON/CSV/XML Response Types

API Connector supports all three major data formats: JSON, XML, and text/CSV. If you request data in another format, apply JMESPath that converts the response into a non-JSON/CSV/XML format, or request a 'regular' HTML website URL rather than an API endpoint, the request won't work as expected.

Paid Account Not Recognized

If API Connector doesn't recognize your upgraded account status after purchase, try the following:

• Click the Account icon in the footer or main menu of API Connector to update the cache and force a refresh of your account status.
• Double-check that you're using the same email address you entered into the purchase form. You will need to be logged into Sheets with the same email address that purchased the license. If the email address is not the same, you may transfer your account.

Quotas and Limits

The API you're accessing may limit the number or rate of requests. Google Sheets itself also has built-in limits, as documented here: https://developers.google.com/apps-script/guides/services/quotas. There is a limit of 20,000 API calls quota per day per user, per sheet.

To avoid inadvertently reaching these limits, limit triggers and queries where possible (e.g. don't run hourly refreshes if you only need daily). Since these limits are per user, you may also hit the API limit if you're running your own custom scripts or functions in addition to requests from API Connector.

Rate Limits

Many APIs limit the number of requests you can send per minute. Generally API Connector doesn't hit these limits as it automatically inserts 2 second delays between stacked or paginated requests. However, there are two scenarios where it's more likely to run into rate limits:

• when using popular, open APIs. Open APIs often rate limit by IP address, and large numbers of people use these APIs to pull data to Google Sheets. All requests in Google Sheets run through Google's shared IP address pool at the same time, such that rate limits can be triggered even when you personally aren't running a large number of requests. Because of this, it's generally best to find APIs limited by API keys rather than IP address.
• The IMPORTAPI custom function can rapidly fire off large numbers of requests. Please apply IMPORTAPI carefully or use the standard sidebar method to avoid these issues.

Request Syntax Errors

When you're reading API documentation, you'll likely see examples based on different coding languages. Don't copy these in directly -- API Connector is a no-code method of interaction with APIs, so pasting in code snippets won't work.

Just type in the URL, any headers, and other options. Check the knowledge base for an example of what a completed API request looks like.

Requests Timing Out

Request processing time relates to both waiting for the server to respond and converting/processing the response into a tabular format for Sheets.

Therefore, if requests are timing out, first fetch the raw data from the server (click Output options > More > View API Response).

If the server responds normally (i.e. within a few seconds), then the the issue likely lies with converting the response into a table for Sheets. Very large or deeply nested data sets may cause API Connector to time out or stop responding, due to Google Sheets' 6-minute per request limit.

Solution

To resolve this issue, try any or all of the following:

• limit the size of your request. Most APIs let you limit request size via date- or ID-based query strings. You can usually use these query strings to fetch new data only instead of re-fetching an entire data set.
• batch requests where possible, e.g. if you're fetching data for a list of product IDs, check if your API accepts a comma separated list of IDs in a single request, instead of sending 1 request per ID.
• switch to 'compact' or 'grid' report style. The algorithm for the default single-row output takes a little longer to run, since it needs to process and flatten all the nested data into a single row.
• if you are using pagination handling, set the request to stop running after 1 or 2 pages, to see if the issue lies with pagination settings (either too many loops, or improper configuration).
• use the field editor or JMESPath filters to reduce the number of fields printed into your sheet.
• processing time may increase if your sheet contains a lot of formulas that update as the API request changes cell values. Therefore, running your requests in a fresh new sheet may help.
• split your request up into multiple requests that each fetch a portion of the data. If you created 4 tabs with 100 records each, you could then combine them all back into a single master sheet at the end with a formula like =unique(QUERY({'Page1'!A1:Z101; 'Page2'!A1:Z101; 'Page3'!A1:Z101; 'Page4'!A1:Z101}))  
• Certain JSON structures produce a very large number of columns by default and time out the report. To fix this issue, use the Force rows option.

Restricted User Agent

Google Apps Script doesn't enable custom user agents. This means any request from API Connector will use Google script's default user agent 'Mozilla/5.0 (compatible; Google-Apps-Script)'. Unfortunately certain APIs may block requests from Google's user agent.

To test if you're encountering this issue, you may test your request through a different API client like Postman. In the Headers section, input a Key of User-Agent, and a Value of Mozilla/5.0 (compatible; Google-Apps-Script) to replicate the setting automatically applied by Google Apps Script.

If you find that your request fails when using Google's user agent, you will need to ask the platform to lift this restriction on their API.

Restricted IP Address

All API requests through Apps Script (the scripting language used by Sheets) originate from a set pool of IP ranges associated with Google's servers. This may prevent a request from resolving if the API has blocked requests from Google's IP addresses, or if the API requires all IP addresses to be whitelisted in advance.

Google provides all the IP ranges used by Apps Script here. Each IP address shown is followed by a slash (e.g. 34.80.0.0/15), indicating that it represents a range. To see the full range represented by each entry, you can use an IP range conversion tool like the one here: https://www.ipaddressguide.com/cidr.

Scheduling Issues

If you are experiencing issues with your scheduled reports, please consult this list of troubleshooting tips specific to triggers: Troubleshooting Triggers

Sidebar Doesn't Scroll

When using the Firefox browser, the scroll bar may not work consistently.

To address, open the hamburger menu in the top right and navigate to Settings > General > Performance. Untick the Use recommended performance settings box, as well as the Use hardware acceleration when available box.

Close and re-open Firefox, and scroll bars should work again.

After completing this, you can test re-enabling recommended performance settings.

Wrong Format

API Connector prints out data as it's received. If the data output does not look as expected, that could be because of how the API has sent the data, or because of how Google Sheets has interpreted the format.

Sometimes you may be able to adjust the format by simply changing the formatting applied to the column in Sheets. In other cases, you may find it more effective to alter the data through a Sheets formula.

Tips for applying Sheets functions:

• Apply Sheets functions directly to fields through the field editor: https://mixedanalytics.com/knowledge-base/choose-and-edit-fields/#editfields-formulas. This will let you pre-process the data before it gets printed into the sheet.
• Alternatively, if you're adding functions after the data has already been printed into the sheet, add them into columns on the left. Then set the destination cell to the right of those formulas, ensuring the formulas don't get overwritten when the data refreshes.
• If you're adding functions to your sheet, use array formulas where possible. Array formulas apply to the entire column so you don't need to copy them down as the data set expands.

Wrong Format (Currency)

Currency format issues are a common subset of the more general "Wrong Format" issue.

API Connector prints out data in the format the API sends it, which sometimes causes a conflict with the settings of the sheet. For example an API may return data in the US format (e.g. $1,234.56), which displays strangely in a sheet set to a German locale.

To address, try the following:

• Set the sheet locale to US as this is the most common format (File > Settings > Locale)
• If changing the locale isn't convenient, you can instead apply a Sheets formula through the field editor to convert the data. For example, this function will convert all periods to commas, and vice versa, and can be selected from the preset formula menu: =substitute(substitute(substitute(E2;",";"#");".";",");"#";".")*1.
• If you prefer to add it separately into your sheet, add the formula(s) into column A, and set the data destination to column B. This will prevent your formulas from getting overwritten. If you add the formula to your sheet rather than through the field editor, you can use an array formula like =arrayformula(if(E2:E<>"";substitute(substitute(substitute(E2:E;",";"#");".";",");"#";".");"")*1). When you use an array formula, you only need to enter it once (in, say, cell A2) as it will automatically apply to the entire column.

Other

Still having trouble? Check out the knowledge base or leave a post in the user community. You'll receive a response shortly!