Search API Connector Documentation

Print

Set Output Mode

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.

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.

Previous Set Data Destination
Next Set Report Style

22 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
  1. 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
  2. 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
  3. 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.

  4. 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, we’re planning a merge option that will address this, but for now 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
  5. 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.

    • Sorry, there is not. Could you explain a bit more about what you’re looking for? May be able to build an option for this into a future release.

      Reply

Leave a Reply to Tom Cancel reply

Table of Contents