I’m trying to jump from cell A10 to cell E600 for instance:

• I have attempted a rectangular drawing assigned to a script.

• It works occasionally, but mostly fails.

Is there an easier way to implement this without hardcoding the values?

function jumpToCell(fromcell, tocell) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var jumps = {
    A10: "E600",
    A11: "E550",
    A12: "E500",
    A13: "E450",
    A14: "E400",
    A15: "E350",
    A16: "E300",
    A17: "E250",
    A18: "E200",
    A19: "E150",
    A20: "E100",
    A21: "E50",
    E600: "A10",
    E550: "A11",
    E500: "A12",
    E450: "A13",
    E400: "A14",
    E350: "A15",
    E300: "A16",
    E250: "A17",
    E200: "A18",
    E150: "A19",
    E100: "A20",
    E50: "A21",
  };

  Logger.log("fromcell: " + fromcell + " tocell: " + tocell);

  if (jumps[fromcell] && jumps[fromcell] === tocell) {
    Logger.log("Jumping to: " + tocell);
    sheet.getRange(tocell).activate();
  } else if (jumps[fromcell]) {
    Logger.log("Jumping to mapped destination: " + jumps[fromcell]);
    sheet.getRange(jumps[fromcell]).activate();
  } else {
    Logger.log("No jump found for: " + fromcell);
  }
}

function jumpFromA10ToE600() {
  Logger.log("Jump from A10 to E600 triggered"); 
  jumpToCell("A10", "E600");
}

function jumpFromE600ToA10() {
  Logger.log("Jump from E600 to A10 triggered"); 
  jumpToCell("E600", "A10");
}





function jumpFromA10ToE600() {
  jumpToCell("A10", "E600");
}

function jumpFromA11ToE550() {
  jumpToCell("A11", "E550");
}

function jumpFromA12ToE500() {
  jumpToCell("A12", "E500");
}

function jumpFromA13ToE450() {
  jumpToCell("A13", "E450");
}

function jumpFromA14ToE400() {
  jumpToCell("A14", "E400");
}

function jumpFromA15ToE350() {
  jumpToCell("A15", "E350");
}

function jumpFromA16ToE300() {
  jumpToCell("A16", "E300");
}

function jumpFromA17ToE250() {
  jumpToCell("A17", "E250");
}

function jumpFromA18ToE200() {
  jumpToCell("A18", "E200");
}

function jumpFromA19ToE150() {
  jumpToCell("A19", "E150");
}

function jumpFromA20ToE100() {
  jumpToCell("A20", "E100");
}

function jumpFromA21ToE50() {
  jumpToCell("A21", "E50");
}


function jumpFromE600ToA10() {
  jumpToCell("E600", "A10");
}

function jumpFromE550ToA11() {
  jumpToCell("E550", "A11");
}

function jumpFromE500ToA12() {
  jumpToCell("E500", "A12");
}

function jumpFromE450ToA13() {
  jumpToCell("E450", "A13");
}

function jumpFromE400ToA14() {
  jumpToCell("E400", "A14");
}

function jumpFromE350ToA15() {
  jumpToCell("E350", "A15");
}

function jumpFromE300ToA16() {
  jumpToCell("E300", "A16");
}

function jumpFromE250ToA17() {
  jumpToCell("E250", "A17");
}

function jumpFromE200ToA18() {
  jumpToCell("E200", "A18");
}

function jumpFromE150ToA19() {
  jumpToCell("E150", "A19");
}

function jumpFromE100ToA20() {
  jumpToCell("E100", "A20");
}

function jumpFromE50ToA21() {
  jumpToCell("E50", "A21");
}

Also tried getUI() as commented:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Weeks")
    .addItem("Week 1", "jumpToE50")
    .addItem("Week 2", "jumpToE100")
    .addItem("Week 3", "jumpToE150")
    .addItem("Week 4", "jumpToE200")
    .addItem("Week 5", "jumpToE250")
    .addItem("Week 6", "jumpToE300")

    .addItem("Week 7", "jumpToE350")
    .addItem("Week 8", "jumpToE400")
    .addItem("Week 9", "jumpToE450")
    .addItem("Week 10", "jumpToE500")
    .addItem("Week 11", "jumpToE550")
    .addItem("Week 12", "jumpToE600")
    .addToUi();
}

function jumpToCell(target) {
  if (!target || target.trim() === "") {
    Logger.log("Error: Target cell is null or empty.");
    return;
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  try {
    Logger.log("Jumping to cell: " + target);
    sheet.getRange(target).activate();
  } catch (e) {
    Logger.log("Invalid cell reference: " + target);
  }
}

function jumpToE600() {
  jumpToCell("E600");
}
function jumpToE550() {
  jumpToCell("E550");
}
function jumpToE500() {
  jumpToCell("E500");
}
function jumpToE450() {
  jumpToCell("E450");
}
function jumpToE400() {
  jumpToCell("E400");
}
function jumpToE350() {
  jumpToCell("E350");
}
function jumpToE300() {
  jumpToCell("E300");
}
function jumpToE250() {
  jumpToCell("E250");
}
function jumpToE200() {
  jumpToCell("E200");
}
function jumpToE150() {
  jumpToCell("E150");
}
function jumpToE100() {
  jumpToCell("E100");
}
function jumpToE50() {
  jumpToCell("E50");
}

This works; still too slow. Should be an easier way to do this.

I’m trying to jump from cell A10 to cell E600 for instance:

• I have attempted a rectangular drawing assigned to a script.

• It works occasionally, but mostly fails.

Is there an easier way to implement this without hardcoding the values?

function jumpToCell(fromcell, tocell) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var jumps = {
    A10: "E600",
    A11: "E550",
    A12: "E500",
    A13: "E450",
    A14: "E400",
    A15: "E350",
    A16: "E300",
    A17: "E250",
    A18: "E200",
    A19: "E150",
    A20: "E100",
    A21: "E50",
    E600: "A10",
    E550: "A11",
    E500: "A12",
    E450: "A13",
    E400: "A14",
    E350: "A15",
    E300: "A16",
    E250: "A17",
    E200: "A18",
    E150: "A19",
    E100: "A20",
    E50: "A21",
  };

  Logger.log("fromcell: " + fromcell + " tocell: " + tocell);

  if (jumps[fromcell] && jumps[fromcell] === tocell) {
    Logger.log("Jumping to: " + tocell);
    sheet.getRange(tocell).activate();
  } else if (jumps[fromcell]) {
    Logger.log("Jumping to mapped destination: " + jumps[fromcell]);
    sheet.getRange(jumps[fromcell]).activate();
  } else {
    Logger.log("No jump found for: " + fromcell);
  }
}

function jumpFromA10ToE600() {
  Logger.log("Jump from A10 to E600 triggered"); 
  jumpToCell("A10", "E600");
}

function jumpFromE600ToA10() {
  Logger.log("Jump from E600 to A10 triggered"); 
  jumpToCell("E600", "A10");
}





function jumpFromA10ToE600() {
  jumpToCell("A10", "E600");
}

function jumpFromA11ToE550() {
  jumpToCell("A11", "E550");
}

function jumpFromA12ToE500() {
  jumpToCell("A12", "E500");
}

function jumpFromA13ToE450() {
  jumpToCell("A13", "E450");
}

function jumpFromA14ToE400() {
  jumpToCell("A14", "E400");
}

function jumpFromA15ToE350() {
  jumpToCell("A15", "E350");
}

function jumpFromA16ToE300() {
  jumpToCell("A16", "E300");
}

function jumpFromA17ToE250() {
  jumpToCell("A17", "E250");
}

function jumpFromA18ToE200() {
  jumpToCell("A18", "E200");
}

function jumpFromA19ToE150() {
  jumpToCell("A19", "E150");
}

function jumpFromA20ToE100() {
  jumpToCell("A20", "E100");
}

function jumpFromA21ToE50() {
  jumpToCell("A21", "E50");
}


function jumpFromE600ToA10() {
  jumpToCell("E600", "A10");
}

function jumpFromE550ToA11() {
  jumpToCell("E550", "A11");
}

function jumpFromE500ToA12() {
  jumpToCell("E500", "A12");
}

function jumpFromE450ToA13() {
  jumpToCell("E450", "A13");
}

function jumpFromE400ToA14() {
  jumpToCell("E400", "A14");
}

function jumpFromE350ToA15() {
  jumpToCell("E350", "A15");
}

function jumpFromE300ToA16() {
  jumpToCell("E300", "A16");
}

function jumpFromE250ToA17() {
  jumpToCell("E250", "A17");
}

function jumpFromE200ToA18() {
  jumpToCell("E200", "A18");
}

function jumpFromE150ToA19() {
  jumpToCell("E150", "A19");
}

function jumpFromE100ToA20() {
  jumpToCell("E100", "A20");
}

function jumpFromE50ToA21() {
  jumpToCell("E50", "A21");
}

Also tried getUI() as commented:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Weeks")
    .addItem("Week 1", "jumpToE50")
    .addItem("Week 2", "jumpToE100")
    .addItem("Week 3", "jumpToE150")
    .addItem("Week 4", "jumpToE200")
    .addItem("Week 5", "jumpToE250")
    .addItem("Week 6", "jumpToE300")

    .addItem("Week 7", "jumpToE350")
    .addItem("Week 8", "jumpToE400")
    .addItem("Week 9", "jumpToE450")
    .addItem("Week 10", "jumpToE500")
    .addItem("Week 11", "jumpToE550")
    .addItem("Week 12", "jumpToE600")
    .addToUi();
}

function jumpToCell(target) {
  if (!target || target.trim() === "") {
    Logger.log("Error: Target cell is null or empty.");
    return;
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  try {
    Logger.log("Jumping to cell: " + target);
    sheet.getRange(target).activate();
  } catch (e) {
    Logger.log("Invalid cell reference: " + target);
  }
}

function jumpToE600() {
  jumpToCell("E600");
}
function jumpToE550() {
  jumpToCell("E550");
}
function jumpToE500() {
  jumpToCell("E500");
}
function jumpToE450() {
  jumpToCell("E450");
}
function jumpToE400() {
  jumpToCell("E400");
}
function jumpToE350() {
  jumpToCell("E350");
}
function jumpToE300() {
  jumpToCell("E300");
}
function jumpToE250() {
  jumpToCell("E250");
}
function jumpToE200() {
  jumpToCell("E200");
}
function jumpToE150() {
  jumpToCell("E150");
}
function jumpToE100() {
  jumpToCell("E100");
}
function jumpToE50() {
  jumpToCell("E50");
}

This works; still too slow. Should be an easier way to do this.

• google-sheets
• google-apps-script
• hyperlink

• 1 Why does the source cell matter? – Cooper Commented yesterday
• 1 In Google Sheets, a cell reference has a letter and a number, i.e., A1. It's unclear what you mean by "jump" and why it's required to have two parameters. Google Apps Script functions might ge assigned to drawings. It's not possible to assing a drawing to a script. – Wicket Commented yesterday
• 1 I think it would be easier by using .getUI(), and the way it works you just need to Input the parameter. – Lime Husky Commented yesterday
• 1 Please edit the question to add all the relevant clarifications. – Wicket Commented yesterday
• 1 @LimeHusky Tried getUI(); thanks for the comment! – aaa Commented yesterday

4 Answers 4

Using .getUI() to create Menu

I'll post my Comment as an Answer.

@LimeHusky Tried getUI(); thanks for the comment!

Class UI helps users create a custom menu bar on the editors user interface enabling them to add personalized functions.

Sample Code:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('foo')
    .addItem('bar', 'foobar')
    .addToUi();
}

This might help to return to the cell A10 to A21 using for of loop.

function returnToLocation() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const location = sheet.getCurrentCell().getA1Notation().replace(/\D/g, '');

  const ranges = [
    {currentLocation:600,targetLocation:"A10"},
    {currentLocation:550,targetLocation:"A11"},
    {currentLocation:500,targetLocation:"A12"},
    {currentLocation:450,targetLocation:"A13"},
    {currentLocation:400,targetLocation:"A14"},
    {currentLocation:350,targetLocation:"A15"},
    {currentLocation:300,targetLocation:"A16"},
    {currentLocation:250,targetLocation:"A17"},
    {currentLocation:200,targetLocation:"A18"},
    {currentLocation:150,targetLocation:"A19"},
    {currentLocation:100,targetLocation:"A20"},
    {currentLocation:50,targetLocation:"A21"}
  ];

  for (const {currentLocation, targetLocation} of ranges) {
    if (location >= currentLocation) {
      sheet.getRange(targetLocation).activate();
      break; // Breaking the loop if matches
    }
  }

}

References:

Class UI

You can also use a Named Function to generate a hyperlink to the other cell.

JUMP_TO(id, range, label)=
  LET(range, IF(ISREF(range), range,
             IF(ISREF(INDIRECT(range)), INDIRECT(range), 
             IF(ISREF(INDIRECT(range&"!A1")),INDIRECT(range&"!A1"),))),
      rangeA1N, ADDRESS(ROW(range),COLUMN(range),4),
      HYPERLINK("#gid="&id&"&range="&rangeA1n, label))

id: The id parameter is the target sheet's SheetID or gid number, shown in the url. It's best to have those numbers available, but if you don't, then this script can be used to get it.

function GETSHEETID(rng='') {
  const rng = SpreadsheetApp.getActive().getRangeByName(rng) || SpreadsheetApp.getActive().getRange(rng+'!A1')
  const sheet = rng ? rng.getSheet() : SpreadsheetApp.getActiveSheet()
  return sheet.getSheetId()
}

range: Can be any of the following:

• Direct reference to any range
• A1Notation to range on current sheet
• Full A1Notation to any range
• Sheet name (link will go to A1 on the target sheet)
• Named range

label: The label to display for the hyperlink.

These are all demonstrated in this spreadsheet: JUMP_TO

In Google Sheets, assigning a Google Apps Script function to an image or drawing is possible—ref Clickable images and drawings in Google Sheets. Unfortunately, clicking on an image/drawing with an assigned script doesn't pass an event object, as with simple and installable triggers.

If you don't want to have a function for each image/drawing, one option is to use checkboxes and on edit triggers instead of images/drawings with assigned functions.

"jump from cell A10 to cell E600"
"without hardcoding the values"

One easy way to quickly move from one place in a spreadsheet to another is to use named ranges. They are not "hardcoded" in the sense that they continue to point to the same target even if you add or delete rows.

Once you've created a named range at each target location, you can use range names in a script the same way as "A1" type range references:

function jumpToCell(target = 'NamedRange1') {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(target).activate();
}

Alternatively, use an onEdit(e) simple trigger that automatically activates the "next" cell when you're finished editing the previous one. See my jumpFromCellToCell_() script for sample code.

In the event you need the jump order to stay flexible, you can jump directly to any target by clicking a range name in the Data > Named ranges sidebar, or use the Name box in the top left corner. Press Control+J, type the first few letters of a target name, and press Enter to jump there.