Search API Connector Documentation

Print

Set Output Mode

Last Updated On
byAna
< Knowledge Base

Overview

The API Connector add-on for Google Sheets provides two different output modes: overwrite and append. To access, click on the 'Output options' button.

api-connector-overwrite-append

Overwrite mode is selected by default. In this mode, the output of your request will appear in your destination sheet and cell, overwriting any data that is already there. This mode will also include the headers associated with your data set, in the first row. Sheet data will be cleared to the right and below the response.

Append mode will append results to the first empty row, and won't print out any headers. When you use Append mode, first map fields to columns with the visual field editor, otherwise data may get misaligned between pulls.

Duplicate data

API Connector will pull in all data returned from the API, so when you use append mode, you generally need to set parameters to ensure you're not pulling in duplicate data. Most APIs allow you to set a query string that filters by date or ID, which can be used to limit data refreshes to just new data. One useful technique is to reference a cell containing a date to automatically retrieve and append fresh data on a schedule.

24 thoughts on “Set Output Mode”

    • That's right. However, you should be able to get new records by adjusting your URL, since most APIs let you add query strings to retrieve records after a certain ID or date. You can also base your URL on a cell to make these query strings dynamic so you don't need to manually update your URL each time.

      Reply

  2. Hi, is there a way to make the output start in another cell different from A1? I have a few columns that I like to add to either the left or the right, to show weekday and weeknum. Thanks!

    Reply

    • Hey Loris, sorry, there is not. I suggest using the API response as your source data sheet, then pulling the data into a second summary sheet where you can apply any data transformations. A formula like =QUERY(Sheet1!A:Z) will pull in all the data from one sheet to another, and you can place that function in any column you want.

      Reply

  3. Hi! Is it possible to overwrite a cell without header? I would like to update the cell with with a json field value. Append returns exactly what I need, but not in the same cell.

    Reply

    • If you click Edit Fields before you run your request, and then erase all the field names (open the field editor, click the pencil icon next to each header, and replace it with a single space), it will print out into your sheet without headers.

      Reply

    • Probably not. We print out the data as the API sends it. Some APIs have a "sort" function but even in those cases, it's possible they'd add new values to their list that would prevent data from landing in exactly the same position. In general, if you want data to stay in a fixed position, you would create a column in a second sheet containing those fixed values, then use a VLOOKUP (or similar) function to pull in the associated data. Then it doesn't matter how the API sends back the data as your report will keep everything in the same place.

      Reply

  5. When I set my Query to Overwrite its erase everything in the columns going to the right and all rows below.

    How do i get simple print data to defined cell without it erasing surrounding data.

    Reply

    • We clear data to the right and below since most people find that preferable, but we will add an option for that in the future. For now you can either send your data into a source data sheet and then use Sheets functions like VLOOKUP to pull the data into the specific cell you want, or you can check out the IMPORTAPI custom function.

      Reply

      • Just to second this, ideally this would be a checkbox in the output options since I'm trying to import a lot of data and vlookups will increase overhead and also make the file more messy than it needs to be.

        In the meantime, I guess it can be worked around by making sure my requests are scheduled from right to left.

  6. Hmm, either this API connector is rather limited in usefulness, or I am missing something. I am trying to create a google sheet with a set of crypto currencies from Coinbase. Coinbase API has a request for pulling a price. For example:
    GET:
    https://api.coinbase.com/v2/prices/ADA-USD/buy

    I need to configure an API request for each currency. However, API connector will wipe-out /overwrite an entire sheet of values on each run. Therefore, I would need to find a way to force the API connector queries to run in order from top to bottom, and never try to add a queries in the middle of the sheet. This is really, really weird implementation. This would mean I need to create a new sheet/tab for each crypto. Appending the output would also not work because this would constantly change the sell of the current currency value. Am I missing something here? Or is API Connector just that limited?

    Reply

    • If you want to run queries from top to bottom, you can list out all the URLs and cycle through them with a multi-query request, or just use a different crypto API that provides data endpoints for multiple coins at the same time (most of them do, as far as I know Coinbase is the only one that doesn't).
      If your question is more about keeping data in order once you pull it, you can do this with Sheets functions like VLOOKUP. I've made a few preset templates that use these functions to keep crypto data in order, e.g. here.

      Reply

  7. Is it possible to transpose the JSON output as part of the configuration? I am working with single rows of data that are very long - too long for the maximum columns Google Sheets allows.

    Reply

    • Oh, not the maximum. Figured out how to add more, phew. Still, it would be super useful to see the data as a column rather than a huge row 😀

      Reply

      • There's no direct "transpose" option, but you can often transpose output by using a JMESPath expression.
        If you'd like to message support with a sample of your JSON, I'll be happy to take a look and let you know whether JMESPath will be able to help here.

  9. Hi - I'm pulling some data from Airtable into a source data sheet, and I have other sheets where I build dashboards based on the source data.
    When we add a column to Airtable, it usually changes the column arrangement of the source data sheet, which in turn messes up our dashboards as the formulas are no longer correct.
    Is there a way to make sure that any new columns added to the source sheet get put in the furthest right empty column?

    Reply

    • Not exactly, but you can use the field editor to set your column order and lock them in place. Then, even if new fields are added to Airtable, they won't be displayed in your Google Sheets, and won't affect your formulas. (If you do want to add them in, you'll need to refresh your fields and re-create the report).

      Reply

Leave a Comment