Track the Important Numbers in Your Life : How to set up a Number Dashboard (#Dashboard) in Google Sheets

Number Dashboard Screenshot
Number dashboards (#dashboards) can be used to track numbers that are important to you.  They are built on Google Sheets but can be easily and quickly viewed on mobile browsers.  If you already have numbers calculated and stored in other Google Sheets you can easily copy a #dashboard into existing spreadsheets and plug in your numbers.  Or use standalone #dashboards to hook into multiple spreadsheets in a straightforward workflow and collate your numbers in a single location.

 

 

In the latest iteration of #dashboards I’ve made a small collection of background images that can be included in your #dashboard.  These images are designed to be used dynamically.  As your numbers change so does the image to reflect that change.  For example, say you are counting down to a target date, as you approach the date a circle outline can progressively be filled in.  Or say your crypto investment is on the up, then an upward graph can be displayed, or a downward graph if things aren’t going so well.  The choice is yours.  In most cases you will need to design your own spreadsheet logic to incorporate the images as you need them, but I have simplified the procedure for incorporating countdown and count up images.
Number Dashboard Images Screenshot
The following will walk you through the process of setting up your very own #dashboard.

Method 1.  Set up a#dashboard to collate numbers from multiple other Google Sheets.

  1. Make a copy of my publicly available number dashboard: https://docs.google.com/spreadsheets/d/1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw/edit#gid=0
  2. On the Data tab give your number a name, e.g. “Fuel Range”, in the relevant row.
    Number Dashboard Data tab Screenshot.png
  3. Copy the spreadsheet key from the spreadsheet that hosts your number and paste it into the cell on the Data tab.
    Spreadsheet key is the string of random alpha numerals in the spreadsheet URL.  The key for the above spreadsheet is 1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw
  4. Write out the address of the cell.  Cell Address is the name of the sheet (or tab) in the spreadsheet followed by an ! then the coordinates of the cell using column letter and row number, e.g. SheetName!B3
  5. The spreadsheet will then proceed to access the number.  But before it can display it you need to authorise the connection to the spreadsheet by hovering over the #REF error message that appears and clicking “Allow access”.
    You are only allowing the copy of the #dashboard that you created and own to access the data so you are ok here on a privacy front.
  6. Give a unit to your number if appropriate.
  7. Find an image on the web that you want to use to represent your number.  Copy the image address and paste it in.
  8. Everything else is optional: you can add a further written description, a link (to a graph or more information), and include a dynamic image (more on that below).
  9. On the Data tab you can add a link to an image that will display at the top of the #dashboard.  I find that a nice way to differentiate between my various #dashboards.  If you don’t want one, ignore that and shrink the row on the dashboard.
  10. Once you’ve added in your numbers publish the #dashboard to the web.
    File > Publish to the web > Change “Entire document” to “Dashboard”
    Also expand “Published content and settings” and change “Entire document” to “Dashboard”
  11. Copy the URL and paste it in the web browser ➝ that is your dashboard.
  12. One last bit of tidying up: add “&chrome=false” to the end of the URL and go there.  Much nicer hey?
  13. Send that URL to your phone and bookmark it or add it to your home screen for quick access.

Method 2.  Add a #dashboard to an existing Google Sheet.

  1. Open my publicly available number dashboard: https://docs.google.com/spreadsheets/d/1Yw7jcS8HT53zOZDz8YNnv10Q2WfNoLCX4kZRLtpgMZw/edit#gid=0
  2. Right click the Data tab.  Choose “Copy to …”
  3. Search for and select the Google Sheet you want to add the #dashboard into.
  4. Open that Sheet and rename the tab you copied back to “Data”.
  5. Now repeat steps 2 ➝ 3 for the Dashboard tab.
    (If you want to use the images in the Images tab then you’ll also need to copy that tab into the new Sheet too.)
  6. Now the dashboard lives in your spreadsheet.
  7. To add a number to the #dashboard simply overwrite the formula in the value column and make it point to the cell where the number is, e.g. type”=“ and navigate to the cell or type something like this “=sheetName!C3”.
  8. Type in all the details as in steps 2, 6 ➝ 9 under Method 1 above.
  9. To publish the #dashboard follow steps 10➝ 12 under Method 1 above.

Add a dynamic image to your dashboard.

  1. I have a number that is going to count up to a target value.  I want to use that fantastic circle to fill in as I approach the target!
  2. Type “Up” in the column headed: “Image counts up or down to target value?”
  3. Choose Circles from drop down menu.
  4. Type in the value to start counting from.  Usually this is the value as it is now, or 0.
  5. Then type the target value.
  6. Now the #dashboard knows to ignore the image link (if there is one) and use the dynamic circle image.
  7. The image appears automatically in column on the far right.
  8. There are ten chunks of the circle that get added as the target is approached.
    Screen Shot 2017-08-07 at 16.00.45
  9. If you are comfortable typing logical statements into spreadsheets then you can design formulae to add dynamic emoji or graphs to a number.  I included an example for you called : “Investment Gain / Loss”.  That number doesn’t change automatically so feel free so to play around with the value and see the image change.
I hope that you like the #dashboards and that they provide a useful service for you.  If you need any help setting yours up then feel free to comment below or contact me on Twitter: https://twitter.com/rapidreportsHQ
Advertisements

Inbox by Google : Great and Annoying

I like many aspects of Inbox on iOS. It is Google’s own app so it can presumably harness the email data in ways that third party apps cannot, or at least the direct access it has to email data must give it a reliability and speed edge. Snooze to location is good. Built in actionable reminders. Google’s summary cards for standard emails. These are all great things, however the app does has some very annoying limitations.
1. No text formatting options. Five years ago this wouldn’t have been problem as most people would have been happy to be able to email whilst mobile. But these days it should be possible to add bold, italics and underline to your emails. This is all the more problematic given that even WhatsApp and Slack allow for some formatting – WhatsApp only handles bold but Slack has a range of options. If instant messengers can offer text formatting an email app absolutely should be able to do so. At times Inbox has had support for text formatting, but it is there one update and gone the next. Presently it is available, but who knows for how long?

2. No file attachments outside of Photos / Drive. Sure you can browse your file storage app and attach from there, but is it really too hard for Google to add a proper file picker? Furthermore this is no good for replying to emails; you have to start a new thread (not helpful).

3. Lack of draft saving from share sheet. When sharing files from other apps to email from Inbox, if the email fails to send or you stop composing the email you lose the email. Whereas when sharing into other apps (i.e. Spark or Outlook) the file is opened in the email app itself and attached to a new email. If you then stop composing the email you are given the option to save a draft. Inbox’s behaviour is nice in that it doesn’t switch you into the Inbox app; it overlays Inbox’s interface into the app you are sharing from. But as outlined above it is annoying if you start and email and aren’t able to complete it in one go as you’ll lose the entire email. A common problem here is that if you start composing from the share window and then need to check something in the document you’re trying to share (or another document in the same app for that matter) then there is no way to open the document without closing and losing your email. 

4. No printing! If you want to print emails you’ll need a different app. Even attachments can’t be printed unless they are a PDF. I don’t often need to print emails but when I occasionally do this is a major limitation. More often it is the need to turn an email into a PDF that I notice this lack of feature. In iOS you can turn any document / file into a PDF if it can be printed (find out how here). So the fact that I cannot print an email means that I cannot turn it into a PDF.
I hope that these lack of features will be addressed soon. Does anyone have thoughts about their favourite email app?

Google’s Rapid 2 Step Authentication Option

Google Prompt is a fast second step authentication option on iOS and Android.  Whenever a login attempt is made on your Google account Google Prompt sends a login confirmation to the Google app on your  phone.  You open the app (via push notification) and tap “Yes” to confirm a login attempt is valid (or “No” to deny a login attempt).
This login method is quite a bit faster than using an authenticator app.  I use Authy which even with its widget in the notification centre takes some time to copy the number into the field.  I like Google Prompt for its speed and simplicity.  The slight downside is that it requires your phone to have a live internet connection to use.  This is only a slight downside because if you are signing into a Google service somewhere chances are that you have an internet connection available; with some exceptions.  But Google Prompt works parallel to the other second step options available including an authenticator app, so in the absence of an internet connection on your phone you still have offline options to fall back on.  Head into your Google 2 step verification settings to set it up.
Google 2 Step Authentication Options.png
Google 2 Step Verification Options

 

From a security perspective it is hard for me to say whether this is a better or worse method than an authenticator app.  I understand the methodology behind authenticator apps, but not this one.  That said, I trust Google with my information and I trust them to have built a reliable and safe second step with Google Prompt.  Furthermore I trust that they will be on the ball enough to keep it safe.  I say this because of the numerous articles and security updates that are the result of contributions that Google has made to tech security worldwide.
In summary Google Prompt is a system I am prepared to trust and it makes my login process a great deal more streamlined whilst maintaining its integrity.  I recommend this for anyone wanting the benefits of 2 step authentication with a bit of a faster workflow.

Are You Planning to See the Total Eclipse of 2017?

On 21 August 2017 a Total Eclipse will travel across the USA from West Coast to East – right across!

The eclipse will hit the West Coast just after 10:15 AM PDT, it will then reach the East Coast about 90 minutes later at just gone 2:45 pm EDT.

A great website with loads of information about the eclipse can be found here: Great American Eclipse.  They’ve got maps covering a wide range of eclipse information, videos, eclipse viewing glasses for sale, and lots of information about viewing the eclipse.  The maps available on Great American Eclipse are fantastically detailed – make sure you download the high resolution computer wallpaper.  If you are able to make it to view the eclipse you will certainly find their resources very helpful.

If you are interested in seeing the path overlaid on Google Maps I have added it and you can access it with this link: http://bit.ly/eclipsemap17.  Also embedded below.  Now you will be able to navigate there and confirm you are indeed in the path of the oncoming eclipse right in your Google Maps app.*

I really hope I can make it out to the States to see this, what about you?

 


*Please note that there will undoubtedly be some inaccuracies in the data displayed on my overlay of the map.  So to be certain of being right in the path of the eclipse it would be advisable to move a reasonable distance inside the path and not stay on the very edges as denoted by the black lines.

Act fast to show your support for Numerous

Numerous has been on my blog to do list for some time now.  That is, to write about how great it is – especially in conjunction with Google Forms and Sheets.  It has proven to be an incredibly functional app for me and for many others.  But they recently announced their imminent closure due to being unable to become self sufficient.

However, the are many people who find Numerous incredibly useful and yet it is a free app and service.  Perhaps there are enough of us out there willing to pay to keep Numerous going.*  If you love / rely on / appreciate Numerous take a moment to show your support by filling out this form and then follow the result on Numerous.  This method of course showcases how cool Numerous is: Google Sheets picks up input to the form and Numerous hooks into the sheet to display a number summarising the results.  Multiple cells on the spreadsheet can be linked to from Numerous so you can display multiple results.  For example, with this poll I am taking here I can display (1) how many people are willing to pay for Numerous, (2) on average how much they are willing to pay and (3) the most popular monthly fee for Numerous.

https://nmrs.co/e/slfry9rmymap

* I appreciate that getting enough revenue might in itself not be enough to keep Numerous going, but maybe just maybe it is.

UPDATE 13/2/16: 

For a more detailed analysis of responses check out the published results here.

Here are a couple of other numbers on Numerous to track results of the poll:

Average Numerous Fee
The average fee that users are willing to pay
Most popular fee
The most popular monthly fee for Numerous

Update 3/3/16:

Tweet from @NumerousApp :

@nadnosliw We’ve heard from dozens of people, which is very gratifying, but it needs to be more like 10,000 or 100,000…

Scaled Conditional Formatting Arrives in Google Sheets

Good news spreadsheet people: you can now use colour scales in the conditional formatting in Google Sheets.  So now you can get a quick visual representation of a group comparison.  As a teacher I occasionally need to rank student achievement across a whole class / cohort (the more common and important analyses are those against an individual student’s own benchmarks).  So this newly added feature should prove useful for my grade book.

G Sheets Conditional Formatting - scales

Inbox: A new approach to email

Google have announced more than a revamped Gmail app, a restructured approach to email. Inbox brings a personal assistant, Google Now, right into the email infrastructure.

Inbox - iPhone               Inbox -Android

Google have also incorporated snoozes into the app.  I’ve found snoozes to be incredibly useful with Mailbox.  Google also has an inherent advantage of being an email provider with Gmail.  That means they can access Gmail data directly and fully, which means they can use that data in ways other email clients are unable to.  I would say that I am pretty good at staying on top of my inbox but I wonder if this novel approach could aid me further?

Anyone interested in using the new app has to request an invitation, the aim here being good marketing rather than needing a steady roll out!  The app is available for iPhone, Android smartphones and Chrome.  Note that it is not compatible with Google Apps domains.

So although I’m not a big user of Google Now, the inclusion of snoozes and this new approach have kindled my interest, invite requested!  Are you interested in trialling this new app?  If so, send an email to inbox@google.com.

The most awesome use of Google Sheets for students

I’m really pleased to announce the arrival of the best gradebook for BTEC you have ever seen!

BTEC Gradebook - Progress Monitor

Track student progress like never before with intelligent summaries of portfolio progress, insightful graphs covering all aspects of individual and group progress, real time teacher collaboration and time-saving group management tools.

Each class has its own gradebook and one management spreadsheet can manage data for up to six groups. The gradebooks allow for easy data input by teachers and yet give the most detailed information per student in an easy to understand Progress Monitor. This level of data manipulation hasn’t been seen in a Gradebook for BTEC students until now.

The system is built on Google Sheets which allows for real time collaboration in the Gradebooks, i.e. teachers don’t have to wait for another teacher to finish editing before they can enter marks. There is also no need for a shared network location (along with the problems when a linked document is moved) as this Gradebook lives in the cloud. Linked documents are always accessible (with an internet connection) no matter how you arrange the file directory.

The system is operational now but there are a couple more features still to be released before I define it as fully operational: customisation for calculating the fallback grades and a student view.

A live preview of a Gradebook and Management sheet are available on my website. I’m planning to sell the Gradebook if there is interest in it but I haven’t decided exactly how to go about that yet.

BTEC Gradebook - Unit View