Data Source Guides
      Back to home
      1. Help Center
      2. Data Source Guides

      Guide: Using Google Sheets with Databox

      HOW TO

      How to connect a Google Sheet

      IN THIS SECTION

      How to connect a Google Sheet

      The ability to connect Google Sheets and access Metric Builder is available on the Professional and higher plans. Request a free trial of Google Sheets by following these steps. 

      To connect a Google Sheets in Databox, go to Data Manager > + New connection. Type Google Sheets in the search bar and click on the green Connect button. 

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

      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.

      How to format Google Sheets file

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

      You can format your data in columns, which would allow you to use the Metric Builder for Google Sheets 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. 

      Learn more about how to format your Google Sheets files here.

      What is the maximum file size for Google Sheets in Databox?

      The maximum file size allowed is 10 MB, 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 Sheets 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 connected with Databox.
      • Split large Google Sheets file into several smaller files and connect them separately in Databox.
      • Do not include custom formatting in the spreadsheet, which incluedes customized fonts, bolding, adjusting the alignment, colors, etc.
      • Aggregate values in your Google Sheets to decrease the file size by following these instructions. You should do this in a new sheet in order to keep an original copy of your data. This can help reduce the file size of the Google Sheets that you want to connect to 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 or updated in the cells included in the Custom Metric. Databox checks for new data in your sheet every hour and will sync when appropriate. 

      Learn more about sync frequency based on Databox plan here. 


      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:

      • 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 All Values as a Data 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 Metric Builder for Google Sheets. Learn more about creating Custom Google Sheets Metrics here. 

      Pro Tip:  You can start building Databoard and Custom Google Sheets Metrics with the help of a wizard. To do so, navigate to  Databoards page >  + New Databoard button >  Use Wizard option, and the wizard will guide you to select your Google Sheets Data Source connection and further through the Custom Metric creation.

      How to use the Metric Builder for Google Sheets

      With the Metric Builder for Google Sheets, you can create Custom Metrics to sync your Google Sheets data with Databox. 

      Learn more about the Metric Builder for Google Sheets here

      Additional information

      • One user can only connect up to 100 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 101st Google Data Source, the 1st Google Data Source that they connected will disconnect. Learn more here.
      • When you connect a Google Sheet in Databox, the connection is dependent on the location of the Google Sheet and the access level of the user who connected the Google Sheet. 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.