Crosshair Highlight in Google Sheets with Apps Script

Let's create a new Highlight menu in Google Sheets with Google Apps Script to perform row, column and crosshair highlight.

The Highlight menu and crosshair highlight
The Highlight menu and crosshair highlight

If you don't care about the steps to do it, feel free to scroll to the end and copy paste the code! 😆

Background

Google Sheets has keyboard shortcuts to perform row and column highlight but not both:

However, I've configured my MacBook CTRL + SPACE shortcut to something else and it overridden the Sheets shortcut. 🤷🏽‍♀️ It's annoying and Sheets doesn't support crosshair highlight yet which is extremely useful for viewing big data sheet.

So... let's roll our own function with Google Apps Script to do so.

Creating the Highlight menu

In a spreadsheet, Open Tools > Script editor.

Create a new File, name it Code.gs or whatever name you like.

We will be creating a new Highlight menu with 3 sub menus:

Here is the code:

function createHighlightMenu() {
SpreadsheetApp
.getActiveSpreadsheet()
.addMenu('Highlight', [
{ name: 'Highlight row', functionName: 'highlightRow' },
{ name: 'Highlight column', functionName: 'highlightColumn' },
{ name: 'Highlight crosshair', functionName: 'highlightCrosshair' }
]);
}

function highlightRow() {
// code later
}

function highlightColumn() {
// code later
}

function highlightCrosshair() {
// code later
}

In the createHighlightMenu function, the name under the .addMenu function refers to the menu display name on the Sheets UI and functionName is the name of the function. We will code these functions in a bit.

Add Highlight menu automatically

Now we have the function to create Highlight menu, we need a way to add the menu automatically everytime when we open the Sheets.

We can use Triggers to do so.

On the left menu, select Triggers, then create a new Trigger.

Steps to open Triggers
Steps to open Triggers

Follow my configuration below and save it:

Trigger configuration
Trigger configuration

Basically we are setting our createHighlightMenu function earlier (the Highlight menu) to run everytime when the spreadsheet opens.

Next, go back the spreadsheet and hit refresh. Wait for a moment, the Highlight menu should appear at the end of the menu.

The Highlight menu
The Highlight menu

Creating the Highlight functions

Go back to our Code.gs file. Let's start coding the Highlight functions. The basis of all 3 highlight functions (row, column, crosshair) are the same. Therefore, in order to save some coding effort, we will add one more function highlight(type) with a parameter to handle the actual operation.

function highlight(type) {
// code later
}

function highlightRow() {
highlight('row');
}

function highlightColumn() {
highlight('column');
}

function highlightCrosshair() {
highlight('crosshair');
};

Great, now let's look at the highlight function.

function highlight(type) {
const ss = SpreadsheetApp.getActive();
const s = ss.getActiveSheet();
const a = ss.getActiveCell();

const col = a.getColumn();
const row = a.getRow();
const maxColumns = s.getMaxColumns();
const maxRows = s.getMaxRows();

const c = s.getRange(1, col, maxRows, 1); // the whole column
const r = s.getRange(row, 1, 1, maxColumns); // the whole row
const ar = s.getRange(row, col); // the current active cell

// Highlight properly based on type
let ranges = [];
if (type != 'column') ranges.push(r.getA1Notation());
if (type != 'row') ranges.push(c.getA1Notation());

ranges.push(ar.getA1Notation());
ss.getRangeList(ranges).activate();
}

Let's walkthrough the code together:

  1. First we need to get the current selected cell ss.getActiveCell().
  2. Then from the current selected cell, we can get the column, row information. We also need to get the max row and column number because we need to highlight the whole row and column later.
  3. Next, we use the getRange(row, column, numRows?, numColumns?) function to set the appropriate highlight range - column, row and the current cell ar.
  4. Depending on the type, we will highlight the appropriate ranges with this line ss.getRangeList(ranges).activate();. For example, if it's row type (if (type != 'column')), then we won't highlight column.

Save your code and viola. Run it yourself and see it in action! 😃

Here are the demos:

Row highlight
Row highlight
Column highlight
Column highlight
Crosshair highlight
Crosshair highlight

Bonus tip: Highlight on select

Says, if you want the crosshair highlight happens automatically when you select a cell, you can add this function in the code.

function onSelectionChange(e) {
highlight('crosshair');
}

onSelectionChange(e) is basically a trigger provided by the Sheets.

Crosshair highlight on selection change
Crosshair highlight on selection change

Save your code and test it on your page. Take note that the performance is quite slow. It takes a few seconds to crosshair highlight after you select a cell.

Final code

Finally, the complete code:

/* Code.gs */

function createHighlightMenu() {
SpreadsheetApp
.getActiveSpreadsheet()
.addMenu('Highlight', [
{name: 'Highlight row', functionName: 'highlightRow'},
{name: 'Highlight column', functionName: 'highlightColumn'},
{name: 'Highlight crosshair', functionName: 'highlightCrosshair'}
]);
}

function highlight(type) {
const ss = SpreadsheetApp.getActive();
const s = ss.getActiveSheet();
const a = ss.getActiveCell();

const col = a.getColumn();
const row = a.getRow();
const maxColumns = s.getMaxColumns();
const maxRows = s.getMaxRows();

const c = s.getRange(1, col, maxRows, 1);
const r = s.getRange(row, 1, 1, maxColumns);
const ar = s.getRange(row, col);

let ranges = [];
if (type != 'column') ranges.push(r.getA1Notation());
if (type != 'row') ranges.push(c.getA1Notation());
ranges.push(ar.getA1Notation());
ss.getRangeList(ranges).activate();
}

function highlightRow() {
highlight('row');
}

function highlightColumn() {
highlight('column');
}

function highlightCrosshair() {
highlight('crosshair');
};

// Uncomment this function if you need
// function onSelectionChange(e) {
// highlight('crosshair');
// }

Have something to say? Leave me comments on Twitter 👇🏼

Follow my writing:

Hand-crafted with love by Jecelyn Yeen © Licenses