Print

Set Output Mode

Overview

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

outputmodes-selectmode

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Overwrite mode

Overwrite mode is selected by default. In this mode, new data will completely replace existing data. By default, headers will be printed out in the response (this can be toggled off under Output options).

When you use Overwrite mode, all data will be replaced below and to the right of the destination cell. If you'd like to use functions and formulas, place them to the left of the destination cell to avoid overwriting them when the data refreshes.

Append mode

Append mode will simply append new records to the existing table. Headers won't be printed out, and new records will be printed into the first empty row. (A row is considered empty if it contains no values or formulas below and to the right of the destination cell.)

When you use Append mode, it's a good idea to first map fields to columns with the visual field editor. This ensures subsequent data pulls will maintain the same structure.

Merge mode

Merge mode is the most complex and powerful mode as it involves selecting a unique merge key that joins the old and new data sets. This key determines whether new data should be added to the end of the sheet or used to update existing records.

When the merge key matches, existing records will be updated; otherwise, new records will be appended to the sheet.

Use case examples

  • Update order statuses over time (e.g. from "processing" to "paid"). Re-fetch orders and update their status without adding new rows to your sheet
  • Repeatedly update a table with month-to-date data without creating duplicates or re-fetching the entire set from scratch.


Data example

Consider this example data set of 3 transactions:

order_idamount_paidamount_refunded
AAA250
BBB150
CCC320

After running a merge with "order_id" selected as the merge key, the new data set might look like this:

order_idamount_paidamount_refunded
AAA250
BBB1515
CCC320
DDD260

Order BBB was updated, while new order DDD was added.

Merge key

When you select the Merge mode radio button, a "Merge key" dropdown will appear where you can select the merge key that identifies unique records in the table. Typically merge keys will be a date, an order ID, or some other kind of unique record ID.


Usage notes

  • Begin by clicking Edit fields to open the field editor and select the fields for the report. If you would like to rename any fields, do so now via the field editor.
  • Next, click Run to fetch an initial data set in overwrite mode. No merge key is needed for the initial run as nothing is being merged yet.
  • Once the initial data set is in the sheet, switch to merge mode and select a merge key. Hit the refresh icon next to the merge key dropdown to see all available keys.

Troubleshooting

If the API response is erasing existing data or appending to the end of the sheet rather than merging as expected, please double-check the following items:

  • The merge key should be unique.
  • The merge key needs to match in these 3 locations:
    • 1. the header row shown in the field editor
    • 2. the header row of the data table in the sheet
    • 3. the Merge key dropdown
      outputmodes-matching
  • The merge key should correspond to a field returned in the API response, not a field that's been manually added to the sheet
  • When working with merge keys, the format of data in the sheet and data from the API response must match. For example, if the Sheet shows “June 8, 2023" and the API sends back "2023-06-08", a match will not be found and response data for that date will be appended rather than updated.

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.

Merge mode will automatically avoid duplicates, as long as you select a unique key. (Though for performance reasons it is generally still a good idea not to re-fetch the entire data set.)

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.
      Update: you can now use merge mode to avoid duplicates.

      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.
      Update: you can now simply tick the "Remove header row" option.

      Reply
    • For columns, you can use the Edit fields function to keep them in the same order. For rows, 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.
      Update: You can now toggle off the "Clear sheet data" option.

      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
  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.
        Update: we now have a "force rows" option that can help transpose data from columns to rows.

    • I'm not sure if this is exactly what you're looking for, but if you tick the "add timestamp" or "add request URL" option, you'll see those values populate the row even if no other data is returned.

      Reply
  6. 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, re-open the field editor and drag them to the right of existing fields, or whichever location you like).

      Reply

Leave a Comment

Jump To