Google Sheets
Dashboard and Reporting Software

To ensure compatibility with Databox, durations formatted as [hh]:[mm]:[ss] should be converted into seconds in your spreadsheet, as Databox only recognizes time durations in seconds. After conversion, Databox will automatically aggregate the time into minutes and hours based on your selected format. You can learn more about how to convert durations to seconds in your spreadsheet here.

This issue may arise if your spreadsheet’s locale settings don’t match the format used in the cells. Date and number formats vary across regions, so adjusting your file’s locale settings ensures that the date and number formats align with your region. This leads to a consistent and clear presentation of dates and numbers in Databox.

For example, consider the date December 5th, 2023:

• In the United States, it’s typically formatted as 12/05/2023 (MM/DD/YYYY), where the month (December) comes first, followed by the day (5th), and then the year (2023).
• In many non-US regions, such as the UK or Europe, the same date would be represented as 05/12/2023 (DD/MM/YYYY), with the day (5th) first, followed by the month (December), and then the year (2023).

Learn more how to modify your spreadsheet locale settings here.

For Google Sheets, Databox does not enforce file size or number of cells limits. However, Google does have limits in place for their API. Google allows 10 MB file downloads and the files themselves have a 10 million cell limit. A Google Sheets file exceeding either one of those limits will not work in Databox.

In a spreadsheet, vertical orientation means organizing data into columns, where each column represents a specific category or attribute, and individual entries or records are placed in rows within these columns.
In horizontal orientation, data is organized across columns, with each column representing a unique entry or category. Specific information within those entries is then arranged vertically, with different rows containing various attributes or details for each entry.

In Databox:

• Vertical orientation is required for the Metric Builder Wizard.
• Horizontal orientation is supported only for manual metric setup (without the Wizard option).

Databox requires that the value in a cell is defined as a number and when a cell displays #DIV/0 (divide by zero) error, the value assigned to this in Databox is number 7.

We advise users to handle division by 0 in their sheet directly. There are multiple ways to do this, here is an example:
How to Remove #DIV/0! in Google Sheets

This issue may occur if dates are not correctly entered in the cells. Dates should be formatted as complete dates, including the day, month, and year, rather than just the month and year or only the month. For detailed guidelines on proper date formatting, refer to the ‘Requirements regarding date formatting’ section in the Behavior & usage tab.

Columns that contain words such as Today, Yesterday, Tomorrow, Next Year, Last Year, Next Month, Last Month, etc., will be recognized as Date instead as Dimension in Metric Builder Wizard due to parsing limitation. As consequence, they can not be used as Dimensions in Wizard.

Columns containing words like Today, Yesterday, Tomorrow, Next Year, Last Year, Next Month or Last Month are automatically recognized as Date fields instead of dimensions in the metric builder Wizard due to parsing limitations. Consequently, these columns cannot be used as Dimensions within the Wizard.

Workaround

Create metrics using the manual setup, where such columns can be selected as dimensions:
How to switch between Manual Setup and the Wizard in Google Sheets
How to switch between Manual Setup and the Wizard in Excel

Databox does not validate all rows when determining whether a column contains numbers, text, or dates. To provide a quick preview in the metric builder, only the first 50 rows are checked. As a result, if a column lacks numeric values within the first 50 rows, it will not be identified as numeric and will default to being treated as a dimension.
Additionally, empty cells are interpreted as empty strings (“”), regardless of whether a ‘number’ format has been set for the cell. An empty cell with a numeric format but no value is also treated as an empty string, not as a numeric value.

Users should avoid leaving empty cells within the top 50 rows of any column in the source document if data is present in cells below the 50th row of the same column.
To address this, it is recommended to populate all empty cells with appropriate values, such as 0.00 or any other numeric value suitable for the specific use case.

Dates in Google Sheets are represented as floating-point values, where the integer part corresponds to the number of days since December 30, 1899, and the decimal part represents the time of day. For example, 1 day equals 1.0, an hour equals 1/24, and a second equals 1/86400. This allows dates and times to be expressed as numbers, such as 45639.0826388889, which represents December 13, 2024, at 1:59 AM.

However, rounding these values, either through formulas or functions, can lead to inaccuracies when converting them back to a date and time. For instance, if 45639.0826388889 is rounded to two decimal places, it becomes 45639.08, which corresponds to December 13, 2024, at 1:55 AM — four minutes earlier than the original value. This discrepancy highlights the potential issues with modifying the precision of date-time values in Google Sheets.

We strongly recommend ensuring that all dates in your spreadsheet are recognized as dates—either automatically, based on locale settings, or manually by applying custom date formats that override locale settings. This approach greatly reduces the likelihood of encountering issues later on.

If a date string is not recognized by the document, Databox will attempt to validate it against common standards like ISO 8601 and RFC 2822.

We recommend adopting popular date formats, such as:

YYYY-MM-DD
MM/DD/YYYY
DD/MM/YYYY
December 4, 2023
4 December, 2023

In this help article, you can learn how to check if a date format is properly set for the usage in Databox.

Click Refresh Sheets Data in Google Sheets or Excel metric builder to view the latest data from your document. This is particularly useful if you’ve recently made changes to your sheet. Doing so will also update the preview and data for all existing custom metrics.

If the IMPORTRANGE function is heavily used in a Google Sheets document connected to Databox, it can cause performance issues on both the Google Sheets and Databox sides under certain conditions. In Databox, this may result in increased loading times, sync delays, or 5XX API server errors. To ensure smooth operation, it is advisable to optimize the usage of IMPORTRANGE and explore alternative approaches where possible.

To avoid overusing the IMPORTRANGE function, please consider advice in the Google article Technical details & best practices.

Google Sheets files large in size and by number of cells and using additional styles/formatting/images in worksheets can cause longer loading times in the Metric Builder. In such cases, users are advised to:

– Remove all images from file.
– Split the file into several smaller files and connect them separately to Databox.
– Remove any custom cell formatting (fonts, styles, alignment, colors).
– Deduplicate and aggregate values wherever possible.
– Remove any unused sheets/ data.

Changing the location, visibility, or permissions of your Google Sheet file can disrupt its connection with Databox. It’s essential to avoid making these changes once the connection has been established.

Altering visibility, locking sheets, or modifying file permissions is comparable to changing the lock on a door—the ‘key’ previously provided to Databox will no longer work. This can result in a loss of access and inaccurate data appearing in your reports.

If adjustments are necessary, ensure you reconnect the file in Data Manager afterward to restore proper access.

Databox imposes a limitation on the maximum number of digits a numeric value can have, as outlined in this help article. To ensure proper functionality in Databox, all numeric values must adhere to the following criteria:

• The integer (whole) part of the number must not exceed 16 digits.
• The decimal part must have a precision of no more than 6 digits.

Updating Google Sheets via automated scripts typically does not update the document’s last-modified date. As a result, Databox cannot detect changes or download the latest sheet and cell values. To address this, we recommend adding a Google Apps Script to your Google Sheet. This script will automate the process of refreshing the document at regular intervals, ensuring the timestamp reflects the most recent updates. By doing so, you can ensure that the latest changes in your Google Sheet are properly synced with Databox.

See prepared code and further explanation in the article How to refresh a Google Sheet using Google Apps Script.

The sync schedule frequency in Databox depends on several factors, including the Databox plan and account activity, as detailed in this article.
At the scheduled sync time, our system first checks if the source Google Sheet has been updated since the last data fetch by examining the document’s last modified time provided by Google Drive’s API.

• If the last modified time is more recent than our previous check, the system downloads the document in .xlsx format and analyzes its contents to update the metric’s values with the most recent data.
• If the last modified time remains unchanged, the document is not downloaded again, and the previous data will continue to display in the app.

Google automatically updates the last modified timestamp based on certain criteria, though these are not fully disclosed. We have identified some scenarios where Google may not update the timestamp or may combine multiple changes under the same timestamp. These include but are not limited to:

• Submissions through Google Forms
• Function recalculations (e.g., NOW(), RAND())
• Changes made via APIs (e.g., third-party integrations, scripts)

These behaviors can result in updates not being detected by our system during sync. To address this issue, consider the following solutions:

1) Manually Refresh the Document: Open the Google Sheet in your browser. This action will trigger formula recalculations and capture any new changes, thereby updating the last modified timestamp.

2) Automate Updates with Apps Script: Set up an Apps Script as described in this article. This script will periodically update the document, ensuring that the timestamp reflects the latest changes automatically.

3) Automate Data Updates with Zapier: Use Zapier to add or modify data in the document at regular intervals. This keeps the document refreshed and ensures the timestamp accurately represents the most recent updates. Detailed steps can be found in this article.

Google imposes a limitation where a single user can have a maximum of 100 active Google data sources at any given time. This includes all types of sources combined, such as Google Sheets, Google Ads, Google Analytics 4, and others.

If this limit is exceeded, the oldest connected data sources will automatically disconnect when new ones are added.

Recommendations to Avoid Hitting the Limit:

1) Create Additional Google Users:

• • To manage more data sources, Databox recommends creating additional Google accounts and using these accounts to connect new data sources.
  • For example, create a new Google user after every 85 data sources connected to provide a buffer and avoid reaching the limit.

2) Establish New Connections:

• • Once you’ve connected 85 data sources with one Google account, create a new Google user.
  • In Databox, add a new connection using the credentials of this new Google user, and use it to connect additional data sources (e.g., Google Sheets).

By distributing data sources across multiple Google accounts, you can effectively bypass the 100-source limitation while maintaining uninterrupted service.

In some cases, users may want to apply filters to their reports to include or exclude specific data, helping them focus on the most relevant information for their analysis. However, Databox currently does not support filtering data after it has been imported. As a result, any necessary filtering must be done directly in the spreadsheet before creating the Metric in Databox.

Powerful tools for extracting specific subsets of data from a sheet, table, or range of cells include the QUERY() function in Google Sheets and the FILTER() function in Excel. Learn more about how to use these functions here.

Check if the sheet/tab contains a Pivot table. Pivot Tables are not currently supported and cannot be synced with Databox, hence they will appear empty in Metric Builder.

Sometimes, spreadsheet entries contain extra spaces, which can create duplicates. For example, you might have two dimensions: ‘Closed Won’ (without a space after the ‘n’) and ‘Closed Won ‘ (with a space after the ‘n’). Although they may look identical in Databox, they will appear as two separate dimensions because the system treats spaces as characters, recognizing them as distinct entries.

Users can use the ‘Find and Replace’ feature in their spreadsheet to correct dimension names. For example, they can replace ‘Closed Won ‘ (with a space) with ‘Closed Won’ (without the space).

Using macros from the Extensions → Macros menu in Google Sheets will update the Last Modified Time timestamp, thereby triggering data syncing with Databox.

By default, Databox allows viewing data up to 10 years in the past and 2 years in the future for spreadsheet documents. When the ‘All Time’ date range is selected for, it will display data within this -10 to +2-year range. This limitation also applies to any other date range confined within these boundaries.

However, if the spreadsheet document contains dates beyond this range, they will be pulled, stored in the Databox warehouse, and displayed when a broader date range is selected, such as ‘Last 20 Years’ or a custom range that exceeds the -10 to +2-year limit. Once such a range is selected for a metric containing data beyond the default boundaries, the ‘All Time’ date range will automatically include and display the extended data for that specific metric.

If users collect data through Google Forms, the responses are automatically recorded in a connected Google Sheet, making it a convenient method for surveys, feedback, or other form-based data collection. However, in Databox, new rows added by Google Forms will not trigger data updates because they do not modify the sheet’s last modified time timestamp.

To resolve this issue, users should consider the following options:

1. Open the document in the browser. This will trigger any formulas to recalculate and capture any new changes, thereby updating the timestamp.

2. Set up Google Apps Script as explained in this article.

3. Use Zapier to add or update data in the document on a regular basis.

As described in Units help article, Databox automatically recognizes numbers formatted in specific currencies, such as the United States Dollar ($), Euro (€), British Pound Sterling (£), Japanese Yen (¥), and Russian Ruble (₽). For other currency formats, their symbols will appear in the Unit selector within the app instead of the standard three-letter codes like USD or EUR.

If a custom currency format is not recognized or supported, use the number format instead and configure the desired currency format through Databox Advanced Settings. You can find more information about currency formatting in articles on Google Sheets and Excel.

For other measurement units (e.g., weight, duration, speed), it’s recommended to remove unit labels from the cells containing values and instead use Databox in-app formatting functionality to display the appropriate unit.