How to format Google Sheets and Excel files

In order to effectively sync your Google Sheets and Excels data with Databox, there are guidelines to follow when formatting your files.

You can format your data in columns, which would allow you to use the Wizard (like Metric Builder for Google Sheets Wizard and Metric Builder for Excel Wizard) when creating Custom Metrics. This is the easiest way to get set up with Databox and is the recommended setup.

If you prefer, you can also format your data in rows. While you wouldn't be able to use the Wizard, you can still manually set up Custom Metrics to sync data formatted this way with Databox.

Pro Tip: Dates must be formatted in a specific way in order to successfully sync data with Databox. This includes information on the day, month and year that the Metric value should be pushed to. Dates in your file should be formatted as mm/dd/yyyy, dd/mm/yyyy, mm.dd.yyyy, or dd.mm.yyyy.

Learn more about formatting your Google Sheets and Excel Dates.

How to use Columns

It's recommended that you use columns in your files so that you can use the Metric Builder Wizard to set up your Custom Metrics.

If you're only storing dates and Metrics (i.e., numerical values), you'll be creating Standard Metrics in Databox. In this case, it is recommended that your file is formatted with the Date in Column A, and Metric (numerical) values in Columns B, C, etc., repeating for as many Metrics as you have to store.

Individual entries should be listed in the appropriate column.

If you're storing dates, Metrics (i.e., numerical values), and Dimensions (i.e., categories that break up or add further context to the metric values), you'll be creating Dimensional Metrics in Databox. In this case, it is recommended that your file is formatted with the Dates in Column A, Dimensions (strings) in Column B, and Metrics (numbers) in Column C.

All Dimension columns should be next to each other (i.e, Columns B, C, D), and all Metric columns should be next to each other (i.e., Column E, F, G).

How to use Rows

It's recommended that you use columns in your files so that you can use the Metric Builder Wizard to set up your Custom Metrics. However, if this is not possible or if you prefer to use rows, follow the guidelines below to set up your files. In order to build Custom Metrics, you will need to use the Metric Builder Manual Setup option (like Metric Builder for Google Sheets (Manual Setup) or Metric Builder for Excel (Manual Setup)).

If you're only storing dates and Metrics (i.e., numerical values), you'll be creating Standard Metrics in Databox. In this case, it is recommended that your file is formatted with the Date in Row A, and Metric (numerical) values in Rows B, C, etc., repeating for as many Metrics as you have to store.

Individual entries should be listed in the appropriate row.

If you're storing dates, Metrics (i.e., numerical values), and Dimensions (i.e., categories that break up or add further context to the metric values), you'll be creating Dimensional Metrics in Databox. In this case, it is recommended that your file is formatted with the Dates in Row A, Dimensions (strings) in Row B, and Metrics (numbers) in Row C.

All Dimension rows should be next to each other (i.e, Rows B, C, D), and all Metric columns should be next to each other (i.e., Rows E, F, G).

Additional Information

A Date column is not required if you are reporting on up-to-date Daily or Total Value. Learn more in the examples of Custom Google Sheets Metrics here.
New columns or rows should be added at the end of the file in order to avoid duplicating data.
To avoid any data inconsistencies, you should not include cells that show the sum of individual columns or rows (this could duplicate data in Databox and result in data inconsistencies).

How to format Google Sheets and Excel files

IN THIS ARTICLE