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
- improve Sheets performance by reducing data size
- re-arrange and rename columns
- modify data with Sheets formulas
- simplify reporting by displaying only the fields you need
- Before You Begin
- Open Editor
- Edit Fields
- Shift Fields
- Refresh Fields
- View API Response
Before You Begin
If you haven't already, click here to install the API Connector add-on from the Google Marketplace.
To enter the visual field editor, click Edit fields from the Create screen.
For each response field, you have several options:
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.
To rename fields, click the pencil icon next to the field name. This will produce a modal where you can rename your field.
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.
- 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.
- Hit Save fields and then run the request to apply the formula (it won't be reflected in the preview window)
- Force text to uppercase
- Convert text strings to number
- Return the first 4 characters of a string
- Divide by 100
- Convert UNIX epoch timestamp in seconds to Sheets date
- Convert UNIX epoch timestamp in milliseconds to Sheets date
- Convert US currency to European currency format
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.
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.
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.
- 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.