Focusing Selected Cell to Top Left on Google Spreadsheet using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

This is a sample script for moving the selected cell to the top left on Google Spreadsheet to focus it using Google Apps Script.

One day, there might be a case where you are required to focus the specific cell on Google Spreadsheet to help edit cells. In this post, I would like to introduce a sample script for achieving this.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.

function myFunction() {
  const ui = SpreadsheetApp.getUi();
  const r = ui.prompt("Input range").getResponseText();
  if (!r) return;
  const offsetRow = 0; // If the frozen rows are set, please set the number of frozen rows.
  const offsetCol = 0; // If the frozen columns are set, please set the number of frozen columns.

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange(r).activate();
  const row = range.getRow();
  const col = range.getColumn();
  if (row > 1 + offsetRow) {
    const p = [1 + offsetRow, row - 1 - offsetRow];
    sheet.hideRows(...p);
    SpreadsheetApp.flush();
    sheet.showRows(...p);
  }
  if (col > 1 + offsetCol) {
    const p = [1 + offsetCol, col - 1 - offsetCol];
    sheet.hideColumns(...p);
    SpreadsheetApp.flush();
    sheet.showColumns(...p);
  }
}

When this script is run, a dialog is opened on Google Spreadsheet. When you input a cell coordinate, the inputted cell is shown in the top left. You can see the demonstration in the above animation gif.

Note

  • The key factor for achieving this is as follows. This flow leads to the goal.

    • sheet.hideRows(...p), SpreadsheetApp.flush, sheet.showRows(...p)
    • sheet.hideColumns(...p);, SpreadsheetApp.flush();, sheet.showColumns(...p);
    • By the way, it seems that sheet.getLastRow() and sheet.getLastColumn() can be used instead of SpreadsheetApp.flush.

  • In this sample script, there are offsetRow and offsetCol. For example, when your Spreadsheet has 1 frozen row and 2 frozen columns, please set to const offsetRow = 1; and const offsetCol = 2;. By this, the script is run by considering those frozen rows and columns.

Reference

 Share!