Ensure reliable data sync from spreadsheets in Databox

Metrics built on datasets connected to Google Sheets, Excel, or similar files can break if changes are made directly in the source file. This article explains why these issues occur, the most common scenarios that cause them, and how to fix or prevent metric errors related to column names and schema changes.

Column structure and metrics

When you create a dataset, the system generates a schema based on the structure of your source file. Column names act as unique identifiers, and metrics rely on those identifiers to calculate values correctly.

If you change column names or data structure directly in the source file, the dataset schema updates on the next successful sync. Existing metrics, however, are not updated automatically. Because the system cannot reliably determine what changed in the source file, it cannot safely update metrics that depend on those columns.

This can result in broken metrics, missing columns, or incompatible aggregations.

Common scenarios that affect metrics

Metric issues most often occur when changes are made directly in the source spreadsheet, outside of Databox. The sections below explain common scenarios that impact metrics, how they affect your data, and best practices for fixing and avoiding issues.

Column names are changed

This scenario occurs when column headers are renamed in the source spreadsheet after metrics have already been created. On the next successful sync, the dataset updates to the new column names, but existing metrics continue to reference the original column names.

• Symptom: You may see the error <column_name> is not found in the dataset schema.
• Root cause: Metrics rely on column identifiers that are created from header names. When a header is renamed, the identifier changes, but metric definitions are not updated automatically.
• Solution:
  1. Open the affected metric.
  2. Update the metric definition to use the correct column from the dataset.
  3. Save the metric to rebuild it using the updated schema.
• Best practices:
  • Avoid renaming column headers after metrics are built.
  • If renaming is required, review and update any related metrics immediately after the change.
  • If column names are expected to change frequently, consider configuring the dataset to use row 0 as the header row.

Using row 0 as the header row

When row 0 is selected as the header row, column identifiers are based on column position (A, B, C, and so on) rather than header text. This allows column names to change in the source spreadsheet without breaking existing metrics.

Column names and column order can be modified later in Databox through the dataset editor, without changing the source file.

Data is temporarily removed

This scenario occurs when scripts or automations update a spreadsheet by temporarily clearing all values before inserting new data. If a data sync happens while the sheet is empty, the dataset schema may be re-detected incorrectly.

• Symptom: You may see the error: Selected aggregation 'SUM' is not compatible with the selected measure column.
• Root cause: When the sheet contains no data during sync, column types may default to text. Metrics that rely on numeric columns then become incompatible with their configured aggregations.
• Solution:
  1. Ensure the sheet contains valid data again.
  2. Refresh the dataset so the correct column types are detected.
  3. Open the affected metric and trigger a refresh by saving it or adjusting an advanced setting, such as the date range.
• Best practices:
  • Avoid update processes that leave the sheet empty during sync.
  • If you use scripts or automations, ensure data is written continuously or that updates occur outside of scheduled sync windows.

Frequently Asked Questions

Is it safe to reorder columns in my sheet?

Yes, as long as column names remain the same. Reordering columns does not affect metrics unless you are using the row 0 header setup.

Why don’t metrics update automatically after column changes?

The system cannot reliably determine what changed in the source file, so it cannot safely update metric definitions without user input.

Still need help?

Visit our community, send us an email, or start a chat in Databox.