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.

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:

• CTRL + SPACE highlights the column
• SHIFT + SPACE highlights the row

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:

• Highlight row
• Highlight column
• Highlight crosshair

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.

Follow my configuration below and save it:

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.

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:

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.

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');
// }