Mobile Dashboard Script

Overview

Spreadsheets are a great tool for personal finance.
Unfortunately, they often aren’t responsive, accessible, or user-friendly when you are on the go.

So, how best to check in on key metrics when you are out and about?
How can one answer questions like:

  • Is there enough budget left to dine out tonight?
  • Has my paycheck posted this month?
  • Is my checking account balance dangerously low?

Last month, our #winning-workflow theme was mobile-friendly dashboards.
I wanted to share a solution I built for my personal-finance dashboard.

The tiller-web-dashboard project leverages Google-Apps-Script web apps to publish a real-time, responsive, mobile-friendly dashboard for your personal-finance spreadsheets. Just identify the metrics that matter to you, link them into the Web Dashboard configuration sheet, and get quick answers on the web wherever you are.

Warnings

  • This project is designed for intermediate users and includes only lightweight error checking. We hope it meets your needs out of the box, but further tweaks may be required to get it working in your environment. As a one-off Tiller-Labs release, Tiller offers no warranties or support for this solution.
  • Before running the script, you will see a message from Google that says “This app isn’t verified.” This is normal since you will have just created the script. Review the code and make sure you are comfortable with what it is doing— again this script is for intermediate & advanced users. You will need to click an authorization button from Google that says Go to <your script name> (unsafe) to run the script.

How the Script Works

The script contains two components:

  1. Google Apps Script (GAS) script that will be embedded in your spreadsheet
  2. Dashboard renderer hosted by Tiller (this html won’t render properly if you click on it— you’ll need to click “View Page Source” to see the code)

The GAS script will be configured as a web app and will respond to calls to its endpoint by sending the contents of your spreadsheet’s Web Dashboard sheet to the dashboard renderer. The dashboard will then leverage Bootstrap, JQuery and Javascript to render the data into a responsive, mobile-friendly dashboard.

We have chosen to deploy the GAS script with minimal formatting responsibility so that it will rarely need to be updated and redeployed. It is built to simply forward the sheet data to the renderer.

Set Up Your Spreadsheet

  1. Install the sample dashboard configuration sheet (instructions on GitHub)
  2. Install the script in your spreadsheet (instructions on GitHub)
  3. Personalize your dashboard (instructions below)

Personalize Your Dashboard

Configure your dashboard to always shows the metrics that matter to you most.

The dashboard is configured via the content of the new sheet named Web Dashboard.

The first row in the Web Dashboard sheet contains your dashboard title.

The second row in the Web Dashboard sheet contains headers recognized by the renderer. The renderer recognizes the following header names:

  • Header
  • Value
  • Type
  • Color
  • Note

Columns with (row-two) headers not listed above will be ignored by the renderer.

Each subsequent row will contain cells defining a metric block.

Once the script is deployed as a web app, the contents of your dashboard will always be live. Just reload your dashboard URL (the one from the “Deploy as web app” window) and you will see your most up-to-date metrics.

Title

Update the contents of cell A1 to change your dashboard’s title.

Metric Blocks

Metrics will be organized into responsive, mobile-friendly squares.

Header

The header cell is the title of the metric block.

Value

The value cell is the primary value of the metric block. The content will be formatted by the Type cell.

Type

The type cell determines which formatter is applied to the value.

The renderer recognizes the following types:

  • currency - a currency value without trailing digits (e.g. $1,515)
  • currency00 - a currency value with trailing digits (e.g. $1,515.50)
  • date - a date formatted as “m/d/yy” (e.g. 2/14/20)
  • integer - an integer (e.g. 5)
  • percent - a percent without trailing digits (e.g. 15%)
  • text - unformatted text

Color

The color cell determines the color of the metric block.

The following colors are recognized by the renderer: “apricot”, “beige”, “black”, “blue”, “brown”, “cyan”, “darkgray”, “darkblue”, “green”, “lavender”, “lime”, “gray”, “magenta”, “maroon”, “mint”, “navy”, “olive”, “orange”, “pink”, “purple”, “red”, “teal”, “white”, "yellow”.

Using formulas to dynamically set the color of metric blocks can be a powerful tool for calling attention. For example, a formula like the one below can flip a metric from green to red when it falls below a certain value:
=if(B5<200,"red","green")

Note

The note cell behaves like a footer to the metric. It is located at the bottom and appears in a smaller font. It can be used to provide more detailed context for a metric (like the metric’s date range).

Bundled Metric Blocks

With proper formatting, multiple data points can be bundled into a single metric block.

Multi-value

With multi-value bundled metric blocks, multiple values are sequentially displayed, like a vertical list, within a single metric block. Multiple metric rows (in the Web Dashboard) are rendered into a single metric block when subsequent rows have a blank header cell.

Data Tables

With data table bundled metric blocks, a small table can be built within a metric block by setting the value and type cells as data arrays. As with multi-value bundled metric blocks, multiple rows can be added to the data table by leaving the header cell subsequent rows blank.

A data table value should be formatted to look like:
["2020-02-15","Citi Mortgag",-1000]
And may have a corresponding type cell that looks like:
["date","text","currency"]

To build array data like the example above, you may use a formula like:
="["""&text(G10,"YYYY-MM-DD")&""","""&left(H10,12)&""","&I10&"]"
This formula takes a date value from G10 and applies a date formatter. It also clips the description in H10 to just 12 characters.

Examples

Uncategorized Transactions

This example counts uncategorized transactions in the Transactions sheet in the current year.

=countifs(Transactions!C2:C,"",Transactions!A2:A,">="&date(year(today()),1,1),Transactions!A2:A,"<"&date(year(today())+1,1,1))

Current Account Balance

This example performs a lookup on “Wells Fargo Checking” from the Accounts sheet.

=vlookup("Wells Fargo Checking",{Accounts!$H$2:$H,Accounts!$J$2:$J},2,false)

Category Available in Current Month

This example finds the available/remaining budget in the “Clothing” category by doing a two axis lookup in the Yearly Budget sheet— category on the rows axis and budget month on the columns axis.

=offset('Yearly Budget'!$E$7,match("Clothing",'Yearly Budget'!$A$7:$A,0)-1,iferror(match(date(year(today()),month(today()),1),INDIRECT("'Yearly Budget'!E3:3"),0))+1)

FAQ

Can I remove the “Terms of Service” message at the top?

I don’t believe so. I think it is a precaution applied to all web apps by Google.

Intrigued. I have a fun quarantine project now

The setup is a little wonky and the documentation is hot off the presses… but our founder, @peter, managed to work through it. Don’t hesitate to ask if you have any questions.

I can’t wait to hear what you think, @richl.

Randy, no issues at all getting this dashboard app to work. Instructions were clear and easy. Only issue I had was with the default metrics you had and the formulas it used to work. It looks like the columns you call are in different column letters than in the default sheets. For example, it refers to column A in transactions sheet which is blank by default , Nothing too hard to figure out, but the webapp didnt show any data by default. easy fix, but you may want to look into this for others.

Question, is there a limit to the number of blocks it will show?

Overall, I cant wait to take my envelope sheet data and add it to my dashboard. Today, I use Appsheet to do the same.

Great Job Randy, I really appreciate this.

Rich

That’s really great to hear that you’re up and running, @richl.

I wrestled with automating column lookups— like we do with Tiller templates— and ultimately decided that, since this is a tool for advanced users, they’d be able to massage it to work with their spreadsheets.

There are no limits on the number of cells in the code. I haven’t pushed it too hard but I’d imagine you could make it work with a lot of blocks.

Thanks for giving the dashboard a shot.
Randy

P.S. If you want to personalize it, you can pull down the HTML at the URL referenced by templateUrl, change it (to render with different CSS or a different layout or a different widget type), host the updated file (or you could host the HTML in the script project), and then republish the script pointing to your personal HTML template.

Hey Randy, last night I added your dashboard to new tiller sheet. Today, I want to add to my Envelope sheet, but I already have my envelope script. Will tha tbe an issue or do I just need to add a new script and publish it as a web app. CAn I connect two to the same sheet?

I haven’t tried it, but I believe it should work.

I’d use the Script Editor file menu to create a new script file to hold your envelope script. (Will be easier to maintain if they are separate.) I believe the publish web app functionality only runs a specific function, so it shouldn’t care that you have other scripts in the project.

Hope this helps. Let me know if you bump into any problems.
Randy

yep worked no problems. I will send a screen shot once i am done.

Once again, Thanks

1 Like

I also made this work just fine with your Envelope spreadsheet.
I had an enjoyable quarantine evening figuring it all out.

2 Likes