Tag: Google Sheets

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

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.

Continue reading “Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER”

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML

One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the same unit. Before I can run my code I need to determine what modules are running and what units we are up to for me to run my automated code.

To do this I created a dialog box when the code is run from the add-on bar. In a few clicks, I can then choose the relevant modules and units and then run the selected code.

Let’s take a look at what the dialog box looks like:

Dialog Form in Google Apps Script

Upon “Submit”, the dialog box returns an array  of objects of checked values from the radio buttons that can be uses in the server-side Google Apps Script.

In this tutorial we will look at the following :

Continue reading “Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets”

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

Google Apps Script, 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:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, Google Apps Script

Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.

For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Continue reading “How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.”

Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing

What is Sections to Sheets?

Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation export and printing.

You can find Sections to Sheets at the Google Chrome Webstore.

Chrome Web Store Badge

Example 1

Let’s say you want to print out a list of expenditures by department and you want each department to start on a new page. You’ll also want to keep the same header for each page.

Example 2

You might want to export a list of grades by class number. You sort the grades by class and then export the list with each class starting on a new page with a header and footer.

Sections to Sheets can help you achieve this quickly by creating a new Google Spreadsheet and separating each selection and putting it into a new Sheet(tab) with or without headers and footer. This will enable you to quickly export or print your sheets.

Continue reading “Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing”

Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.

Google Apps Script

Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width just does not budge.

copied Google Sheet
When you want to copy and paste this…
Paste without column widths.
…but you get this.

Continue reading “Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.”

Google Apps Script – Disable Enable Submit Button in Sidebar

Google Apps Script, Javascript, Jquery, HTML

I was working on a Google Apps Script project lately in Google Sheets that set up parameters in a sidebar and then ran the process once the user clicked the “Submit” button. The problem was that the process was taking a while and that “Submit” Button was ripe to be clicked multiple times by the impatient user, before the server-side code could even finish it’s operation.

To fix this I needed to disable the submit button once it had been clicked and then enable it again once the server-side process was complete. Here, I need to:

  1. Disable the button and get the data from the client-side Javascript inside my sidebar’s html file.
  2. Do something awesome with it server-side.
  3. Upon the completion of the server-side awesome, call back to the html file and enable the button again.

The Example

I have a very simple Google Sheets Side Bar with a “Submit” and “Cancel” button. When the “Submit” button is clicked it calls the function submittington (can he get any more creative? No. No he can’t).

This function then disables the “Submit” button sends a variable to the client side code.gs and displays it stylishly in the sheet multiple times for the users viewing pleasure. After the code.gs function is executed, it calls back to the client-side submittington function and enables the button.

Behold!!! The example:

Disable Enable Button in Sidebar Google Apps Script

Continue reading “Google Apps Script – Disable Enable Submit Button in Sidebar”

Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. 

Google Apps Script and Google Sheets

Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colors after each category in your sort column is complete.

The Example

I have the following list of numbers in column 1 and 2. I have sorted these numbers by my Grouping Column of planets in column 3. After each grouping, I have alternated the background color to make the transition easier to read.

Alternating color by section - Google Apps Script

The Code

Continue reading “Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. “

Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets

Google Apps Script and Google Sheets

Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do something like those sections like put each section of students in their own sheet or set alternating colors for each section to make the sheet easier to read.

Alternatively, you may want to grab sales data by region or sales items by a particular category and work with them in Google Apps Script.

The Example

Let’s say we want to get the range values of the follow sheet by planets. We will be categorizing our data by the Grouping, column C.

Grouping By Planet - Google Apps Script

First we don’t want to take into account the headers on the first row. Our first grouping will be Mars, followed by Jupiter, Uranus and Mercury. We want to know which column that each category starts on and how many of that category there are.

The Code

Continue reading “Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets”

How do I reverse the Rows in A Column in Google Sheets?

Reverse Rows in Column in Google Sheets
My favourite Killjoy members. Column A – From most favourite. Column B Saving the best for last.

Sometimes you have a need to reverse a list quickly in Google Sheets.

That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not in alphabetical or numeric order?

Below are 3 ways to reverse your data:

For the examples below, I’ll be using a list of my favourite Killjoys characters. Yeah, I’m a sci-fi geek.

Continue reading “How do I reverse the Rows in A Column in Google Sheets?”