How to create a dashboard with Google Sheets

Google Sheet is a great tool to use when you want to add your own custom data to your dashboards or to display data from integrations that may not be supported yet by DashThis.

1. Requirements are important!

2. Connect your Google Sheet source
3. Modifying your Google Sheet source

4. Google Sheet insights

5. Limitations

1- Requirements are important!

First things first, please make sure that your Google Sheet meets the specifications required to integrate your data. Below you will find a list of the four main requirements:

Requirements	Notes and Limitations
1- The spreadsheet must be in Google Sheets	Excel is not supported
2- 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.
3- Your spreadsheet must contain one column with dates. (ex: 12/1/21) Ideally, in the first row.	Date ranges (ex: 12/1/21 - 12/2/21) are not supported. Make sure all dates are in the same format If your spreadsheet contains multiple columns with dates, the first one found in the file will be used as the period in DashThis. Hours, minutes, and seconds in date range columns will be ignored. DashThis will only keep the Year, Month, and Day.
4- Your spreadsheet must contain at least one column with numerical values.	All numerical values need to be in the same format and based on the formatting you're applying in your spreadsheet, DashThis will apply the same formatting in the report. 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. 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”.

You can use our template below as a base DashThis preset Google Sheet Template

gsheetsteps

2- Connect your Google Sheet source to DashThis

Step 1 - Go to your integrations manager page
Step 2 - Click on Add beside Google Sheet *Important: Make sure you have enabled your third-party cookies on your browser. Otherwise, this action won't work properly* Browser extensions, like AdBlockers, could also affect the use of third-party cookies.
Step 3 - Choose your account
Step 4 - Allow DashThis to connect to your account
Step 5 - Choose the spreadsheet that you want to connect
Step 6 - Select the source (You can select multiple sources no problem!)
Step 7 - All done! How easy was that? Now you can create a dashboard using your Google Sheet account.

Note: You can see a detailed list of Google Sheets data sources by clicking on the (i) tooltip.

3- Modifying my Google Sheet

IMPORTANT: If modifications are made to your spreadsheet after adding it to your DashThis account, you will need to update this file by re-adding it to your Google Sheet sources and then adding the new source in your dashboard by accessing the (see video below the screenshot)

Modifications can be changing a cell format, adding or deleting new columns, etc.

For example, let's say we add a new tab to our sheet and you want to add this to DashThis:

newsourcecreated

This is how you will add a new source to an existing dashboard:

4- Google Sheet insights

If you have a Google Sheet source that no longer contains data from a past period but you still see those results from that period in your widgets, simply add a line in your sheet (for the period concerned) and set the metric value to 0.
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 Google Sheets widget, please follow the instructions displayed in the error message, or contact our Support Team for assistance.
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.
As soon as data is imported into DashThis, we keep historic data on our end. If you would like to have this data removed, you must reset your data by inserting a 0 for the periods that you want to delete.

5- Limitations

Limitations	Notes
For performance reasons, DashThis can only import 52 columns 50,000 rows, and a maximum of 40 tabs from a spreadsheet file.	If your spreadsheet contains more data, we recommend that you create another file with the remaining data.
If new data is available in your spreadsheet, your DashThis dashboard will be updated once every hour.	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 with 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.
Make sure that your sheet only contains what you want to import in DashThis.	For example, if your sheet contains a pivot table next to your original data, DashThis will not be able to import it

Should you need any assistance with your Google Sheet, please reach out to our Support Team. To help us resolve your request quickly, don't forget to include your Google Sheet 💪