Search API Connector Documentation

Print

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 columns in your report.

Why Use The Field Editor?

  • simplify reporting by displaying only the fields you need
  • 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
  • remove headers
For more advanced filtering functionality, please check out JMESPath filtering.

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-img1

Edit Fields

For each response field, you have several options:

Filter/Keep fields

Choose whether to display or hide 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.

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.

If you'd like to remove headers entirely, replace each field name with a single space.

Re-arrange Fields

By hovering over a field header, you can drag and drop it to a new location.

Remove Headers

To remove headers from your table, replace each header name of your report with a single space.

When you next run the report, headers will be empty.

Reset Field Settings

When you change your report style, enter a new URL, or if your API starts sending new or different fields, you need to reset the field mappings to match the updated field structure.

You may also want to reset any changes you've made to column names or positions.

To reset your fields, click Reset all.

After resetting your field mappings, you can click Save to save the current mapping, or simply click Close to continue without a fixed mapping.

View API Response

To view the raw response data from the API, click the Show raw response button.

Troubleshooting

  • For paginated requests, the field editor will only show records from the first page of results.
  • If you change the report style (e.g. from single row to grid) after saving a field mapping, you will be prompted to reset any saved fields.
  • Features like adding timestamps, adding request URLs, and truncating headers are managed independently of the field editor, and can be toggled on and off through the sidebar.
  • If you apply a JMESPath expression, the field editor will display the post-JMESPath output.

27 thoughts on “Edit Fields”

  1. 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.

    Reply
    • 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.

      Reply
    • 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.

      Reply
  2. 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?

    Reply
    • Unfortunately the field editor won't help here 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).

      Reply
  3. 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.

    Reply
    • 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.

      Reply
  4. 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

    Reply
    • 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.

      Reply
  5. 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?

    Reply
    • 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?

      Reply
  6. 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

    Reply
    • When you use Append mode, the header row should be automatically removed. Alternatively, you can delete header names to remove headers, please check the section above called 'Remove Headers'.

      Reply
  7. 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.

    Reply
  8. 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

    Reply

Leave a Reply to Hari Cancel reply

Jump To...