3.6: Google Workbook (Sheets) Dashboards Tips
- Page ID
- 433662
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
Dashboards Quick Tips
Gauge Chart
If you want a display of the most recent data reading you can use a guage chart. This shows the current reading and a scale.
You will need to use insert, not append when adding you data.
Lets say I want a gauge chart of the current IAQ score. Here the data is being inserted to the top row of the spreadsheet.
On my dashboard I will select a cell where I want to have the gauge chart and will use the indirect function to select where I want the reading to come from.
Returns a cell reference specified by a string
Why can't I make a gauge chart directly from the data tab? The range of charts will automatically update when new data is inserted, for example if I did just =F2 when new data was inserted the formula would update to =F3. Since we want to lock the cell location not the data point we need to access it indirectly.
Now select the cell on you dashboard and select insert chart. Change the chart type to gauge chart. You can add labels and style the chart to add color ranges.
Troubleshooting
Here are some common issues that can come up when inserting data into google sheets.
My data has quotes or a parentheses '
By default your input type in your python code will be "RAW" which means data is stored in sheet exactly as is. If you change it to 'USER_ENTERED' numbers will stay numbers but the strings will be updated to to follow the rules of google sheets. (AKA will be autoformatted)
My Range changes when new data is inserted
This is google's default to keep formulas and such from breaking when new data is inserted. It's incredibly helpful when you have to make changes to your sheet! But not so helpful when you want to update your sheet every time on purpose.
One work around is to call the range indirectly
Make a new tab and use the INDIRECT function to select the range you want for the chart.
For example if I want a display of the past 10minutes and I'm recording values every 30 seconds, I will want a range of 20rows
=indirect("Data!A1:B20")
Make sure the range is in quotes!
Now I can select the columns on the tab I just made and use that to insert my chart!
I want more colors for my gauge/ chart
So google sheets currently limits you to one background color for charts and only three colors for the gauge. We can sneak around this by setting the background color of the chart to transparent and putting an image behind it.
Edit Chart>Customize>Chart Style>Background Color>"None"
Then on the menu
Insert>Image or Drawing
(If image keeps appearing on top of the chart try inserting it into a cell instead)