How to fix data mismatch issues in Google Sheets

Integrating Windsor.ai with Google Sheets enables seamless data analysis and reporting. However, discrepancies can arise due to various factors. This guide outlines common causes and provides step-by-step solutions to ensure data consistency.

Data mismatches between Windsor.ai data sources and Google Sheets can arise due to several factors on both sides:

• Incorrect settings (such as date ranges, filters, aggregation levels, or field selections)
• Invalid API key
• Data refresh issues (manual refresh not triggered or schedule not set)
• Schema mismatches (changes in field names, removed columns, or inconsistent data structures)
• Time zone differences between Windsor.ai data and Google Sheets display
• API and query limitations, including field availability and quota limits
• Data type inconsistencies (e.g., numbers formatted as text, missing values, or incorrect field parsing)

Windsor.ai supports two main ways of connecting data to Google Sheets: 

1. Using the Windsor.ai Google Sheets extension
2. Connecting Google Sheets from the Windsor.ai platform through a destination task

Each method has its own workflow and potential issues. Below are detailed explanations and troubleshooting steps for both approaches.

This method involves installing and using the Windsor.ai add-on directly within Google Sheets. 

Below are some data mismatch issues that may arise using this method and their resolution.

• Verify that the selected date ranges and filters are correct. 
• Check whether the target sheet name is correct.
• Confirm that the proper data accounts are selected.

Update these settings and click “Load Data” again.

If your API key is invalid or has expired, you have to go to Windsor.ai dashboards data preview, select “Google Sheets” as your data destination, and copy your valid API key from the instructions.

Re-enter this API key in Google Sheets via Extensions -> Windsor.ai -> Login.

If you are seeing outdated or missing data, you can do the following troubleshooting tricks:

• Enable schedule refresh in the Windsor.ai extension.
• Check whether the refresh rate is low according to your data needs. Select the right refresh rate.
• Reconnect the Windsor.ai extension and load data again to apply manual refresh.

Differences in selected field names or data types can lead to data mismatches. Take the following steps to troubleshoot these issues:

• Select all the fields for which you require data in the sheet.
• To refresh the fields, you can reconnect the Windsor extension.

This method allows you to configure the data push from Windsor.ai directly into a selected Google Sheet without the need to install the extension and obtain an API key.

Let’s review some frequent Google Sheets data mismatch issues that may arise using this method and their resolution.

If no data is shown in your spreadsheet, check the following things:

• Correct Google account permissions are given while connecting to Google Sheets through the Windsor app.
• The correct Google Sheet document is selected in the destination form.
• Verify that the ‘Test connection’ is successful.

• Verify that the selected date ranges are correct. 
• Select the data sources you want to pull data from in your spreadsheet.

If you are seeing old data in your spreadsheet, take the following troubleshooting steps:

• Check whether the schedule refresh is set according to your needs.

• Windsor caches data for the last 6 hours. You can refresh the cache in the data preview dashboard manually.

• Check if Windsor’s data preview is showing the proper data. If not, verify that the correct data sources are connected and selected.

You may face some other common Google Sheets data mismatch issues when using the Windsor.ai connector. Let’s take a look at them along with their troubleshooting steps.

Timestamp misalignments between Windsor and Google Sheets can lead to inaccurate numbers or missing data when comparing metrics across platforms.

How to fix that:

• Match time zones across platforms: Ensure both Windsor.ai and Google Sheets (or your local system) are using the same time zone to avoid data drift.
• Understand Google Sheets behavior: Google Sheets often reflects your system/browser time zone. If your data source uses UTC (which Windsor.ai does by default), consider adjusting timestamps in your formulas or query parameters.
• Be cautious with date aggregations: Metrics grouped by day or hour can shift when time zones don’t match. Confirm you’re interpreting the data with the correct time context.

Certain Google Sheets data mismatches may stem from the limitations of the underlying APIs or constraints within how the data is queried or displayed in Windsor and Google Sheets.

How to fix that:

• Check Windsor.ai API/data sources/refresh quotas: If you’ve reached your API/Data Sources/Refresh usage limits or are being rate-limited, your data may fail to load completely or update reliably. You can resolve these issues by adapting the correct windsor.ai plan for your needs.

• Check Data Sources API Limits: Please note that the data sources you connect to may have their own data availability and API rate limits. It’s important to review the limitations of each source to set accurate expectations for data retrieval and performance.
• Be aware of Google Sheets limits: A spreadsheet can handle only a certain number of rows and cells. If you’re working with large datasets, simplify your query by selecting fewer fields or narrowing the date range. Here are the limitations that you should consider:
  • Total cells per spreadsheet: Up to 10 million cells.
  • Maximum columns per sheet: 18,278 columns (up to column ZZZ).
  • Maximum rows per sheet: Depends on how many columns you use.
    (e.g., ~5 million rows if you only have 2 columns).
  • Maximum sheets per spreadsheet: Up to 200 sheets per spreadsheet.
• Avoid overly complex queries: Minimize the use of advanced filters, joins, or calculated fields in a single query, as they might increase load time or lead to incomplete data retrieval.
• Handle pagination correctly: If your dataset spans multiple pages in Windsor.ai’s API, ensure pagination parameters are correctly configured to fetch the full result set.

By following these troubleshooting steps, users can resolve common data mismatches when streaming data to Google Sheets via Windsor.ai.

If the issue persists, contact our customer support for further assistance.

Please, follow this quick guide to effectively report on your data discrepancy: https://windsor.ai/documentation/how-to-report-data-integrity-mismatch-issues. In this format, our dev team will be able to identify the problem much quicker.

Usually, investigating data discrepancies takes time (1-3 days), so it’s better to organize the report and send it to [email protected]. We’ll take a detailed look and reply to you as soon as we find a resolution.