--------
/**
* When the spreadsheet is opened, activates the cell in
* column A of the last row that has data on every sheet.
*
* To take this script into use:
*
* - take a backup of your spreadsheet through File > Make a copy
* - select all the text in this script, starting at the first "/**"
* line above, and ending at the last "}"
* - copy the script to the clipboard with Control+C (on a Mac, ⌘C)
* - open the spreadsheet where you want to use the function
* - choose Extensions > Apps Script (this opens a new tab)
* - if you see just the 'function myFunction() {}' placeholder, press
* Control+A (on a Mac, ⌘A), followed by Control+V (⌘V) to paste
* the script in
* - otherwise, click Files+ > Script, enter 'Jump to last row' to name
* the file, and press Enter, Control+A (⌘A), Control+V (⌘V)
* - if you have an existing onOpen(e) function, add the following line
* as the first line after the initial '{' in that onOpen(e) function:
* jumpToLastRow();
* ...and then delete the onOpen(e) function below
* - press Control+S (⌘S) to save the script
* - when prompted, name the project 'Jump to last row in every sheet'
* - the script will run automatically when you open the spreadsheet
*/
/**
* Simple trigger that runs each time the user opens the spreadsheet.
*
* @param {Object} e The onOpen() event object.
*/
function onOpen(e) {
if (!e) {
throw new Error('Please do not run the script in the script editor window. It runs automatically when you open the spreadsheet.');
}
jumpToLastRow();
}
/**
* Activates the first cell in the last row on every sheet.
*/
function jumpToLastRow() {
// version 1.0, written by --Hyde 24 August 2021
// - @see https://support.google.com/docs/thread/122596329?msgid=122680143
// - @license https://hyde.mit-license.org/2021
const sheets = SpreadsheetApp.getActive().getSheets();
for (let s = sheets.length - 1; s >= 0; s--) {
sheets[s].getRange(getLastRow_(sheets[s]), 1).activate();
SpreadsheetApp.flush();
}
}
/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
// version 1.5, written by --Hyde, 4 April 2021
const values = (
columnNumber
? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
: sheet.getDataRange()
).getDisplayValues();
let row = values.length - 1;
while (row && !values[row].join('')) row--;
return row + 1;
}
