Guide: Using Google Sheets with Databox
How to connect a Google Sheet
IN THIS SECTION
- How to connect a Google Sheet
- How to import data sets and spreadsheets to a Google Sheet
- How to import JSON data to a Google Sheet
- What is the maximum file size for Google Sheets in Databox?
- What is the maximum number of cells that can be synced with Databox?
- What is the sync schedule for Google Sheets data in Databox?
- How to update Google Sheets that are syncing with Databox?
How to connect a Google Sheet
The ability to connect Google Sheets and access Query Builder is available in Plus and Business Accounts. Agency Basic Accounts have the ability to connect Google Sheets and access Query Builder in the Agency Account only.
Navigate to Data Sources > Query Builder to access the Query Builder for Google Sheets. Click the green + Create Custom Metric button and select your connected Google Sheet from the Data Source drop-down list.
When you connect a Google Sheet in Databox, the connection is dependent on the location of the Google Sheet, the access level of the user who connected the Google Sheet, name of Google Sheet and the name of the Worksheets. If any of these are changed, the Data Source will disconnect in Databox.
How to import data sets and spreadsheets to a Google Sheet
Learn how to import data sets and spreadsheets to a Google Sheet here.
How to import JSON data to a Google Sheet
Learn how to import JSON data to a Google Sheet here.
What is the maximum file size for Google Sheets in Databox?
The maximum file size allowed is 10MB without images included in the Google Sheet.
Pro Tip: To check the size of your Google Sheets file, you can download it as an Excel file. To do this, in Google Sheete navigate to File > Download > Microsoft Excel (.xlsx). Once your file is downloaded as an Excel file, you can check the size.
To avoid any file size-related issues in Databox, it is recommended that you:
- Remove all images from the Google Sheets file that is used as a Data Source Connection in Databox.
- Split large Google Sheets file into several smaller files and use them as separate Data Source Connections in Databox.
What is the maximum number of cells that can be synced with Databox?
Google Sheets connected to Databox can contain a maximum of 2,000,000 cells of data.
Pro Tip: All cells, empty or full, are calculated into the size of your sheet. To count the number of cells in your Google Sheets, you can make use of the function COUNTA to count cells with data and the function COUNTBLANK to count empty cells.
- The COUNTA function counts how many cells in a specific cell range contain any kind of data. For example, '=COUNTA(A2:B9)' will give you the number of all cells that contain data in the cell range A2:B9.
- The COUNTBLANK function counts the number of empty cells in a specific cell range. For example, '=COUNTBLANK(A2:B9)' will give you the number of all cells that contain data in the cell range A2:B9.
How often does Google Sheets data sync with Databox?
Google Sheets Custom Metrics will only update if new data is added to rows, columns, or cells used in the Custom Metric. Databox checks for new data in your Sheet every 2 hours and will sync when appropriate.
Learn more about sync frequency based on Databox plan
At the top of your Google Sheets file, you can find the Last Edit information displayed. This timestamp is updated every time changes are made to your Google Sheets file and is what triggers data syncs in Databox.
If your Google Sheet is updated with new data automatically (i.e., if you use Scripts or other solutions to automatically add data to Google Sheets), this may not update the Last Edit information and could result in Databox not recognizing new data. As a result, data will not be synced.
If your Google Sheets Data Source is updated automatically, you can try and update the Last Edit information in Google Sheets by using one of the following options:
- Set the Recalculation setting in Google Sheets to On change and every minute. This will refresh your Google Sheets every minute.
To do this, navigate to File > Spreadsheet settings > Calculation. In the Recalculation section, click on the drop-down menu and select On change and every minute. Save the settings.
- Update the Google Sheet manually
You can manually add '1' in a blank cell in any Sheet and update it when necessary. Make sure the given cell is not selected for any of your Google Sheets Custom Metrics. Making changes manually to your sheets will update the Last Edit information in Google Sheets and the relevant data will be automatically updated in Databox with the next scheduled sync.
- Update the Google Sheets by using Zapier
You can use Zapier to push data into Google Sheets (e.g. pushing current date/time) every hour. This will update the Last Edit information every hour, so Databox will search for new data in the sheets with every scheduled sync.
How to update Google Sheets that are syncing with Databox?
While updating the Google Sheet, new rows or columns should not be added to the top or in the middle of the document. This would result in duplicate data syncing with Databox and potential data inconsistencies. It is recommended to add new rows or columns at the end of the document.
If you update a single cell's Value in your Google Sheet, you should select Total Value as an Event Type. Learn more here.
How to create a Databoard using data from Google Sheets
The Google Sheets integration is completely custom, so there are no Basic Metrics available. All Custom Metrics must be built using the Query Builder for Google Sheets. Learn more about creating Custom Google Sheets Metrics here.
- One user can only connect up to 50 Google Accounts in Databox. This includes all Google Data Sources, such as Google Sheets, Google Analytics, and Google Ads. When a single user connects a 51st Google Data Source, the 1st Google Data Source that they connected will disconnect.
- When you connect a Google Sheet in Databox, the connection is dependent on the location of the Google Sheet, the access level of the user who connected the Google Sheet, name of Google Sheet and the name of the Worksheets. If any of these are changed, the Data Source will disconnect in Databox.
In the Data Preview, you will only see the first 1,000 rows from your Google Sheet. Additional rows are still included in the Custom Metric, they just are not visible in the Data Preview.
If you are using the Sync option within your Google Drive (Google Drive Backup and Sync) to sync files between Google Drive and your computer, or if you manually uploaded Excel files to your Google Drive, the added files will not be recognized by Databox. Because of this, you will not be able to successfully connect the Google Sheets to your Databox Account.
In order to successfully connect these documents to Databox, you must convert them into Google Sheets. To do this, select File > Save as Google Sheets within the document.
Pivot Tables are not currently supported and cannot be synced with Databox.