API Connector Documentation
Edit Fields
API Connector's visual field editor lets you choose which fields from the response to filter or keep in your sheet. You can also re-arrange and rename the fields in your report, and modify field values with formulas.
Why Use The Field Editor?
- lock columns in place when an API changes the order of fields in their response
- re-arrange, rename, and sort columns
- modify data with Sheets formulas
- simplify reporting by displaying only the fields you need
Contents
Before You Begin
If you haven't already, click here to install the API Connector add-on from the Google Marketplace.
Open Editor
To enter the visual field editor, click Edit fields from the Create screen.
Edit Fields
For each response field, you have several options:
Filter/Keep fields
Choose whether to display or filter out fields by clicking the checkbox at the top of each column.
Use the Filter all / Keep all buttons at the top to modify all fields at once. When used in conjunction with the search bar, only fields matching the search term will be modified.
Rename Fields
To rename fields, click the pencil icon next to the field name. This will produce a modal where you can rename your field.
Sort Columns
Click the sort icon next to the field name to sort the data in the column.
Clicking once will sort ascending, clicking again will sort descending, and clicking a third time will remove the sort.
Add Formulas
Click the code icon next to the field name to transform data with any Sheets function. This will produce a modal where you can select from a list of preset functions or transform your data with your own custom function.
Formula Usage Notes
- To refer to the field you're modifying, use the syntax
$1. If the cell values are strings, use quotation marks, i.e."$1" - Formulas must follow Google's localization requirements for the locale of your sheet, i.e. several European countries use semi-colons rather than commas as separators in Sheets formulas.
- Formulas apply only to the field you're modifying, and can't produce calculations that involve 2 or more fields.
- Since custom functions can only return data, formulas will work only for requests run through the sidebar, not those run through the IMPORTAPI custom function.
- Hit Save fields and then run the request to apply the formula (it won't be reflected in the preview window)
Formula Examples
- Force text to uppercase
=UPPER("$1") - Convert text strings to number
=VALUE("$1") - Return the first 4 characters of a string
=LEFT("$1",4) - Divide by 100
=$1/100 - Convert UNIX epoch timestamp in seconds to Sheets date
=EPOCHTODATE($1,1) - Convert UNIX epoch timestamp in milliseconds to Sheets date
=EPOCHTODATE($1,2) - Convert US currency to European currency format
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("$1";",";"#");".";",");"#";"."))
Re-arrange Fields
By hovering over a field header, you can drag and drop it to a new location.
Once fields are re-arranged click Save fields to save the updated column order. Once you click Save, the response from the API will always print out in the order shown in the field editor.
Add New Fields
After saving a field mapping, you may modify your request URL (or the API may change its response) such that new fields are now available that weren't in the original saved field mapping.
These new fields will appear to the right of the original columns, and will be marked as new. 
Save Field Positions and Settings
You may want to account for cases where the API changes the order of fields in their response. For example, a field named "purchases" may be zero for a particular date range, in which case the API doesn't return it at all, causing all other fields to shift over one column.
To address this and lock columns in place, click Save fields in the bottom right. Once fields are saved, all future runs of this request will maintain all edits and print out columns in exactly the same order, regardless of how the API's response is structured. 
On the other hand, if you'd prefer your request to print out all fields exactly as they're received from the API, click Refresh fields and then Close to continue without a fixed field mapping.
Shift Fields
Clicking the shift button sends all selected fields to the left, where it's easier to view and manage them.
Shifting fields is just for convenience while building a report; it doesn't affect the output in any way.
Refresh Fields
When you change your report style, enter a new URL, or if your API starts sending new or different fields, you may want to refresh fields to match the updated field structure.
You may also want to reset any changes you've made to column names or positions.
To refresh fields and reset any changes, click Refresh fields
Show Raw API Response
To view the raw response data from the API, click the Show/Hide raw response button.
You can also click Download raw response if you'd like to view the raw data in another application or paste it into the JMESPath query tester.
Troubleshooting
- For paginated or multi-query requests, the field editor will only show records from the first page of results.
- Features like adding timestamps, adding request URLs, and removing headers are managed independently of the field editor, and can be toggled on and off through the sidebar.
- JMESPath expressions are processed first. If you include a JMESPath expression, the field editor will display the post-JMESPath output.
Is it possible to filter data by a specific value in a specific field?
Sure, please check out JMESPath filtering.
Hi is it possible to keep the same font format when updating? Everytime I run the API, the font resets to the default and its kinda annoying. Also the API empties so many cells and have to format such that no data can be on the right side of the data extracted.
Sorry, we currently clear the sheet when requests are run, since most people expect/prefer that, but I'll try to add an option for this in the future. For now I suggest doing all formatting in a second sheet and importing in the data you need from your API data sheet (e.g. with QUERY or VLOOKUP functions), that way the formatting won't be affected.
Update: there's now an option not to clear the sheet (info).
Is it possible to create multiple instances? I need to have weather forecasts for multiple cities and not just one.
Thank you for your reply.
Sure, you can either create and run separate requests, or you can cycle through requests all in one go using the multi-query feature.
{"error":{"message":"(#200) Provide valid app ID","type":"OAuthException","code":200,"fbtrace_id":"AmYYAeFGq2NRF4pZfNp9ZOO"}}
This looks like an error message from Facebook saying that you've entered an incorrect app ID.
If you provide some context I may be able to give you more specific suggestions.
How do I filter for a specific piece of data in a specific location? The API is returning date and stock price data. The data I want is always the first one at the top of the JSON code, looking at the raw response. The others below it are the previous days which I dont need. How do I filter so only the data at the top of the JSON code, the most recent data, is displayed?
The field editor won't help in this case as it's designed to let you choose which columns you'd like to keep in your sheet, not to filter specific records from the API. But most APIs accept date parameters so you should be able to tell it from the start to send back data from only the previous day. That would be the most straightforward solution.
Alternatively, you could use Sheets functions in a second tab to do this. There are many possible ways to use functions for this, for example you could use =MAX() to get the MAX date into one cell, and then =VLOOKUP() to get all the data associated with that maximum date (here's one guide to that technique).
Ok, thank you for the alternative suggestions.
First off, THANK YOU FOR ALLLLLL OF THIS.
Second, is there any way to add new fields to the request URL while keeping custom column names? I just spent a decent chunk of time selecting and editing field names, to then discover I was missing a nice-to-have column in my Shopify order data.
Sorry, you can't currently add fields once you finalize your mapping, but that's a really good suggestion, we'll add support for that.
As a workaround we could export your request file, edit it, and then import it back in. Just message me via support and I'll set it up for you or walk you through how to do it.
Update: new fields will now be shown in the field editor, so you can select them and add them in without resetting the entire mapping.
Hi thank you for all this info
I have a quick questions. How do I change the order of the output data?
Its currently pulling the most recent data to the op of my sheet, I would like the latest date to be at the bottom
Hey Russell, we don't have a sort function within the extension, but many APIs provide a sort parameter on their side. If you share which API you're looking at, I can take a look at their docs and let you know.
Update: You can now directly sort fields through the extension (info)
Hi,
After saving and closing "Edit Fields" screen, if i want to review/edit subsequently, clicking on the "Edit Fields" doesn't open the "Preview and Edit Fields" popup - is this the intended behaviour?
Nope, it should re-open whenever you click it. I just tested and it seems to be working properly on my side. Which browser are you using?
Is there a way to remove the header row entirely? I am appending data, and don't want to include the header row with each scheduled run
When you use Append mode, the header row should be automatically removed. Alternatively, there's an output option to remove headers.
Hi, my data does not provide more than 101 results
Sounds like your API limits the results they send back at one time. Usually you can get more records by editing a limit parameter or applying pagination, please check this article for more info: Pagination Handling
Such a great tool, very impressed with the ease of use and utility. Thumbs up!
My API returns field names that come nested in two collections called "d" and "results" so each fieldname is pre-pended by d.results. (e. g. d.results., d.results. , d.results.). I would like to instruct the application to omit the string "d.results." for all the field labels. Is there a way to batch edit all resulting field names so that I don't have to edit 50+ fields individually? There is a similar functionality in PowerQuery when importing fields.
I just saw and tried the "Truncate Headers" option and that did the trick! Please disregard --> once again - very impressed.
Thank you for the nice words, I appreciate it! Glad you're all sorted now.
Is there a way to show the item of orders? Like I have 3 items in one order response data showing all item names but when I try to edit fields there is no item field there to get the ordered item. Can you please suggest
You should see all fields in the response, if not can you please refresh fields in case you've filtered them out?
hello,
I'm trying to pull the data and it's missing 1 data field that I need the most, while pulling everything else. I can't seem to figure out how to add it in "Edit Field". Could you please help suggest?
Thanks!
Hey Anh, can you please click "Show raw response" to see what the API server has sent back?
If your field is visible there, but not in the field editor, click Output options > JMESPath and see if there's a JMESPath expression that could be filtering the results.
If your field is not in the raw response, then the request URL probably needs to be edited, since the field isn't coming back from the server. In that case you'll need to go back to the API docs, or feel free to message support if you'd like me to take a look.
Hi,
I may be missing something obvious here, but I can't seem to add a custom field. I am trying to make this usable to connect etsy and fb commerce, which needs specific titles and data. While most of them are very close and obvious, I can't seem to add a new column whether using the auto Etsy style or custom. Greatly appreciate all of the hard work done here though!!
Hey Kirsty! Thank you 🙂 Fields can't be added through the field editor; it's just a way to view & edit the fields that the API sends back after the request has already been processed. If you'd like to add fields to the response, you would need to do that through the API request itself, as that's where you tell the API which fields you'd like to retrieve. I hope that clarifies a bit, or feel free to message support if you'd like to work through this together.
Hey Ana - I have a paginated request - unfortunately the field editor isn't showing all fields.
I suspect it is because it only shows the first page of results, therefore it isn't showing any entries that have non-empty values for those fields
Any suggestions or other hypothesis around it isn't showing fields? I don't have any filtering on
I see, yeah, the field editor will only show the first page of data. To work around this I suggest setting it to run for a page that contains all the columns you need (e.g. by editing the date range) and saving the field mapping, then re-running your request. Another option would be to manually create a field mapping by downloading the request file and adding in the fields you need following the pattern that you see in that file. If you need help with that just let me know!
Hi Team, very useful tool well done.
Quick Q, am using the tool to retrieve Google-matching results based on specific text queries in Column-A but my challenge is for some inputs there are multiple responses in Column-B and API-Connectors returns multiple responses correctly (i.e. Input cell A2 returns Response1 in B2 & Response2 in B3) but results get mis-aligned when there are multiple responses. Meaning if passing 200 inputs and there are 250 responses I'm having to manually re-link responses to inputs.
Is there a way to a) set-up response layout such that if one input returns multiple responses that it duplicates input-row, or b) to include 'input-cell-value' in every response, or any other recommendation
Here are a couple of ways to preserve the association between each input and its response:
1) tick the "include request URL" option so that you know which request was associated with each response.
2) Use JMESPath or Flatten fields to headers option to flatten each response into a single row.
The formula function within Edit Fields is excellent. However, when I use =IMPORTAPI the formulas appear to be ignored.
MID("$1",2,3) on [LAX] result gave me MID"[LAX]",2,3
It's fine when I run the request manually.
Thanks, Nick. You're right, formulas don't work with IMPORTAPI, because custom functions return only data. I discussed this with our developer, and there doesn't seem to be any good way to force Google to set a formula for a cell in the case of custom functions. Sorry for the inconvenience but I'm afraid you'll need to run these requests without formulas, and then apply those MID formulas yourself, i.e. in a separate column in your sheet.
I just updated the documentation above to make this limitation more clear.
I had manually added a new column to be the last column (after all columns are mapped) in order to record the date and time when a new row is inserted (I can't use the Api connector's built in Add Timestamp since it updates the time on every run). But every time I run a request, my new column is removed from the sheet, likely because it's not part of the field mapping. Is there a way to have this column remain?
Hi - can you please add the custom column to the left of your data destination (e.g. in Column A), and set the data destination to a cell to the right of your custom column (e.g. Column B)?
https://mixedanalytics.com/knowledge-base/set-data-destination/#num2