# Migrate from legacy spreadsheet metrics to datasets

## Why migrate from legacy spreadsheet metrics

The legacy Google Sheets and Excel metric builders are being deprecated, along with all metrics created using them. Spreadsheet-based reporting is being consolidated into [datasets](/understanding-datasets) to provide a more scalable and reliable way to work with your data.

Legacy spreadsheet metrics sync by downloading your file and reading cell values for each metric. In some cases, Databox stores a local copy of the file, such as with Google Sheets. In other cases, the file must be downloaded at every sync for every metric. If you have 50 metrics, that means 50 separate file reads during each refresh. This increases processing time, raises the risk of sync failures, and makes maintenance more complex.

With Datasets, syncing is tied to the dataset instead of individual metrics. Your spreadsheet is processed once, and a single dataset can power dozens or even hundreds of metrics. This reduces sync load, improves reliability, and simplifies long-term management.

By migrating to datasets, you can:

- Reuse the same dataset across multiple metrics
- Analyze data across multiple dimensions
- Apply filters and drill-downs directly in visualizations
- Create custom calculated columns
- Merge datasets from multiple data sources
- Export raw data when needed
- Use AI-powered insights with Genie and [Databox MCP](/query-and-analyze-your-data-using-ai-tools-and-databox-mcp)
- Improve refresh performance and maintainability


In-app migration tools will be available soon to help identify legacy data sources and replace legacy spreadsheet metrics. You may still choose to migrate manually based on your timeline or requirements, but the tooling will provide a more streamlined and reliable migration experience once available.

## Key considerations for migration

### What's changing

Previously, you built metrics directly on top of your spreadsheet data sources. Each metric independently read data from the spreadsheet during sync.

Now, a data preparation step is introduced between your spreadsheet and your metrics. You first create a dataset from your spreadsheet, and then build metrics from that dataset.

One dataset can power multiple metrics, and a single sync refreshes all metrics built on that dataset. Column configuration and data structure are now managed within the dataset instead of inside the metric builder. The metric builder interface also reflects this updated workflow.

### What stays the same

Your underlying spreadsheet data remains unchanged. Historical data, metric definitions, and existing content are preserved and remain accessible.

## Manual migration steps

The biggest benefit of datasets comes from consolidating queries.

Instead of migrating each metric 1:1, look for patterns where multiple metrics query the same table with different filters.

### Step 1: Identify legacy spreadsheet metrics

Before migrating, take inventory of your spreadsheet-based metrics:

1. Go to [Metrics > Custom Metrics](https://app.databox.com/metrics/custom-metrics).
2. Filter by spreadsheet data sources (Google Sheets, Excel).
3. Look for metrics marked with a ⚠️ warning icon.
4. Open each metric and record its cell or range reference type.


To determine the reference type, review the cell or range defined in the metric settings:

| Reference format | Reference type | Preparation required | Migration approach | Example |
|  --- | --- | --- | --- | --- |
| A:A | Entire column range | None | Direct migration from dataset | `Sheet1!A:A` |
| B10:B20 | Selected column range | Add `Row Number` column | Filter by `Row Number` (X to Y) | `Sheet1!B10:B20` |
| C3 | Single cell | Add `Row Number` column | Filter by `Row Number = X` | `Sheet1!C3` |
| 10:10 | Row range | Transpose data (built-in option or manual transpose) | After transposing: migrate as **Entire column** or **Selected range** | `Sheet1!10:10` |
| Sheet1!A:A + Sheet2!B:B | Cross-sheet reference | Create multiple datasets (or a combined tab) | Merge datasets or consolidate into one tab | `Sheet1!A:A + Sheet2!B:B` |


### Step 2: Prepare the sheets

If you identify one or more metrics that use references other than entire column ranges, you can restructure your spreadsheet before migrating. This may be simpler than recreating the logic using dataset modifications.

#### For selected column ranges and single-cell references

To standardize your data, add a helper column that generates row numbers.

It is recommended to add this as the **first column** in your sheet to keep the structure consistent.

1. **Insert a new column** as Column A.
2. In the first row, **enter the header** `Row Number`**.**
3. In the second row, add one of the following:
**Option 1: Copy-down formula**
  - Enter: `=ROW(A1)`.
  - Copy the formula down for all rows that contain data.
**Option 2: ARRAYFORMULA (recommended for Google Sheets)**
  - Enter: `=ARRAYFORMULA(IF(B2:B<>"", ROW(B2:B)-1, ""))`.
  - This automatically generates row numbers for all populated rows without manually copying the formula.


#### For row range references (optional)

To transpose your data before creating the dataset, you can either use a formula for a dynamic conversion or perform a one-time manual transpose.

If you want the transposed data to update automatically:

1. **Create a new tab** in your spreadsheet (for example,`Transposed_Data`**).**
2. In cell A1 of the new tab, **enter:** `=TRANSPOSE(Sheet1!A:Z)`.


If you prefer a one-time conversion without formulas:

1. **Select your row-based data.**
2. **Copy the selection** (Cmd+C / Ctrl+C).
3. Go to the destination cell.
4. **Right-click > Paste Special > Transpose**.


You can transpose the entire sheet during dataset creation without modifying the original file. For details, refer to [Step 3](#step-3-create-datasets) below.

#### For cross-sheet references (optional)

If your legacy metrics reference multiple tabs, you can combine the required data into a single tab before creating your Dataset.

1. **Create a new tab** in your spreadsheet (for example, `Combined_Data`).
2. Use formulas to pull data from other tabs.


If the tabs share the same structure and should be stacked vertically:


```
={Sheet1!A:B; Sheet2!A:B}
```

This appends the rows from both tabs into one continuous table.

If you need to reference specific columns:


```
=Sheet1!A:A  
=Sheet2!B:B
```

Ensure the combined tab has consistent column headers and a uniform structure across all appended data. Consolidation works best when the original tabs share the same column layout or represent different time periods or categories.

### Step 3: Create datasets

Each tab used to create a legacy metric must be recreated as a separate dataset.

In other words, **one sheet equals one Dataset**. If your metrics pull data from three different tabs, you will need to create three separate Datasets.

1. Go to [Data Manager > Datasets](https://app.databox.com/data-manager/datasets).
2. Click + **New dataset**
3. Select your spreadsheet data source and click **Continue**.
4. Choose the sheet that contains your data.
5. Enable the **Switch rows to columns** option if your metric is based on a row range and requires transposed data (optional).
6. Configure the **Header row** setting:
  - If row 1 contains column names, select **1 (default)**.
  - If your sheet does not contain headers, select **0 (A,B,C...)**.
7. Click **Preview data**to confirm the structure and formatting look correct and the data appears as expected.
8. Click **Continue**.
9. Select update frequency and click **Finish.**


### Step 4: Recreate metrics from the datasets

For each legacy metric:

1. Open the [dataset view](/view-a-dataset).
2. Click **+ Create new metric** on the right-hand panel.
3. Select the appropriate **measure**, **date**,and **dimension** columns.
4. **Add filters** to control which rows are included in the calculation (optional).
5. Click **Preview data** and compare the new metric with the legacy metric to ensure the numbers align.
6. Click **Save**.
7. Replace the old metric wherever it's used across your content to ensure everything continues working with the new dataset-based metric.


## Migration examples

### Entire column range metrics

Your legacy metrics pull data from an entire column, such as:

- Date column: `Sheet1!A:A`
- Value column: `Sheet1!B:B`
- Dimension column: `Sheet1!C:C`


entire-column-range-metric
To migrate these metrics, select the same **columns by name (or column letter)** andchoose the equivalent **aggregation function** in the metric builder to match the legacy setup.

### Selected column range metrics

Your legacy metrics pull data from a specific row range within a column, for example: `Sheet1!B10:B20`.

Datasets operate on entire columns rather than fixed row ranges. To replicate a selected row range, use a `Row Number` column as a filterable dimension.

selected-column-range-metric
To migrate these metrics:

1. Select the same **value**, **date**, and **dimension** column(s) in the metric builder.
2. Choose the equivalent **aggregation function** that matches the legacy metric setup.
3. **Add a filter group** with the following conditions:
  - **Column:** Row Number
**Operator:** Greater Than or Equal
**Value:** X
AND
  - **Column:** Row Number
**Operator:** Less Than or Equal
**Value:** Y


Replace **X** and **Y** with the starting and ending row numbers from your original range.

### Single cell metrics

Your legacy metrics pull data from a single cell, for example: `Sheet1!B3`.

Datasets operate on entire columns rather than individual cells. To replicate a single-cell reference, use a `Row Number` column as a filterable dimension to isolate that specific row.

To migrate these metrics:

1. Select the same **value**, **date**, and **dimension** column(s) in the metric builder.
2. Choose the equivalent **aggregation function** that matches the legacy metric setup.
3. **Add a filter group** with the following condition:
  - **Column:** Row Number
  - **Operator:** Equals
  - **Value:** X


Replace **X** with the row number from your original cell reference.

### Row range metrics

Your legacy metrics pull data from an entire row or a range of rows, for example: `Sheet1!10:10`.

Since datasets are column-based, row-oriented data typically needs to be transposed before building metrics.

After transposing the data:

- If **all resulting rows are in use**, follow the same process as described in [Entire column range metrics](#entire-column-range-metrics). Select the relevant columns in the metric builder and apply the appropriate aggregation.
- If **only specific rows are in use**, follow the same process as described in [Selected column range metrics](#selected-column-range-metrics). Use the `Row Number` column as a filterable dimension to isolate the required rows.


### Cross-sheet reference metrics

Your legacy metrics pull data from multiple sheets within the same spreadsheet, for example: `Sheet1!A:A + Sheet2!B:B`.

Datasets are created per sheet, meaning each tab must be configured as a separate dataset. Cross-sheet references cannot be recreated within a single dataset.

To migrate these metrics, use one of the following approaches.

1. **Merge multiple datasets (recommended)**
2.   1. Create a separate dataset for each tab.
  2. [Open one](/view-a-dataset) dataset and click **Merge data**.
  3. Select the dataset(s) to [merge](/merge-datasets) and configure the join using a common key (for example, Date or ID).
  4. Save and build metrics from the merged dataset.
3. **Consolidate data into one tab**
  1. Create a new tab that references the required sheets using formulas (see [Step 2](#step-2-prepare-the-sheets)).
  2. Create your dataset from the combined tab.
4. **Use Data Calculations**
  1. Create separate datasets and metrics.
  2. Use [Data Calculations](/create-a-custom-metric) to combine the metrics (for example, Revenue − Costs).


Choose the approach that best matches how your data is structured.

No. You can migrate metrics at your own pace before the deprecation takes effect.

Group related metrics and migrate them together as a consolidated dataset. This makes it easier to validate results and ensure everything works correctly before moving on to the next group.

If your date column is not detected during dataset creation, it is usually due to formatting issues in the spreadsheet.

Check that:

- All values in the column are valid dates
- The column uses a consistent date format
- There are no mixed text or non-date values


If automatic detection fails, manually select the date column during dataset configuration.

For detailed guidance on preparing your spreadsheet, see [this guide](/format-your-google-sheets-and-excel-files).

If your new dataset-based metric does not match the legacy metric, review the following:

- **Aggregation method changed** — Confirm the metric uses the same aggregation (Sum, Average, Count, etc.).
- **Different date range** — Make sure you are comparing the same time period**.**
- **Header row misconfigured** — Verify that the correct row is set as the header in the Dataset.
- **Missing row filter —** If the legacy metric referenced a range (for example, `B10:B20`), ensure the corresponding `Row Number` filter has been added.


 

Ask Genie
Get instant answers or help with your data using the in-app AI assistant.

Talk to an expert
For customers: Get help with your setup, strategy, or making the most of Databox.

Book a demo
New to Databox? See how it works and get guidance on getting started.

Send an email
Reach out to support for help with your account, data, or technical issues.