Skip to content

Instantly share code, notes, and snippets.

@tamirko
Last active January 28, 2020 09:20
Show Gist options
  • Save tamirko/b577d92ea97e98ef2e3a6d8ee03143a2 to your computer and use it in GitHub Desktop.
Save tamirko/b577d92ea97e98ef2e3a6d8ee03143a2 to your computer and use it in GitHub Desktop.
How to go to a specific row or cell or to the 1st empty row in a Google sheet
function onOpen()
{
var menuEntries = [{name: "Go to Cell", functionName: "goToCell"}, {name: "Go to Row", functionName: "goToRow"},{name: "Go to 1st empty Row", functionName: "selectFirstEmptyRow"}];
SpreadsheetApp.getActiveSpreadsheet().addMenu("MyUtils", menuEntries);
}
function goToCell()
{
var strRange = Browser.inputBox("Insert the required cell (e.g.: B351):", Browser.Buttons.OK_CANCEL);
if(strRange != "cancel")
{
try
{
SpreadsheetApp.getActiveSheet().getRange(strRange).activate();
}
catch(e) {Browser.msgBox(e.message);}
}
}
function goToRow()
{
var strRange = Browser.inputBox("Insert the required row number (e.g.: 132):", Browser.Buttons.OK_CANCEL);
if(strRange != "cancel")
{
try
{
strRange = "A" + strRange
SpreadsheetApp.getActiveSheet().getRange(strRange).activate();
}
catch(e) {Browser.msgBox(e.message);}
}
}
function selectFirstEmptyRow() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.setActiveSelection(sheet.getRange("A"+getFirstEmptyRowWholeRow()))
}
function getFirstEmptyRowWholeRow() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var row = 0;
for (var row=0; row<values.length; row++) {
if (!values[row].join("")) break;
}
return (row+1);
}
@tamirko
Copy link
Author

tamirko commented Jan 28, 2020

Click here to learn how to go to the first empty row a Google sheet:

Click here to learn how to add a button which triggers a Google script.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment