Skip to content
  • There are no suggestions because the search field is empty.

Guide: Using Google Sheets with Databox

Explore how to report your Google Sheets data in Databox. Learn how to connect, format, visualize, import, and update your data. Gain valuable insights into data trends and patterns for informed decision-making.

IN THIS ARTICLE

Overview

Google Sheets is a cloud-based spreadsheet program developed by Google as part of its office suite within Google Drive. It provides users with the ability to create, edit, and collaborate on spreadsheets online in real-time.

They are used in various scenarios, including personal budgeting, project management, data analysis, financial tracking, inventory management, collaborative work among team members, and more. Its accessibility, collaborative features, and ease of use make it a popular choice for individuals, businesses, educators, and organizations needing a versatile and collaborative spreadsheet solution.

To connect a Google Sheet, you need to be on the Professional or higher plans. If you're interested in trying it out, you can request a trial by following these steps.

Usage

IN THIS SECTION

How to connect a Google Sheet to Databox

Go to Data Manager and click on the + New connection button. Type Google Sheets in the search bar and click on the Connect button. Next, click on the green Connect now button.

This will open the Authorization window, where you will be prompted to enter your Google login credentials and authorize the Databox app.

How to format the data in the Google Sheet

Google Sheets offers immense flexibility in organizing and structuring data according to specific needs and requirements. However, when using an external tool like Databox to report on your data, it is important to format the data within Google Sheets consistently and in a structured manner that aligns with Databox's specifications.

To learn more about the accepted layouts and recognized data types, you can refer to this article for detailed information.

How to visualize data from a Google Sheet

Since this is custom solution, you won't find any pre-existing Metrics available upon connecting. This means you'll need to create your own Metrics using the Metric Builder tool. To learn how to create new Metrics using this feature, check out this article for detailed guidance.

Pro Tip: You can get a Databoard started with the help of a wizard. To do so, navigate to Databoards and click on the + New Databoard > Use Wizard option. The wizard will walk you through the entire process of creating a Custom Metric, starting from the connection step and guiding you to the end.

How to update data on a Google Sheet

Here are some best practices for updating data in a Google Sheet, especially when adding new rows and columns:

  1. Use descriptive headers: Clearly label new columns or rows with descriptive headers. This makes it easier to understand the data's purpose and improves readability.

  2. Maintain consistency: Keep the format and style consistent throughout the sheet. Use the same conventions for dates, numbers, and text to ensure uniformity.

  3. Avoid blank rows/ columns: Minimize unnecessary blank rows or columns as they can create confusion and affect data analysis. Place new data adjacent to existing content whenever possible.
  4. Avoid deleting data: If a previously synced value is no longer valid or relevant to your reports, you should replace it with a value of 0 (zero) instead of deleting its corresponding row or column. This is because Databox acts as an accumulator, meaning it does not detect and retroactively delete data that has already been synced for visualization.

How to import data into a Google Sheet

Importing data into a Google Sheet means bringing information from different sources or locations into your spreadsheet. Here are the common ways to do this:

  1. Manual entry: The simplest way is to manually type or copy-paste data from another source, like a website, document, or another spreadsheet, directly into your Google Sheet.

  2. Importing from file: You can import data from existing files such as Excel spreadsheets, CSV files, or text documents. Use the "File" menu and select "Import" to bring this data directly into your Google Sheet. More information about this option can be found here.

  3. Using Google Forms*: If you collect data through Google Forms, the responses automatically populate a Google Sheet. This is helpful for surveys, feedback, or any form-based data collection. 

  4. External Data Sources*: Google Sheets allows connecting to external sources like other Google services (such as Google Analytics or Google Finance), databases, websites, or APIs. This way, you can automatically import data from these sources into your sheet using specific functions or add-ons.

    If you need to import data from external Data Sources that utilize the JSON standard, we have a detailed article that provides more information on how to accomplish this.

  5. Web scraping*: With certain add-ons or scripting capabilities, you can extract data from websites directly into your Google Sheet. It involves programming a bit but allows automatic data retrieval from specific web pages.

* These methods require additional steps to ensure new data is successfully captured by Databox. Navigate to this section for more information.

Each method has its own advantages and suits different situations. Manual entry is straightforward but time-consuming, while importing from files is quick but requires the data to be in a compatible format. Using external sources or web scraping provides automation but might need some technical know-how or additional tools. Overall, these methods help bring data into your Google Sheet, making it a versatile tool for managing and analyzing information.

Additional Information

IN THIS SECTION

Limitations

The ability of Databox to reliably download and parse data from a Google Sheet is dependent on a couple factors.

File Size: The maximum supported file size in Google Sheets, excluding images, is 10 MB.

Pro Tip: To check the size of your Google Sheet you should download it in Excel format. Navigate to File > Download > Microsoft Excel (.xlsx).

To avoid any file size-related issues in Databox, we recommend you 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.

Cell Count: The maximum number of cells in the file cannot exceed 10,000,000 (ten million).

The size of your Google Sheet includes all cells, whether they are empty or contain data.

It's important to keep in mind that even if your document has fewer than 10 million cells, the overall file size may still exceed 10MB. In reality, a document with 10 million cells or more will always be larger than 10MB, so you won't encounter any issues with this limit.

Pro Tip: To determine the cell count, you can utilize the COUNTA() and COUNTBLANK() functions.

  • The COUNTA() function calculates the number of cells within a specific range that contain any type of data. For example, COUNTA(A2:B9) will provide the count of cells that have data in the range A2 to B9.
  • The COUNTBLANK() function calculates the number of empty cells within a specific range. For instance, COUNTBLANK(A2:B9) will give you the count of empty cells in the range A2 to B9.

Behavior

IN THIS SECTION

Data syncing

When you create a Metric in Databox and use it to visualize data on Databoards, Reports, Scorecards, and other features, a sync schedule is automatically established for that Metric. This schedule guarantees that the Metric consistently fetches new data. The frequency of the sync schedule is determined by various factors, which you can explore further in this article.

At the scheduled sync time, our system will first check if the source Google Sheet has been updated since the last data fetch. To do this, it examines the document's last modified time obtained from Google Drive's API.

If the document's last modified time is more recent than the time of our last check, our system will download the document in .xlsx format and analyze its contents. This allows us to update the Metric's values with the most recent data. However, if the document's last modified time hasn't changed, there is no need to download it again. In this case, the old data will remain visible in the application.

Last modified time

Google automatically updates this timestamp based on certain criteria that are not disclosed. We have discovered, however, that there are instances where Google ignores certain edits or modifications to the document or its data, or combines multiple changes into a single change with the same timestamp. Examples of such instances include but are not limited to:

  • Google Form submissions
  • Function recalculations (e.g.: NOW(), RAND(), etc.)
  • Changes done via API (e.g.: third-party integrations, scripts, etc.)

To resolve this issue, you have a few options:

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

2. Set up Apps Script as explained in this article. This script will automate the process of updating the document at regular intervals, ensuring that the timestamp reflects the latest changes.

3. Use Zapier to add or update data in the document on a consistent basis. By setting up this automation, the document will be regularly refreshed, and the timestamp will accurately represent the most recent updates. Check out this article for more information.

Observations

IN THIS SECTION

Multiple connections

When you connect or authorize third-party apps to access Google services via API (Application Programming Interface), a unique refresh token is issued. Think of these refresh tokens as special keys that the app uses to access your Google account data without you having to enter your password every time.

Google has a cap of 100 unique refresh tokens for a single Google account. So, if you've already granted access to 100 different apps or services using your Google account, and a new app requires access, you'll have to de-authorize or remove access for one of the existing apps to generate a new refresh token for the new app. If the deauthorized app is currently connected to Databox, it will automatically disconnect on the next scheduled sync and display an error message.

When connecting multiple Google Sheets to Databox, it is important to note that each sheet will utilize one of these tokens. Please keep this in mind when establishing connections with multiple Google Sheets in Databox.

Visibility and permissions

Changing the visibility or permissions of your Google Sheet can disrupt the connection with Databox. It is important to avoid making these changes once you have established a connection. However, if you need to make adjustments, make sure to reconnect the file afterwards.

Modifying the visibility or permissions of the file is like changing the lock on a door. This means that the previous "key" provided to Databox may no longer work, resulting in a loss of access and inaccurate data on your reports.

Google Drive Backup and Sync

Google Drive Backup and Sync is a desktop application developed by Google that allows users to synchronize and back up their files between their computer and Google Drive, Google's cloud storage service.

To connect files that have been synced through Google Drive Backup and Sync to Databox, you will need to convert them into Google Sheets. To do this, simply open the file and select "File" from the menu, then choose "Save as Google Sheets". This will ensure that the file can be successfully connected to Databox for reporting and analysis purposes.