API Connector Documentation
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.
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_id | amount_paid | amount_refunded |
| AAA | 25 | 0 |
| BBB | 15 | 0 |
| CCC | 32 | 0 |
After running a merge with "order_id" selected as the merge key, the new data set might look like this:
| order_id | amount_paid | amount_refunded |
| AAA | 25 | 0 |
| BBB | 15 | 15 |
| CCC | 32 | 0 |
| DDD | 26 | 0 |
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
- The merge key should correspond to a field returned in the API response, not a field that's been manually added to the sheet
- Avoid the "truncate headers" option as this changes header keys and may prevent a match
- 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.)
API Connector also provides a "Remove duplicate rows" option (under Output options). This removes data from within the current response set. Note that it doesn't compare and dedupe the response to data already in the sheet.
Only downside with the Append. All date is imported. not just the new ones
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.
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!
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.Update: You can now also set a starting cell to avoid overwriting your saved formulas.
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.
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.
Hi,
is it possible to get the same output order every time when I run the API?
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. You could sort data, but it's always possible there will be new values in the list that prevent data from landing in exactly the same position. In general, if you want data to stay in a fixed position, you would separate out the data level from the presentation level by creating a column in a second sheet containing those fixed values, and using 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.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.
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.
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.
Thank you for the suggestion, it's on the list and I should have some updates soon.
I meant left to right.
Update: added a "Clear sheet" option that you can toggle off.
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?
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 by separating your display sheet from the raw data, like in this template: https://mixedanalytics.com/knowledge-base/import-coinmarketcap-data-to-google-sheets/#template
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.
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 😀
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.
Hi is it possible to display text if there isn't data returned?
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.
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?
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).
I added a schedule to add new data every day. Unfortunately, the whole table is updated. This does not allow us to edit the data, to highlight certain cells with colors, because after the update all this disappears. I found an output mod where there is an append mode that should only add new values below. When I run the query, all these values are duplicated from the bottom again.
Is there any way to keep the visual editing of the tables after the update?
P.S. Sorry for my bad English
Hi Andrii, by default all data in the sheet is cleared in overwrite mode, including formats.
To retain formats, you have a couple of options:
1) use your API sheet as the raw data source, and create a second sheet which contains your display table. You can set your formats in the display sheet. If your raw data is in Sheet1, you can import all the data into your display sheet with a formula like =query(Sheet1!A:Z,"select *")
2) turn off the "clear sheet" option (under Output options). Then neither formatting nor anything else will be cleared
As for Append mode, it doesn't automatically know which data is new or not new. It simply takes the data retrieved by your request and appends it to the end of your data set. To fetch new data only, you'd need to configure your request so that it only fetches new data (e.g. by setting a date parameter or minimum ID or similar), or you can use Merge mode to merge in new data.
I recently discovered a little bug when using the Append method for the Output mode. It seems as if it checks for the first empty row instead of (last row of the sheet) + 1. When making new requests even with Append selected, the rows are overwritten from the first empty row onwards, when it should've appended it at the last row of the sheet instead.
Thanks, I see what you're saying and have confirmed the issue. I'll add this to our list of bugs to address. Empty rows are pretty rare though -- I think this may only occur in cases where pagination returns an empty final row. In any case, for now you should be able to address this by ticking the "Add timestamp" every row box. That will prevent any totally empty rows from appearing in your data sheet, and avoid the issue of appended data starting before the end of the sheet.
Hi Ana, Is it possible to use the Output mode MERGE based on 2 fields e.g. Order ID and Order Status? We want to only update the data with new Orders (Order ID) or the Order Status changes?
Currently the merge key can only accept a single value as a key, so the merge algorithm can't take into account 2 separate fields. I'm not sure I fully understand your use case here -- if you set the merge key to Order ID, then it will update the order status if it has changed, otherwise it will leave as is. Are you looking for something different?
Hi, is it possible to keep existing formatting?
If you untick the "Clear sheet data" option, formatting will be retained, though that setting will also prevent sheet data from being cleared. There's currently no way to keep JUST the formatting. We'll consider adding that option in the future, but for now I suggest keeping your data sheet and your presentation sheet separate, so you can keep all your formatting intact on a second sheet.
Is there a way for the merge mode to only update entries by first checking if the column for an entry is changed? Or does it only match new and old data by the merge key before updating all columns?
Merge mode only works by checking the merge key. When the merge key matches, all columns for existing records will be updated.