Google Sheets Sort By Color And Google Sheets Filter By Color, With Apps Script

Excel has a handy feature that lets you sort and filter datasets by the cell color. Unfortunately, there is no native Google Sheets sort by color or filter by color.

However, with a few simple lines of Apps Script, we can implement our own version.

Filter By Color in Google Sheets

This article will show you how to implement that same feature in Google Sheets.

It’s a pretty basic idea.

We need to know the background color of the cell we want to sort or filter with (user input 1). Then we need to know which column to use to do the sorting or filtering (user input 2). Finally we need to do the sort or filter.

So step one is to to prompt the user to input the cell and columns.

I’ve implemented this Google Sheets sort by color using a modeless dialog box, which allows the user to click on cells in the Google Sheet independent of the prompt box. When the user has selected the cell or column, we store this using the Properties Service for retrieval when we come to sort or filter the data.

Google Sheets Sort By Color

At a high level, our program has the following components:

  1. Custom menu to run the Google Sheets sort by color program
  2. Prompt to ask user for the color cell
  3. Save the color cell using the Properties Service
  4. Second prompt to ask the user for the sort/filter column
  5. Save the sort/filter column using the Properties Service
  6. Show the color and column choices and confirm
  7. Retrieve the background colors of the sort/filter column
  8. Add helper column to data in Sheet with these background colors
  9. Sort/Filter this helper column, based on the color cell
  10. Clear out the values in the document Properties store

Let’s look at each of these sections in turn.

Add A Custom Menu (1)

This is simply boilerplate Apps Script code to add a custom menu to your Google Sheet:

/**
 * Create custom menu
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Tool')
        .addItem('Sort by color...', 'sortByColorSetupUi')
        .addItem('Clear Ranges','clearProperties')
        .addToUi();
}

Prompt The User For Cell And Column Choices (2, 4 and 6 above)

I use modeless dialog boxes for the prompts, which allows the user to still interact with the Sheet and click directly on the cells they want to select.

/**
 * Sort By Color Setup Program Flow
 * Check whether color cell and sort columnn have been selected
 * If both selected, move to sort the data by color
 */
function sortByColorSetupUi() {
  
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  var title='No Title';
  var msg = 'No Text';
  
  //if !colorCellRange
  if(!colorCellRange)  {
    title = 'Select Color Cell';
    msg = '<p>Please click on cell with the background color you want to sort on and then click OK</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
    dispStatus(title, msg);
  }
  
  //if colorCellRange and !sortColumnLetter
  if (colorCellRange && !sortColumnLetter) {
      
      title = 'Select Sort Column';
      msg = '<p>Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.</p>';
      msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
      dispStatus(title, msg);
  }
  
  // both color cell and sort column selected
  if(colorCellRange && sortColumnLetter) {
    
    title= 'Displaying Color Cell and Sort Column Ranges';
    msg = '<p>Confirm ranges before sorting:</p>';
    msg += 'Color Cell Range: ' + colorCellRange + '<br />Sort Column: ' + sortColumnLetter + '<br />';
    msg += '<br /><input type="button" value="Sort By Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
    msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
    dispStatus(title,msg);
    
  }
}

/**
 * display the modeless dialog box
 */
function dispStatus(title,html) {
  
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(350)
     .setHeight(200);
 
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);

}

/**
 * helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
 */
function sortByColorHelper(mode) {
  
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setColorCell();
      sortByColorSetupUi();
      break;
    case 2:
      setSortColumn();
      sortByColorSetupUi();
      break;
    default:
      clearProperties();
  }
}

The buttons on the dialog boxes use the client-side google.script.run API to call server-side Apps Script functions.

Following this, the google.script.host.close() is also a client-side JavaScript API that closes the current dialog box.

Save The Cell And Column Choices In The Property Store (3 and 5)

These two functions save the cell and column ranges that the user highlights into the Sheet’s property store:

/** 
 * saves the color cell range to properties
 */
function setColorCell() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);

}

/**
 * saves the sort column range in properties
 */
function setSortColumn() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
  
}

As a result of running these functions, we have the color cell address (in A1 notation) and the sort/filter column letter saved in the Property store for future access.

Sorting The Data (7, 8 and 9 above)

Once we’ve selected both the color cell and sort column, the program flow directs us to actually go ahead and sort the data. This is the button in the third dialog box, which, when clicked, runs this call google.script.run.sortData();.

The sortData function is defined as follows:

/** 
 * sort the data based on color cell and chosen column
 */
function sortData() {
  
  // get the properties
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');

  // extracts column letter from whatever range has been highlighted for the sort column
  
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  // get an array of background colors from the sort column
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
  
  // get the background color of the sort cell
  var sortColor = sheet.getRange(colorCell).getBackground();
  
  // map background colors to 1 if they match the sort cell color, 2 otherwise
  var sortCodes = sortColBackgrounds.map(function(val) {
    return (val[0] === sortColor) ? [1] : [2];
  });
  
  // add a column heading to the array of background colors
  sortCodes.unshift(['Sort Column']);
  
  // paste the background colors array as a helper column on right side of data
  sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
  sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
  
  // sort the data
  var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);  
  dataRange.sort(lastCol+1);
  
  // add new filter across whole data table
  sheet.getDataRange().createFilter();

  // clear out the properties so it's ready to run again
  clearProperties();
}

And finally, we want a way to clear the properties store so we can start over.

Clear The Property Store (10 above)

This simple function will delete all the key/value pairs stored in the Sheet’s property store:

/**
 * clear the properties
 */
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}

Run The Google Sheets Sort By Color Script

If you put all these code snippets together in your Code.gs file, you should be able to run onOpen, authorize your script and then run the sort by color tool from the new custom menu.

Here’s the sort by color tool in action in Google Sheets:

Google Sheets sort by color

You can see how all of the green shaded rows are sorted to the top of my dataset.

Note that this sort by color feature is setup to work with datasets that begin in cell A1 (because it relies on the getDataRange() method, which does the same).

Some improvements would be to make it more generalized (or prompt the user to highlight the dataset initially). I also have not included any error handling, intentionally to keep the script as simple as possible to aid understanding. However, this is something you’d want to consider if you want to make this solution more robust.

Google Sheets Sort By Color Template

Here’s the Google Sheet template for you to copy.

(If you’re prompted for permission to open this, it’s because my G Suite domain, benlcollins.com, is not whitelisted with your organization. You can talk to your G Suite administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

If GitHub is your thing, here’s the sort by color code in my Apps Script repo on GitHub.

Google Sheets Filter By Color

The program flow is virtually identical, except that we filter the data rather than sort it. The code is almost exactly the same too, other than variable names being different and implementing a filter instead of a sort.

Rather than sorting the data, we create and add a filter to the dataset to show only the rows shaded with the matching colors:

Filter By Color in Google Sheets

The filter portion of the code looks like this:

// remove existing filter to the data range
if (sheet.getFilter() !== null) {
  sheet.getFilter().remove();
}

// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();

// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);

// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);

If you want a challenge, see if you can modify the sort code to work with the filter example.

Google Sheets Filter By Color Template

Feel free to copy the Google Sheets filter by color template here.

(If you’re prompted for permission to open this, it’s because my G Suite domain, benlcollins.com, is not whitelisted with your organization. You can talk to your G Suite administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

Or pull the code directly from the GitHub repo here.

Add-On

Lastly, there is an add-on, called Sort Range Plus, which allows you to do a sort by color. I have not used it however.

2019 In Review And A Look Forward To 2020

Best wishes to all of you for 2020!

Family hike
Photo from a recent family hike up our local mountain. A great way to finish 2019!

This is my 5th annual review post.

I’m proud and thankful that I get to write this post every year, because it means I’m still running my own show and my business is still going.

At this time of year I like to pause. Stop doing the busy work. Step off the treadmill and look back at 2019 to acknowledge what I achieved, what went well, and what can be improved. To look forward to 2020 and make plans for the year ahead.

2019 was my most successful year as an independent, small-business owner. It was great to hit some big milestones this year and see the previous years of hard work pay off.

I’m full of optimism for 2020 and think it’ll be another fantastic year, building on the success of 2019.

Did I Meet My 2019 Goals?

My goals for 2019 were:

  • Create a follow-up Apps Script course — Yes, I achieved this!
  • Create two other courses — 50% success. I launched one other new course
  • Attend the Google Next 19 conference — Yes, it was one of the highlights of the year
  • Continue to grow the community on this site and the online school — Yes (this was a rather meaningless goal without any metrics though!)
  • Hold more webinars in 2019 — This I completely failed at, although I was a guest on a number of webinars and podcasts
  • Deepen my digital analytics and marketing knowledge, and also continue experimenting with data science and Google Cloud topics — I did improve my knowledge of SEO this year, but didn’t really make any progress in other topics. However, I’ve come to realize that my expertise doesn’t lie in this area and my time is best spent on my core business. I do still want to dive deeper into Google Cloud this year though.

2019 Highlights

Online Courses

I launched two new courses:

The Collins School of Data continues to grow with over 20,000 students now! (Thank you all!)

Website

  • I published 17 new pieces of content on benlcollins.com (down on previous years and something I want to increase again in 2020). However, almost all of them are long-form tutorials.
  • The traffic to benlcollins.com continues to climb, reaching around 170k users/month for around 300k pageviews/month. Overall, the site has seen 3.4 million pageviews this year and 1.8 million users. Wow!
  • The traffic grew in the first quarter of the year and then largely plateaued for the rest of the year, so I have work to do to grow again.

My favorite posts of the year were:

Google Sheets Tips Newsletter

  • I sent out a Google Sheets tip every Monday of this year except two (once I was sick and once for Christmas week). I was really happy to achieve this consistency and I’ve seen great growth and engagement around this list.
  • Over 30k people are now signed up to my email list
  • Popular emails were the formula challenges and this long form email on the mind-blowing facts behind card shuffling

Travel & Conferences

This year I traveled three times for work:

  • In April, I attended the Google Next conference in San Francisco. It’s one of the highlights of the year for me because of the energy and people I spend time with. It’s such an inspiration for the rest of the year. Like 2018, I live blogged whilst I was there, covering anything related to Google Sheets. I’ll be there again in 2020. Holler if you’ll be there!
  • In August Google invited me to New York to give a presentation to the Google Sheets team about my work. This was a real honor! I enjoyed meeting the team responsible for this product that I love.
  • In December, I traveled to Copenhagen, Denmark to lead a 75-person workshop for the UN, teaching advanced Google Sheets techniques to help their team transition from Excel to Google Sheets.

Becoming a Google Developer Expert

2019 started in the best possible way!

I became a Google Developer Expert (GDE) in January. It’s been an honor to receive this award and be part of the program. The relationships I’ve developed with other GDEs and Googlers have been fantastic and inspirational. The G Suite GDE family are a great bunch!

I was sad to miss the 2019 GDE summit but hopefully I’ll be there in 2020!

Non-Work Highlights

  • Moving to a mountain town. I LOVE, LOVE, LOVE having the woods and mountains easily accessible. It’s made a huge difference to our lifestyle. (I wrote more about our move from a work perspective and an outdoors perspective.)
  • A huge benefit of living in the mountains are the weekly hikes (like this one up Loudon Heights on the A.T.)
  • A wonderful trip home to the UK to visit family. My brother came over from Australia with his family and it was a joy to see all the cousins play together. My eldest son Dominic, upon meeting his 1 year-old cousin Henry for the first time, exclaimed, “He’s a little bit fat isn’t he?”. It made us all laugh.
  • During this trip, my brother and I had a sublime day hiking in Snowdonia National Park.
  • On the three work trips I did, I explored each city on foot (San Francisco, New York City and Copenhagen).
  • Getting fit again with the weekly hiking, running and biking. It hasn’t all been plain sailing though with injuries and illnesses still impacting my year, but I’m in much better shape than I was in Florida last year.
  • The weekly brainstorming hike with my wife. This has rapidly become one of the highlights of my week.
  • Watching my boys grow. Teaching them about the world. Taking them on hikes. Laughing at their silly questions and funny actions. It’s both the hardest and most rewarding thing I’ve ever done.
  • I read 24 books this year, beating last year’s tally (my long term goal is still 52 books, so some way to go!). My highlights this year were:
    • Company of One – Paul Jarvis
    • Atomic Habits – James Clear
    • The Paris Architect: A Novel – Charles Belfoure
    • Kochland – Christopher Leonard
    • Shadow Divers – Robert Kurson
    • Seveneves – Neal Stephenson
    • The Goldfinch – Donna Tart
  • Other things I enjoyed in 2019:

Challenges In 2019

In recent years I’ve experienced a number of health challenges, and this year was no exception. A nasty chest infection in March turned into pneumonia and landed me in hospital again. I recovered completely but work and life was impacted (another course launch postponed, sigh).

I was pretty healthy through the summer with lots of time outdoors. The trip to the UK was tiring and took some time to recover from. Right at the end of 2019, in the week before Christmas, another chest infection completely knocked me off my feet and necessitated a trip to the ER. A dose of meds straightened me out but it was a rough week.

After years of slow decline in my fitness levels, I reversed the trend this year and am now fitter than I was at the start of the year. I can run and hike much stronger than my Florida self could.

The transition from flat road-running in Florida to mountain running in West Virginia came at a price though. I suffered shin splints for the first time in my life over the Summer and am still managing my running now. I’m confident I’ll be back to normal on that front soon though.

By far the biggest challenge of my life continues to be the balance of being a good husband and father whilst running my own business and keeping fit. It’s hard to not feel like you’re failing on all these fronts, despite going full tilt all the time. All one can do is keep trying!

Looking Forwards To 2020

New Initiatives

  • In January, I’ll be launching a new website, Excel to Sheets, to help organizations migrate from Microsoft Excel to Google Sheets. This is one of the big project areas I want to work on in 2020. Expect to see some blog posts tackling objections that Excel users have to Google Sheets.
  • In March, I’ll be running an online event for all things Google Sheets. It’s going to be huge and very exciting! More details coming soon!

2020 Work Goals

  • Publish more high-quality tutorials than in 2019 (target > 17)
  • Hit 50k newsletter subscribers and send out a tip every Monday
  • Update my existing Google Sheets courses
  • Create one new Google Sheets course
  • Run 10 in-person workshops
  • Re-brand my digital assets
  • Find a VA to help with the business
  • Live-blog Google Next 2020 again
  • Work through this book from last year that I haven’t started yet Data Science on the Google Cloud Platform

Other 2020 Goals

  • My overall number 1 goal for 2020 is to be healthy
  • Fitness goals: be active 5 times/week (a mix of spin classes, runs and at least 1 run/hike up the mountain)
  • Keep up the weekly brainstorming hike with my wife
  • Read 30 books

Thank You

Thank you for giving me this opportunity to share my experiences and knowledge, and carve out this teaching niche.

My mission remains to create a world-class resource for learning Google Sheets and data analysis. The work continues!

Finally, best wishes to all of you for 2020!

Cheers,
Ben

Previous years

Goal Seek in Google Sheets

Goal Seek for Sheets is an Add-On for Google Sheets for doing Goal Seek type data analysis.

In October 2019, Google launched an official Add-On, called “Goal Seek for Sheets”, and it is that Add-On that this tutorial references.

1. What is Goal Seek?

It’s a tremendously powerful and useful technique in data analysis. It’s a process where you set an output you want to achieve (e.g. break even, sell 10k units, save $1m) and let the computer find the input value that will get you there (e.g. 500 attendees, $100k capital lump sum, save $8k/year).

There are three components: 1) the unknown input variable, 2) the equation or calculation that is performed on the input variables to get the output, and 3) the known output.

The Goal Seek algorithm performs a series of “what-if” calculations by plugging in different input values. Each guess (hopefully) gets closer and closer to the solution.

For example, a classic use case of Goal Seek is to determine the number of sales required to break even, given other variables like fixed costs etc.

2. How do you use Goal Seek in Google Sheets?

Imagine Jennifer runs an annual conference for Google Sheet developers called “Sheet Freakz 🤟”.

She has a great venue picked out with room for 500 and she’s confident she can fill it. She knows what her costs are — the rental fee for the room, the cost of catering, the cost of promoting the conference — and she has agreed a $1,500 fee with 15 Google Sheets experts to come and talk about the latest and greatest in Sheets developments.

(Editor note: I wish this was a real conference!! 😄)

What price must she charge to cover her costs?

This is a classic break even cost analysis example that the Goal Seek Add-On is ideally suited for solving.

Setting up the Sheet

The first step is to simply add all of the known variables into a sheet, like so:

Goal Seek Variables in Google Sheet

These are the variables that Jennifer knows at the start of her problem.

Next, add a line for the registration fee per attendee, but set it to 0 for now. I’ve highlighted the cell yellow to indicate that it’s the solution cell that I want Goal Seek to solve for:

Goal Seek Variables

Finally, add a profit line, which is my revenue (# of attendees * registration fee) less expenses (fixed costs + (# of speakers * speaker fee)):

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ) )

Goal Seek setup

Of course, initially, my profit is -$47,500 because I have no attendees and hence $0 revenue.

It’s time to use Goal Seek and let it find the break even registration fee for us.

How do you add Goal Seek in Google Sheets?

Goal Seek is an Add-On, which means you need to add it to your Google Sheet before you can use it.

Search for “Goal Seek” in the Add-Ons marketplace, found under the menu Add-ons > Get add-ons

The official Google Add-On information page will appear.

Click to install. That’s all it takes to add it to your Sheets.

Goal Seek for Sheets

You can also find it in the G Suite marketplace directly by clicking here.

Using Goal Seek

Open the Goal Seek sidebar: Add-ons > Goal Seek > Open

There are three pieces of information you need to enter:

i) Set Cell

Jennifer wants to know what price to charge to break even. In other words, what’s the minimum ticket price to ensure her profit is $0 and she doesn’t lose any money on the conference.

The “Set Cell” is the one we want to specify a value for. It’s the target we’re aiming for.

It’s the cell with the calculation formula in.

** With the Goal Seek sidebar open, click on the cell with the formula, which is cell B10 in this case (1). Add that reference to the Goal Seek solver by clicking on the grid icon next to the Set Cell box (2). This will auto-populate with the cell B10 reference. **

Select cells in Goal Seek for Sheets

ii) To Value

Next, type in the value of the output you want to achieve in the “Set Cell” box.

In this example, we want to set the profit value to 0, so we simply type 0 into this input box.

iii) By Changing Cell

What input variable do we want to vary to solve our equation?

In this case it’s the registration fee. It’s the variable that Jennifer is trying to find, such that her profit is $0 and she breaks even.

Select the cell that holds this variable and then click on the grid icon next to the input field to auto-populate it.

Read it out loud to understand what you’re asking the application to do: “Set Cell X To Value Y By Changing Cell Z”.

In our case, “Set Cell Profit To $0 By Changing Cell Registration Fee” or even cleaner “Set Profit To $0 By Changing Registration Fee”.

When you have all three inputs filled in for the Goal Seek application, the Solve button will turn blue and become active.

Press it.

The registration fee value will start jumping around all over the place as the computer tries different guesses to see what brings the profit value closer to 0.

Eventually it’ll find a solution and notify you that it’s done!

Goal Seek to determine break even cost

In this example, it’s found that the break even registration fee is $94.99999 dollars, or $95. Great!

Click here to open a Google Sheet template with all the examples from this tutorial >>

(Feel free to make your own copy: File > Make a copy…

If the file won’t open without permission, please open in an incognito window and copy from there.)

Manual Checks

It’s always a good idea to check the final solution that the Add-On finds, and not just trust it blindly.

In our example, it’s very simple to check that the two sides of the equation balance.

Jennifer’s expenses to run the conference are:

Expenses
$25,000 fixed costs + ( 15 speakers @ $1,500 each ) = 25,000 + ( 15 * 1,500 ) = $47,500

And her revenue on the other side of the equation will be:

Revenue
500 attendees * $94.99 registration fee (Goal Seek solution) = $47,499.99

The difference is simply a rounding error.

This is good.

The result from the Goal Seek is indeed a solution that gets Jennifer the break even registration price.

Another way to look at how the Goal Seek solver works is to visualize it. A very simplified version might look something like this:

Attempt 1

The computer has no idea what the solution is, so it makes a guess. For example, it might overestimate the result:

Guess 1: Overestimate

Attempt 2

The computer makes another guess. This time it might underestimate the result:

Guess 2: Underestimate

(It won’t always be a neat over/under/over/under guess. For example, if it guesses low, it might take many guesses before the first “over” guess happens. So this over/under flow is just to illustrate the concept.)

Attempt 3

With each additional “guess” the computer gets more accurate, because it uses the information from prior guesses to get closer to the solution. It might still overestimate, as shown here, but it’s getting closer to the solution:

Guess 3: Overestimate

Attempts 4 onwards

So on and so forth, as the computer makes guesses that get closer and closer, under, over, under, over, under, over, etc. until the solution is found:

Converging on solution

The program converges on the solution.

3. Other features of the Goal Seek Add-On

These features are all found in the sidebar underneath the input section for the Goal Seek variables.

Options

Under the Options menu, you can adjust the default settings for the Goal Seek solver.

You can change the 1) max number of iterations, 2) the tolerance (how accurate you need to be) and 3) the maximum time limit for the process, in seconds.

Goal Seek Settings

I’d suggest that the default settings will suffice for the majority of scenarios, but it’s good to know that you can make these changes should you need to.

Solve Status

The Solve Status box displays helpful information about the current Goal Seek solution.

It lets you know when the algorithm is finished, what the final status is, how many iterations it required and how long it took.

Solve Status in Goal Seek for Sheets

History

Access previous runs of the Goal Seek solver under the History menu.

Use the drop down menu to choose a prior solution based on a timestamp.

Goal Seek History

Error Messages

Occasionally the Goal Seek solver fails to find a solution.

One reason might be that the computer guesses get successively further away from the actual solution. They diverge away from the solution.

Should this happen, you’ll see an error message like this:

Goal Seek error message

4. More Goal Seek examples

Conference Break Even Example For Attendee Numbers

In the conference example above, instead of knowing how many people would attend, suppose Jennifer knew the registration fee.

She charges $299 for the registration fee and wants to know how many attendees she requires to break even?

The “changing cell” in this case becomes the number of attendees, rather than the registration fee.

The setup would be:

Goal Seek conference Example 2

The formula in cell B10 does not change from the original example. It’s still:

= ( B7 * B8 ) - ( B4 + ( B5 * B6 ))

The Goal Seek settings are:

Settings for conference example 2

Run Goal Seek and the answer comes back as 158.86.

In other words Jennifer needs 159 attendees paying a registration fee of $299 to break even.

Mortgage Calculation Example

Suppose you’re looking to buy a new house. You have an upper limit for your monthly payments of $1,500.

The other known variables in this case are: it’s a 30 year term with an annual interest rate of 4.5%.

What’s the maximum amount you can borrow?

Goal Seek can solve this for you.

Firstly, setup the sheet with the known variables in your Sheet:

Mortgage Goal Seek Example

The payment equation uses the PMT function in Google Sheets in cell B8:

= -PMT( B6 / 12 , B5 , B7 )

In Goal Seek, set the “Set Cell” to be this equation in cell B8.

Set the “To Value” to $1,500 (the maximum monthly payment that can be tolerated).

Set the “By Changing Cell” to the Amount Borrowed in cell B7 (currently $0.00).

Click Solve and let Goal Seek find your solution.

The algorithm will churn through the possible solutions until it settles on one that satisfies your tolerance (accuracy) setting.

In this case, the maximum amount you could borrow is $296,041.75.

Mortgage Solver Solution

Retirement Calculation Example

Suppose you want to retire with a pot of $1.5m in 40 years time. You’re confident of getting a 5% return on your investments.

What’s the annual contribution you need to make each year to hit this target?

Let’s use Goal Seek to find out.

Firstly, setup the sheet with the known variables in your Sheet:

Retirement Goal Seek Example

The calculation of the retirement pot value uses the Future Value function, the FV function, in Google Sheets in cell B7:

= FV( B5 , B4 , -B6 , 0 , 0 )

In Goal Seek, set the “Set Cell” to be this equation in cell B7.

Set the “To Value” to $1,500,000 (your target retirement pot).

Set the “By Changing Cell” to the Annual Contribution in cell B6 (currently $0.00).

Click Solve and let Goal Seek find your solution.

In this case, you need to contribute $12,417 each year to hit your retirement pot target of $1.5m.

Retirement Solution

Click here to open a Google Sheet template with all the examples from this tutorial >>

(Feel free to make your own copy: File > Make a copy…

If the file won’t open without permission, please open in an incognito window and copy from there.)

Resources

Google Documentation on the Goal Seek feature.

How to connect the Strava API with Google Sheets and Data Studio

Editor’s Note: Strava updated their OAuth workflow (see here), which breaks the code shown below. I’ll post a fix as soon as I can.

This post looks at how to connect the Strava API with Google Sheets and create a visualization in Google Data Studio.

Strava api with google sheets and data studio

Strava is an insanely good app for athletes to track their workouts. I use it to track my running, biking and hiking, and look at the data over time.

This whole project was born out of a frustration with the Strava app.

Whilst it’s great for collecting data, it has some limitations in how it shows that data back to me. The training log shows all of my runs and bike rides, but nothing else. However, I do a lot of hiking too (especially when I’m nursing a running injury) and to me, it’s all one and the same. I want to see it all my activities on the same training log.

So that’s why I built this activity dashboard in Google Data Studio. It shows all of my activities, regardless of type, in a single view.

Connecting the Strava API with Google Sheets

To connect to the Strava API with Google Sheets, follow these steps:

Setup your Google Sheet

  1. Open a new Google Sheet (pro-tip: type sheet.new into your browser window!)
  2. Type a header row in your Google Sheet: “ID”, “Name”, “Type”, “Distance (m)” into cells A1, B1, C1 and D1 respectively
  3. Open the Script Editor (Tools > Script editor)
  4. Give the script project a name e.g. Strava Sheets Integration
  5. Create a second script file (File > New > Script file) and call it oauth.gs
  6. Add the OAuth 2.0 Apps Script library to your project (Resources > Libraries...)
  7. Enter this ID code in the “Add a library” box: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
  8. Select the most recent version of the library from the drop-down (version 34 currently — September 2019) and hit “Save”

Add the code

If you’re new to API and Apps Script, check out my API Tutorial For Beginners With Google Sheets & Apps Script.

In your oauth.gs file, add this code:

var CLIENT_ID = '';
var CLIENT_SECRET = '';

// configure the service
function getStravaService() {
  return OAuth2.createService('Strava')
    .setAuthorizationBaseUrl('https://www.strava.com/oauth/authorize')
    .setTokenUrl('https://www.strava.com/oauth/token')
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction('authCallback')
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('activity:read_all');
}

// handle the callback
function authCallback(request) {
  var stravaService = getStravaService();
  var isAuthorized = stravaService.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

Also available in this GitHub oauth.js repo.

In your code.gs file, add this code:

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Strava App')
    .addItem('Get data', 'getStravaActivityData')
    .addToUi();
}

// Get athlete activity data
function getStravaActivityData() {
  
  // get the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  // call the Strava API to retrieve data
  var data = callStravaAPI();
  
  // empty array to hold activity data
  var stravaData = [];
    
  // loop over activity data and add to stravaData array for Sheet
  data.forEach(function(activity) {
    var arr = [];
    arr.push(
      activity.id,
      activity.name,
      activity.type,
      activity.distance
    );
    stravaData.push(arr);
  });
  
  // paste the values into the Sheet
  sheet.getRange(sheet.getLastRow() + 1, 1, stravaData.length, stravaData[0].length).setValues(stravaData);
}

// call the Strava API
function callStravaAPI() {
  
  // set up the service
  var service = getStravaService();
  
  if (service.hasAccess()) {
    Logger.log('App has access.');
    
    var endpoint = 'https://www.strava.com/api/v3/athlete/activities';
    var params = '?after=1546300800&per_page=200';

    var headers = {
      Authorization: 'Bearer ' + service.getAccessToken()
    };
    
    var options = {
      headers: headers,
      method : 'GET',
      muteHttpExceptions: true
    };
    
    var response = JSON.parse(UrlFetchApp.fetch(endpoint + params, options));
    
    return response;  
  }
  else {
    Logger.log("App has no access yet.");
    
    // open this url to gain authorization from github
    var authorizationUrl = service.getAuthorizationUrl();
    
    Logger.log("Open the following URL and re-run the script: %s",
        authorizationUrl);
  }
}

Also available in this GitHub code.js repo.

Note about the params variable

Have a look at the params variable:

var params = '?after=1546300800&per_page=200'

The ‘after’ parameter means my code will only return Strava activities after the date I give. The format of the date is epoch time and the date I’ve used here is 1/1/2019 i.e. I’m only returning activities from this year.

(Here’s a handy calculator to convert human dates to epoch timestamps.)

The other part of the params variable is the ‘per_page’ variable, which I’ve set to 200. This is the maximum number of records the API will return in one batch.

To get more than 200, you need to add in the ‘page’ parameter and set it to 2,3,4 etc. to get the remaining activities, e.g.

var params = '?after=1546300800&per_page=200&page=2'

Eventually you’ll want to do that programmatically with a while loop (keep looping while the API returns data and stop when it comes back empty handed).

Note about parsing the data

The script above parses the response from the API and adds 4 values to the array that goes into the Google Sheet, namely: ID, Name, Type and Distance.

You can easily add more fields however.

Look at the Strava documentation to see what fields are returned and select the ones you want. For example, you add total elevation gain like this:

activity.total_elevation_gain

If you add extra fields to the array, don’t forget to change the size of the range you’re pasting the data into in your Google Sheet.

The array and range dimensions must match.

Setup your Strava API application

You need to create your app on the Strava platform, so that your Google Sheet can connect to it.

Login to Strava and go to Settings > My API Application or type in https://www.strava.com/settings/api

This will take you to the API application settings page.

Give your application a name, and enter a website and description. You can put anything you want here, as it’s just for display.

The key to unlock the Strava API, which took me a lot of struggle to find, is to set the “Authorization Callback Domain” as

script.google.com

(Hat tip to this article from Elif T. Kuş, which was the only place I found this.)

Strava API application setup

Next, grab your client ID and paste it into the CLIENT_ID variable on line 1 of your Apps Script code in the oauth.gs file.

Similarly, grab your client secret and paste it into the CLIENT_SECRET variable on line 2 of your Apps Script code in the oauth.gs file.

Copy these two values:

Strava API application

And paste them into your code here:

Oauth client id and secret in Apps Script

Authorize your app

Run the onOpen function from the script editor and authorize the scopes the app needs (external service and spreadsheet app):

Apps Script authorization

If your process doesn’t look like this, and you see a Warning sign, then don’t worry. Click the Advanced option and authorize there instead (see how in this Google Apps Script: A Beginner’s Guide).

Return to your Google Sheet and you’ll see a new custom menu option “Strava App”.

Click on it and select the “Get data” drop-down.

Nothing will happen in your Google Sheet the first time it runs.

Return to the script editor and open the logs (View > Logs). You’ll see the authorization URL you need to copy and paste into a new tab of your browser.

This prompts you to authorize the Strava app:

Strava API with Google Sheets authorization workflow

Boom! Now you’ve authenticated your application.

For another OAuth example, have a look at the GitHub to Apps Script integration which shows these steps for another application.

Retrieve your Strava data!

Now, the pièce de résistance!

Run the “Get data” function again and this time, something beautiful will happen.

Rows and rows of Strava data will appear in your Google Sheet!

Connect Strava API with Google Sheets

The code connects to the athlete/activities endpoint to retrieve data about all your activities.

In its present setup, shown in the GIF above, the code parses the data returned by the API and pastes 4 values into your Google Sheet: ID, Name, Type and Distance.

(The distance is measure in meters.)

Of course, you can extract any or all of the fields returned by the API.

In the data studio dashboard I’ve used some of the time data to determine what day of the week and what week of the year the activity occurred on. I also looked at fields measuring how long the activity took.

Setting a trigger to call the API automatically

Once you’ve established the basic connection above, you’ll probably want to set up a trigger to call the API once a day to get fresh data.

You’ll want to filter out the old data to prevent ending up with duplicate entries. You can use a filter loop to compare the new data with the values you have in your spreadsheet and discard the ones you already have.

Building a dashboard in Google Data Studio

Google Data Studio is an amazing tool for creating visually stunning dashboards.

I was motivated to build my own training log that had all of my activities showing, regardless of type.

First, I created some calculated fields in Apps Script to work out the day of the week and the week number. I added these four fields to my code.gs file:

(new Date(activity.start_date_local)).getDay(), // sunday - saturday: 0 - 6
parseInt(Utilities.formatDate(new Date(activity.start_date_local), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "w")), // week number
(new Date(activity.start_date_local)).getMonth() + 1, // add 1 to make months 1 - 12
(new Date(activity.start_date_local)).getYear() // get year

And I converted the distance in metres into a distance in miles with this modification in my Apps Script code.gs file:

(activity.distance * 0.000621371).toFixed(2), // distance in miles

From there, you simply create a new dashboard in Data Studio and connect it to your Google Sheet.

The activity log chart is a bubble chart with day of the week on the x-axis and week number as the y-axis, both set to average (so each appears separately). The bubble size is the Distance and the dimension is set to Name.

Bubble chart in Data Studio

Next, I added a Year filter so that I can view each year separately (I’ve got data going back to 2014 in the dataset).

To complete the dashboard, I added a Strava logo and an orange theme.

Strava api with google sheets and data studio

(Note: There’s also an open source Strava API connector for Data Studio, so you could use that to create Strava visualizations and not have to write the code yourself.)

Next steps for the Strava API with Google Sheets

This whole project was conceived as a way to explore the Strava API with Google Sheets. I’m happy to get it working and share it here.

However, I’ll be the first to admit that this project is still a little rough around the edges.

But I am excited to have my Strava data in a Google Sheet now. There are TONS of other interesting stories / trends that I want to explore when I have the time.

There is definitely room for improvement with the Apps Script code. In addition to those mentioned above, and with a little more time, I would bake the OAuth connection into the UI of the Google Sheet front end (using a sidebar), instead of needing to grab the URL from the Logs in your script editor.

And the Data Studio dashboard was rather hastily thrown together to solve the issue of not seeing all my activity data in once place. Again, there’s a lot more work you could do here to improve it.

To be continued…

Time for a run first though!

Add A Google Sheets Button To Run Scripts

Learn how to add a Google Sheets button to run your Google Apps Script functions.

Let’s see how this works with a simple example.

Imagine you have an invoice template you use on a regular basis, but it’s a pain to clear out all the values each time you need to start over. Well, you can add a button to Google Sheets so you can run scripts and clear your invoice with a single button click.

Google Sheets button

Let’s start by creating a basic invoice template with placeholders to hold information:

Add button to Google Sheets invoice example

The user can enter information into cells B5, B8, E5 and E6 (shown in yellow).

In the script editor, accessed through Tools > Script Editor, add a very simple script to clear these specific cells out:

function clearInvoice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var invoiceNumber = sheet.getRange("B5").clearContent();
  var invoiceAmount = sheet.getRange("B8").clearContent();
  var invoiceTo = sheet.getRange("E5").clearContent();
  var invoiceFrom = sheet.getRange("E6").clearContent(); 
}

You can run this function from the script editor and it will clear out the contents of the invoice.

But that’s a pain.

You don’t want to have to open up the script editor every time. You want to do that directly from your Google Sheet.

To do that, add a Google Sheets button.

You add a button via the Insert > Drawing menu.

This brings up the drawing editor where you can easily add a box and style it to look like a button:

Google Sheets button drawing

When you click Save and Close, this drawing gets added to your Google Sheet. You can click on it to resize it or drag it around to reposition it.

To assign a script, click the three little dots in the top right of the drawing and select Assign Script:

Google Sheets button assign script

Then type in the name of the function you want to run from your Apps Script code. In this example, choose the clearInvoice function (i.e. like the code above!).

Now, when you click the button it will clear out the invoice for you!

Button with apps script in google sheets

Note: to edit or move the button after you’ve assigned it to a script, you now need to right-click on it.

See Create, insert & edit drawings in the Google Documentation for more info on the Drawing feature.