## Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER (Updated Feb 2022)

This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program.

Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. Finally, copy and paste the top 2 ranked students of each group in a new sheet – print and hand to the boss.

However, with 100 classes this was going to turn into a time-consuming chore and one that could be prone to errors.

To solve this problem, I took advantage of Google Sheets RANK.EQ and FILTER formulas. Let’s take a peek at the formula now and go through the steps in detail later.

Let’s move on to an example.

## Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or wants to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.

Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.

Let’s look at the creation process through a recent example I created:

## Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions (Updated January 2022)

I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the `RANDBETWEEN(start val, end val)` function built into Google Sheets.

It goes a little like this. If I want to build a 5 digit random number I would:

But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?

via GIPHY

No…

So instead I decided to make a custom function with Google Apps Script to do this job for me…

## Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately, the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity: