How to create a dashboard using Google Sheets data?

Google Sheets is a great tool allowing marketing experts to include their own data coming from all kinds of sources into DashThis.

1. Meet the requirements

2. Connect your spreadsheet files

3. Notes

4. Limitations

1- Meet the requirements

 

To make sure that all your GSheets data will be compatible with DashThis, there are a few requirements to meet.

  • Use a regular spreadsheet like this one:

  • Make sure your spreadsheet file has been saved in your Google account and is not in an XLS or XLSX format.
  • Your spreadsheet must contain at least one column with numerical values already entered in the file. 
  • Your spreadsheet must contain at least one column with date ranges in order for DashThis to import the right data into the right period of your dashboard.
    • If your spreadsheet contains multiple columns with date ranges, the first one found in the file will be used as the period in DashThis.
    • Make sure that all cells in the date range column contain a value.
  • Each column must have a title located in the first row.
    • If a column does not have a title, it will not be imported into DashThis.
    • If multiple columns have the same title, rename them in order to have unique titles for each column.
  • Make sure the columns with numerical values are formatted correctly.
    • Based on the formatting you're applying in your spreadsheet, DashThis will apply the same formatting in the report.
    • Make sure that all cells for a single column are formatted the same way.
      • For dates, make sure one of the date options is selected.
      • Numbers with decimals will show up to 5 decimals in DashThis.
      • For currencies, select one of the currency options. Keep in mind that in DashThis, the currency symbol used will be the one used in your dashboard.
    • Make sure that the number in your cell is matching the format suggested by Google Sheets in the Format menu.

      Screen Shot 2020-11-16 at 8.27.44 AM

 

2- Connect your spreadsheet files

 

GSheets Connection

  1. In the Integration Manager, choose Google Sheets and click on Add.
  2. You’re then redirected to Google. Sign in as usual and allow DashThis to access your Google Drive data (don’t worry – we only use it to list your spreadsheet files!).
    1. Make sure you have enabled your third-party cookies on your browser. Otherwise, this action won't work properly.
      image-Oct-28-2020-01-59-32-91-PMimage-20201007-134530
  3. Using the Google Drive interface, select the spreadsheet you want to add in DashThis.
  4. You’re then asked to select one or multiple sheets contained in your selected spreadsheet. Those sheets will be considered as your data sources.
    1. It is possible that some sheets happen to be incompatible and can’t be added in DashThis. Make sure to meet the requirements by reading the first section of this article.

 

As a sign of success, your Google Sheets account now appears under the section Integrations in use in DashThis! You can see the detailed list of GSheets data sources by clicking on the (i) tooltip.

You’re all set and done to create dashboards now!

 

If modifications are made in your spreadsheet after adding it in your DashThis account, you’ll need to update this file by going through the same process.

Simply add the file(e) again to apply your spreadsheet modifications to your data sources.

 

3- Notes

 

  • DashThis will use the name, the format, and the position in the spreadsheet of each column in order to rightly import your Google Sheets data. Any change in those parameters will affect your widgets and reports.
    If you happen to see an error in your GSheets widget, please follow the instructions contained in the error message, or contact your account manager if you need more help.

  • The default order for each metric is predefined as Descending, meaning that in a List or a Multi-Column List widget, the values will be ordered from highest to lowest.
  • The default aggregation format is predefined as a Sum for all numbers and currency formats, meaning that if there are multiple values for the same dashboard period, those ones will be summed up in your widget.
    • Only Percentage formats will be aggregated as an Average.

  • Empty cells for numerical values will be displayed as N/A in your widget.
  • Columns containing text values in one or multiple cells will be considered as dimensions in DashThis. Empty cells for text values will be displayed and aggregated as “unknown”.
  • Hours, minutes, and seconds in date range columns will be ignored. DashThis will only keep the Year, Month, and Day.

4- Limitations

 

  • For performance reasons, DashThis can only import 52 columns and 5,000 rows from a spreadsheet file.
    • If your spreadsheet contains more data, we recommend you to create another file with the remaining data.
  • If new data is available in your spreadsheet, your DashThis dashboard will be updated once every 2 minutes.
    • Otherwise, you can still use the "Refresh" button under the widget menu.
    • In that case, when new data for a single period is found in your spreadsheet file, DashThis will replace the old data by the new one. Otherwise, your historical data will never be erased even if you remove it from your spreadsheet file.
  • When creating a widget using multiple GSheets data sources, make sure that the selected KPI corresponds to an existing column in every sheet used in the widget.
    • If the column only exists in some sheets, the widget will likely throw an error.