Normally when running reports in SyncWith for Google Sheets all the data (rows) in your spreadsheet are replaced each time the report runs. This means your data is always up to date, and accurate, but it also means that historical data is not maintained, instead only the data from the latest report run is shown. Normally this is what people want, and it works well, but in some cases you might need something different.

With SyncWith's built in connectors such as Facebook Ads or Google Analytics etc, we offer three modes that you can use:

These settings don't apply to any of our other products such as our connectors for Looker Studio, or our Shopify app Reports & Export.

This is the default mode, and it works for most users, its what we recommend. In this mode each time your report is run, all the rows in the sheet are replaced (or deleted) and the data we fetched for your report are inserted into the sheet.

For example if you ran a report to see campaign performance for Facebook Ads for the last 90 days, and scheduled that hourly, then SyncWith would run your report every hour, then each time the report is run, we'd fetch the data from Facebook directly for the last 90 days at that moment, and then effectively completely replace the data in the sheet. This would mean at any given point you'd have data for the last 90 days, and you'd be losing the older data as time moves forward, eg the last day from 90 days ago today won't be there any more tomorrow.

This mode is designed to help you maintain historical data. Imagine in that case above you wanted to always have fresh data from the last 90 days from Facebook Ads, but you also wanted to accumulate old rows in your sheet, so that for example in 2 years from now you could look back and see the last 2 years of data.

The way this mode works is automatic. Each time the report is run, SyncWith will identify which rows are new, and which existing already. New rows will be appended at the bottom, and existing rows will be updated with new values.

SyncWith determines itself which rows are matching or new, by comparing the values of your dimensions. Dimensions are fields like campaign, country, placement, keyword, day, date. Other fields are called metrics, metrics typically count something, like clicks, spend, reach, impressions etc.

So for example if you setup a Facebook Ads report using the mode Update or add rows as needed, then each time your report runs SyncWith will identify which rows are new based on your dimensions, and append those, and then identify which rows already exist, that have the same dimension values, and update the metrics for those.

As a simple example, if you ran a Facebook Ads report for the fields Date, Country and Clicks, and set the mode to Update or add rows as needed, and set the date range to Yesterday, then the first time the report is run you'd get one row per Date per country for yesterday and how many clicks for each row. When the report runs again, SyncWith would update existing rows if the clicks for any country and date changed, or if there are new countries or dates in the report it would append those instead.

One limitation of the Update or add rows as needed mode is that SyncWith's sorting feature won't work as expected. If you've enabled a sort, then when SyncWith discovers new rows, these are still inserted at the bottom of your existing data, and the data set is not re-sorted, this means the newly appended rows will be sorted as you setup but the entire set of rows in the sheet won't be sorted by SyncWith.

This mode is not useful in almost any cases, we do not generally recommend using it. What this append mode does is literally what it says, each time your report is run, all the data from that report run are appended blindly to the bottom of your data. This lets you accumulate new rows, but often results in duplicates. To use this without getting duplicates is hard, you'd have to rely on your data source and/or scheduling to ensure that you get unique rows each time you run a report.

One example of when this is useful is if you want to pull in daily keyword rankings from Google Search console. If you setup your report to always pull in today's rankings, and you schedule the report to run once a day only, then each day today's rankings could be pulled in and appended to the bottom of the sheet. Note this is a slightly contrived and hard-to-use example, but append mode is here for this few cases where you might need it.

SyncWith has a very technical feature not used by many of our users that lets SyncWith connect to an arbitrary 3rd party API. This requires some technical knowledge to use, and isn't usually what we recommend to users. However, if your business needs access to some data that SyncWith doesn't have a built in connector for then providing you have some technical knowledge this can be a useful way to connect SyncWith to new sources it wasn't designed to.

Below we'll discuss how this feature of updating or appending rows works when used with a connection to a 3rd party api, this information below doesn't apply to when you use a built in connector within SyncWith such as Facebook Ads or Google Analytics.

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:

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.