You can access our Reportz template for Google Sheets here.
Important notice: This template should only be used if you would like the data in your report to be changed as you’re changing the date range of your dashboards.
If you wish to present data from a Spreadsheet that doesn’t need to be changed with the date range, then we advise you to check out our How to Add Google Sheets Widget article.
There are two ways you can report the data from Google Sheets.
On a monthly basis or on a daily basis.
The best way to use our Google Sheets template is to create a copy of it in your own Google Drive.
You can do that by clicking here:
Now, let’s go over the setup for this spreadsheet.
A column
In the first (A) column, you should have dates. The dates have to be formatted as a Date format YYYY-MM-DD, like in the screenshot below.
Be sure to apply this cell format to the entire A column (Date).
B column
The second (B) column is used by Reportz to read the data that has historical values.
Bear in mind that this column must have unique values because if it doesn’t Reportz will only read the first instance of the value.
The way we’ve fixed this is by applying an ARRAYFORMULA to the B column that is just pulling the daily dates from the first column.
Other columns
The rest of the columns only apply to the metrics you will have in Reportz.
Metric 1, 2, 3, should be renamed with the metrics you want to report. Their values should be always formatted as a Number, Percent or any other way you'd like to report on your metrics.
You can add as many columns as you want. The number of additional columns should match the number of metrics that you’re tracking.
Example with renamed metrics columns:
Creating a Widget from the Spreadsheet
This is what the setup for the Widget in Reportz will look like.
Just select if you wish to report on a daily or on a monthly basis in the Sheet box.
Important notice: Before you make a widget out of your sheet, make sure that all data is correct and the sheet is accurately formatted.
If we were to set up our Spreadsheet like this:
This is how the final widget in Reportz would look like:
If you wish to not use our table widget format, you can also go with a chart type.
In this case, the widget would look like this:
Additional note: If you wish to refresh data in your widget you can do so by pressing on the i button and then clicking on Clear Cache and Refresh. You can do this up to 10 times in 24hrs. After you use up all 10 refreshes you will have to wait for 24hrs to be able to refresh again.
For any additional help that you might need, you can always reach out to our support team over the live chat.