Google sheets jump to cell from Custom menu

March 29, 2023

I’m trying to create a script for Google Sheets to implement a menu with a contents and quickly navigate from the menu to the desired chapter.

Algorithm:

Creates a "Scripts" menu and a "Go to" submenu
Chapters from B column are added to the "Go to" submenu (all chapters start with [S], for example: [S] File.txt)
When you click on any chapter from the contents, you should jump to the cell to the left of the chapter (if the chapter is in cell B3232, then jump to cell A3232)

The first two points have been implemented, but the third is a hard one for me.

Here’s the code (sorry for the cursed code)

function onOpen() {
  var ui = SpreadsheetApp.getUi(); // Get: UI object
  var menu = ui.createMenu('Scripts'); // Create: Scripts menu
  var submenu = ui.createMenu('Go to'); // Create: Go to submenu
  var sheet = SpreadsheetApp.getActiveSheet(); // Get: Active sheet
  var range = sheet.getRange("B:B"); // Get: B column range
  var values = range.getValues(); // Get: Cell values as a two-dimensional array
  for (var i = 0; i < values.length; i++) { // Going through all rows in an array
    var value = values[i][0]; // Get: cell value in the first column of the array
    if (value.startsWith("[S]")) { // Checking if value starts with [S]
      var cell = sheet.getRange(i+1, 1); // Get: The cell to the left of the found cell
      submenu.addItem(value, "goToCell" + i); // Add: A cell to the submenu and bind it to the goToCell function + line number
      eval("function goToCell" + i + "() { cell.activate(); }"); // Create a function that activates the cell to the left of the found cell
    }
  }
  menu.addSubMenu(submenu); // Add: A Submenu to a Scripts Menu
  menu.addToUi(); // Add: A Scripts menu to the interface
}

As I already wrote, the first and second parts work fine.
But when it comes to the "Jump to" part – I get an error:
Script function not found: goToCell"CellNumber"
(like Script function not found: goToCell797)

The cell number itself is correct, but the script uses this number somehow wrong and cannot jump.

EG:

So, I need to somehow fix the last pieces of code, but I don’t know how.

Thanks for any help anyone can give.

>Solution :

Modification points:

In your script, I’m worried that when onOpen() is run, eval("function goToCell" + i + "() { cell.activate(); }"); might not be installed the function. In this case, I thought that eval("function goToCell" + i + "() { cell.activate(); }"); is modified to this[name] = function (cell) { return function () { cell.activate(); } }(cell);.
And also, in this case, when you run the function from the custom menu, the function is not existing. Because the function goToCell" + i is not existing. By this, such an error occurs.

From this situation, as a simple modification, how about the following modification?

Modified script:

onOpen(); // In this case, this function is run when the function is run with the custome menu.
function onOpen(e) {
  if (e) return;
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Scripts');
  var submenu = ui.createMenu('Go to');
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("B1:B" + sheet.getLastRow());
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    var value = values[i][0];
    if (value.startsWith("[S]")) {
      var name = "goToCell" + i;
      var cell = sheet.getRange(i + 1, 1);
      submenu.addItem(value, name);
      this[name] = function (cell) { return function () { cell.activate(); } }(cell);
    }
  }
  menu.addSubMenu(submenu);
  menu.addToUi();
}