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

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.

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.
   
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.
   
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

API

Looking forward to seeing how this app, Numerly, turns out. Hoping it will be a great successor to ill-fated Numerous. Will be a pity to no longer rely on my custom number dashboards in Google Sheets (something I really should have written a blog about a long time ago).

Numerly

With Numerly, the default channel when creating a number is called “Create Your Own.” The value for these types of numbers are only changed when you manually change them. You might use this to keep track of your child’s allowance, or maybe to keep a tally of calories you’ve consumed today.

You can also update these types of numbers with the API. There are three endpoints you can call to set, increment, or decrement a number. Each of these endpoints occur over secure HTTPS as a POST request using your APIKEY and refid.

The URL used in these examples might not be the final URL when the app is launched. I’m currently using Heroku as a SaaS provider and I don’t plan to change; however, the URL might change to be something a bit more official.

Authorization

Each API call requires an HTTP header named APIKEY whose value is provided from…

View original post 247 more words

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 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.

New Text Formatting Options in Evernote 8 for iOS

Evernote 8 introduced some text formatting options that people had been requesting for a long time.  Here is a quick tour of how to use them.

Refurbished 2015 15″ MacBook Pros back in the store (limited supply)

As of today a wide range of 2015 15 inch MacBook Pros became available on the Apple refurbished store.  If you’re interested in one move fast as they will get snapped up. 

Thanks to Change Detection I was able to get notified ASAP once more laptops were added. 

Thank you Apple for making more stock available. 

Is it time to abandon the Mac and turn to the dark side, a.k.a. Windows?

I am currently looking to buy a new laptop.  Being an Apple user with a MacBook Pro I am primarily looking at a new MacBook Pro.  But there is no way I am forking out an extra ~£300 for a model with a Douchebar.  So I have been looking into the 2015 model that was reintroduced to the Apple Store alongside the Touchbar models.

There is no way I am forking out an extra ~£300 for a model with a Douchebar

Here are the specific problems that I am facing.  Right before the new models hit the store there was a 15 inch model available on the refurbished store that is now no longer available.  That model had a 2.5 GHz Core i7, 512 GB SSD and a discrete graphics card.  Its price was £1859.  The 2015 model now available in the main store has not had any internal upgrades since it was released in May 2015 (yes that includes the 4ᵀᴴ generation Core i7 processor (4870HQ) that was already old at the time of its initial release!).  The starting specification for the currently available model is 2.2 GHz Core i7, 256 GB SSD and no discrete graphics.  Both have 16 GB RAM and PCIe flash storage.  It is possible to configure a new Mac with a 2.5 GHz processor and 500 GB SSD, but you cannot have a discrete graphics card in it.

The details of the refurbished MacBook are still available to me in my Apple Store account after I added it as a favourite.  Here is a link to a printout from the store downloadable as a PDF: https://www.evernote.com/l/AGQmziGrwr5AzZyNw6z5efiWEIsMGww3VIc  [Accessed on 17 December 2016].

Whatever the configuration the machine is going to be significantly less powerful in comparison to the refurbished option due to the missing discrete graphics card.  But that is only part of the story.  The starting price of the “new” 2.2 GHz model with 256 GB SSD is more expensive at £1899!  If you want to match the spec of the refurbished model, minus the discrete graphics card, it’ll be a huge £2169.  Just to be clear that this is not Brexit related inflation, the price of the refurbished Mac is from November which was after Apple increased their prices by about 20% in the UK post Brexit.

This is not Brexit related inflation

Therefore, Apple arbitrarily decided in November that it would charge its customers an additional £40 for a huge reduction in power.  There is no meaningful difference between a refurbished model the same model bought new.  Both have a 12 month warranty with the option of extending that to 3 years under AppleCare.  Apple had already decided that they would accept £1859 for their mid-tier 15 inch model with discrete graphics card.  The price increase is therefore an entirely obnoxious move by Apple and sadly reinforces the notion that everything is structured by Apple to maximise profit at the expense of customer satisfaction.  This is something that I would say is a relatively new priority in my experience.

Everything is structured by Apple to maximise profit at the expense of customer satisfaction

If that refurbished model were still available to buy I’d probably not be in the position that I’m in now: contemplating my move to Windows.  I am fairly heavily invested in macOS and iOS but I don’t like the feeling that Apple are trying to manipulate people to spend a lot more money than is necessary.  Especially if it means them forcing people to buy something they don’t want – like the Douchebar at an additional £300.   There is an additional reason for me saying this.

Before considering the purchase of the MacBook I did some research into competing laptops and what I found really sets Apple’s offering in an exceptionally bad light.  Whereas in the past when I have looked at comparative laptops I have found prices for comparable Windows laptops* to be similar to Apple’s prices, the new top spec Dell XPS is a beast in comparison to the MacBook Pro.  It has a 15 inch 4K monitor, oh and it is a touchscreen monitor!  It has a proper professional grade graphics processor.  It has a sixth generation Core i7 processor.  16 GB RAM (expandable to 32 GB in the unlikely event you really need it), and 512 GB PCIe SSD.  The enclosure is machined aluminium with a carbon fibre interior at a maximum thickness of  17 mm (just 2 mm more than the 2016 MBP).  The glass is scratch resistant Corning® Gorilla® Glass.  If the 3 year extended warranty is included (with on site support) the Dell XPS will set you back £1960; it’s £1749 without the extended warranty.

What you see is that Dell’s laptop that competes directly with the 2016 MacBook Pros is way, way cheaper than the “comparable” 2015 MacBook Pro Apple are trying to shift.  It is more powerful and more functional than the 2016 models: Apple offer consumer not pro grade graphics cards for more money, Apple offer a thin strip of touchscreen for more money.

15″ Dell XPS

It is disappointing for me to be in this position.  I could really do with laptop with more screen real estate.  I’ve long believed that Apple make great hardware, but Apple want to charge me a hefty price tag for a 15 inch screen and they also want to shaft me on the interior.  On the other hand Dell and Lenovo are offering some powerful and innovative alternatives (the Lenovo Yoga 710 2 in 1 is a great machine which is considerably cheaper than a MBP).  No wonder then that creative professionals are jumping ship to Windows.

14″ Lenovo Yoga 710

I will not buy a laptop with a Douchebar.  I hate it, I believe it is a gimmick.  If Apple consolidate their position on the Touchbar in future iterations of the MacBook Pro lineup I will have no choice but to abandon ship then.  So am I delaying the inevitable anyway by even considering sticking with them for another 4–5 years by buying a MacBook now?

Am I delaying the inevitable anyway?

It was also disappointing that a salesperson in an Apple Store could not give me a better reason to choose the MacBook over the Dell XPS other than: “It is a matter of preference.”  After I detailed all of the above problems with Apple’s pricing of the 2015 model, their failure to update the components and the lack of a discrete graphics card and the problem I have with the Touchbar she agreed with me that Apple could not compete with the alternative I was considering.

I like my existing workflows in macOS and iOS.  But the question for me is do I want to maintain those workflows by paying such a high premium?  Or is it time to start rebuilding new workflows in Windows?

---

* Not just in terms of headline specification but internal components and well constructed enclosure.  It has always been possible to get more powerful components in Windows PCs but the enclosures have not been as good.

The SD Card Slot

Most discussions I’ve read of Apple’s move to only USB-C on MacBook Pros fail to include a key benefit of the SD Card slot.  Sure a lot of non-photographers probably don’t use the slot much, if at all.  But there is a usage scenario that would probably benefit everybody: storage expansion.

SSD storage is expensive, so most people probably have to compromise on what they keep on internal storage.  Therefore any extra storage that is easy to carry with your laptop is undoubtedly going to be useful.  Micro SD cards are available in a huge 128 GB and they are not overly expensive.  Also available are short SD to Micro SD converters.  This means it is possible to keep a high capacity Micro SD permanently attached to a laptop without worrying about it snapping off.

Low profile storage expansion

Micro SD cards as large as 128 GB are available

 

Let’s put that 128 GB size in context.  The entry level storage size for a MacBook or MacBook Pro is 256 GB.  Adding 128 GB to that increases storage by 50%.  That’s a tremendous gain.  Of course the speed is dramatically slower than soldered SSD, but it is fine for storage of files that are not accessed daily.

In the discussions I have read about the move to only USB-C I havent seen anyone discuss the removal of this expansion option.  Discussions have tended to focus on the advantages of USB-C over USB-A, Thunderbolt and/or HDMI.  I completely agree that USB-C is much better than all of these.  In particular the move from a proprietary port (Thunderbolt) to open standard (USB-C) is to be lauded.  So in general I am in agreement with the move to USB-C.  But I view the removal of the SD slot differently based on how I and others use it in practice.

Whilst it is possible to still connect an SD card with a dongle, that isn’t good enough if for an always attached storage option – it’s going to stick out of the laptop and be liable to break.  I have a 500 GB hard drive on my Mac.  That’s big, but I still find it useful to be able to offload large files to an always available 128 GB archive.  This helps a lot to maintain a minimum amount of free storage on my internal hard drive.  In general I thought the introduction of USB-C was a great idea but I do think that it could have been done whilst retaining the SD card slot.

Anyone else got thoughts on the removal of the SD Card slot or the introduction of USB-C?

#TouchBar a Touch of Genius? Er no.

The long term story of the Touch Bar will be, I believe, that it is a productivity killer.  This is certainly true whilst it is a novel technology with limited support from apps.  But I think it will likely be true in the future too regardless of app support.  This is for two reasons…

In the short term it will be impossible to use the Touch Bar without looking at the bar to see what buttons are available and where they are.  In the long term that may also be true because software keys can’t be navigated by touch like physical keys can.  So people who use the keyboard extensively in their workflows will be hindered by the removal of physical function keys as they will have to continually move their line of sight from their screens to the Touch Bar.

Even if someone can train themselves to use some functions without shifting visual focus to the Touch Bar there are other functions that require visual focus.  For example, if you are in a word processing app part of the Touch Bar becomes word suggestions (like on iOS).  You have to look at the word suggestions in order to check if you want to use any of them.  Given the signalled mass migration of tech. professionals away from Macs on the back of the unveiling of the new MacBook Pros, most people using them will be people using regular word processing apps.  So this will be a big part of the average user experience.

One thing that almost certainly won’t be corrected is the problem of the contextual nature of the Touch Bar.  A different set of controls is displayed depending on which app is being used.  On the one hand that is great for having dynamic controls that are appropriate to the app you are currently using.  On the other hand you lose the global nature of function keys.  A great example of useful global functionality is for music control.  People often listen to music whilst doing other things on their laptops – like write documents, spreadsheets, or code.  So you put some music on and continue writing your document.  Then you want to skip a track, or change the volume.  You can no longer access the required controls immediately because you are in the wrong context for those buttons.  You need to pause your typing, switch back to Spotify or iTunes to get those controls, then change the track or volume, then switch back to your writing.  It would have been difficult for Apple to make this process more intrusive.

Apple have released a laptop with a new component that inflated the cost by a few hundred dollars and simultaneously eats into people’s workflows so that they’re less productive and therefore less able to afford this expensive kit (hyperbole 🙂

I cannot think why Apple do something useful like make a new top end laptop with a full touch screen?  Is anyone impressed with the Touch Bar?  If you are add a comment here or on Google Plus.  If you’re not you can add a comment too 🙂

iOS Text Replacement Tip 1 : Email Address

How to type your email address fast on any iOS device.  This also works seamlessly across all iOS and macOS devices as text replacements are shared across iCloud.