How to combine new data with your existing sheet: replace, append or synchronize
When you create a report using SyncWith, the entire API response is inserted into your Google Sheet. If you later refresh the report, there is new data from the API as well as existing data already in your sheet. SyncWith lets you specify whether or not to combine this new data with the existing data. If you choose to combine it, SyncWith also lets you specify how to reconcile existing rows with new rows.
You can configure this behaviour in the Advanced section of your report:
This is the default behaviour. When Replace contents of sheet is selected, any previous data from the connection is first erased from the sheet. Once the sheet has been cleared, the contents of the API response are inserted.
When Append new rows to end of sheet is selected, any previous data is left as-is. The contents of the API response are inserted immediately after the last row of data.
This mode is useful for accumulating data over time. For example, you might use the SERPHouse API to fetch the top organic results for a search term:
In append mode, each time you refresh, the current set of organic results will be snapshotted and appended to your sheet. If you configured this connection to run every day, you would then be able to monitor how the rankings for a given search term change over time.
When Update or add new rows as needed is selected, new data from the API response is merged into existing data. You will need to teach SyncWith how to recognize when data is entirely new versus an update of an existing record.
This is most useful when you are accumulating transactional data that has a sense of identity and ordering. Some common examples:
Shopify orders: orders have IDs and can be sorted by when they were last updated
Stripe charges: charges have IDs and can be sorted by when they were created
Analytics data: data belongs to a specific time interval (October 29, 2021; the week starting October 24, 2021; the month of October 2021) and can be sorted by time
The Stripe /v1/charges API returns the 100 most recent charges. If you wanted to accumulate all of your charges in a single sheet so you could analyze them, you could configure a report in Update or add rows as needed mode.
You must specify at least one column to use when determining if a row should be added to the bottom of the spreadsheet, or if it should update an existing row. In this example, Stripe charges are uniquely identified by the id column.
Now, each time you refresh, SyncWith will intelligently determine which rows overlap with your existing rows. This allows you to synchronize all of your data without getting duplicate data in your spreadsheet.
You might find it useful to first configure your connection to fetch all of its data. This may take a very long time. Once the data has been fetched, you can use API parameters to fetch a smaller subset of the data, and rely on SyncWith to incrementally update your sheet.
Many APIs can be configured to fetch slices of data. Fetching manageable slices of data can be a good way to backfill old data, or to keep up-to-date with new data. Common strategies include: fetching since the last ID, fetching a single day of data, fetching all data updated since a given date
You can configure this behaviour in the Advanced section of your report:
Replace contents of sheet
This is the default behaviour. When Replace contents of sheet is selected, any previous data from the connection is first erased from the sheet. Once the sheet has been cleared, the contents of the API response are inserted.
Append new rows to end of sheet
When Append new rows to end of sheet is selected, any previous data is left as-is. The contents of the API response are inserted immediately after the last row of data.
This mode is useful for accumulating data over time. For example, you might use the SERPHouse API to fetch the top organic results for a search term:
In append mode, each time you refresh, the current set of organic results will be snapshotted and appended to your sheet. If you configured this connection to run every day, you would then be able to monitor how the rankings for a given search term change over time.
Update or add new rows as needed
When Update or add new rows as needed is selected, new data from the API response is merged into existing data. You will need to teach SyncWith how to recognize when data is entirely new versus an update of an existing record.
This is most useful when you are accumulating transactional data that has a sense of identity and ordering. Some common examples:
Shopify orders: orders have IDs and can be sorted by when they were last updated
Stripe charges: charges have IDs and can be sorted by when they were created
Analytics data: data belongs to a specific time interval (October 29, 2021; the week starting October 24, 2021; the month of October 2021) and can be sorted by time
The Stripe /v1/charges API returns the 100 most recent charges. If you wanted to accumulate all of your charges in a single sheet so you could analyze them, you could configure a report in Update or add rows as needed mode.
You must specify at least one column to use when determining if a row should be added to the bottom of the spreadsheet, or if it should update an existing row. In this example, Stripe charges are uniquely identified by the id column.
Now, each time you refresh, SyncWith will intelligently determine which rows overlap with your existing rows. This allows you to synchronize all of your data without getting duplicate data in your spreadsheet.
Tips for update or add new rows mode
You might find it useful to first configure your connection to fetch all of its data. This may take a very long time. Once the data has been fetched, you can use API parameters to fetch a smaller subset of the data, and rely on SyncWith to incrementally update your sheet.
Many APIs can be configured to fetch slices of data. Fetching manageable slices of data can be a good way to backfill old data, or to keep up-to-date with new data. Common strategies include: fetching since the last ID, fetching a single day of data, fetching all data updated since a given date
Updated on: 04/27/2024
Thank you!