Set Output Mode
The API Connector add-on for Google Sheets provides three different output modes: overwrite, append, and merge. To access, click Output options.
- Before You Begin
- Overwrite mode
- Append mode
- Merge mode
- Duplicate Data
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
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 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 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.
Consider this example data set of 3 transactions:
After running a merge with "order_id" selected as the merge key, the new data set might look like this:
Order BBB was updated, while new order DDD was added.
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.
- 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.
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
- 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.
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.)