Assign cell hyperlink to button via script

Question

We're talking about the same spreadsheet here, no cross-links. I just want a button to jump to another part of the same spreadsheet. None of the scripts I found online do this, they all open the link in a new window where you have to sign in into Google again and give an untitled project (presumably the script) permission to edit spreadsheets. Using =Hyperlink doesn't look very nice because you have to hover over the link and then click the little window that appears.

I hope a script like that exists, or if anyone knows how to solve this with macros or add-ons I'm all ears.

Answer 1

This function jumps to the last column in the same row. Run the showDialog() function to display the sidebar and the button is on the top of the sidebar.

function jump() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveRange();
  var col=rg.getColumn();
  var max=sh.getMaxColumns();
  var offset=max-col;
  rg.offset(0, offset).activate();
}

function showDialog() {
  var html='<input type="button" value="Jump" onClick="jump();" />';
  html+='<script>function jump(){google.script.run.jump();}</script>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

Answer 2

Here is a simple script that will create a menu ("Actions") [1] on your spreadsheet UI with an option "Go to Cell" that when clicked it will run the activateCell function that will activate the cell you choose as a parameter on the getRange function [2].

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .createMenu('Actions')
  .addItem('Go to Cell', 'activateCell')
  .addToUi();  
}

function activateCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); 
  var range = sheet.getRange("Z40").activate();  //Change the cell to whichever you want
}

[1] https://developers.google.com/apps-script/reference/base/ui

[2] https://developers.google.com/apps-script/reference/spreadsheet/range