Skip to main content
Chemistry LibreTexts

4: Google Workbook (Sheets) Dashboards Tips

  • Page ID
    434337
  • \( \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.

    clipboard_ef8ff3819c832f533015ed2557310fc62.png

    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.

    INDIRECT("data!F2")
    About

    Returns a cell reference specified by a string

    cell_reference_as_string
    A cell reference, written as a string with surrounding quotation marks.

    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)

     

     

     


    This page titled 4: Google Workbook (Sheets) Dashboards Tips is shared under a not declared license and was authored, remixed, and/or curated by Robert Belford.