CODEWITHSUNDEEP
google-sheetsgoto
NGC Swindle
NGC Swindle

Reputation: 1

Jump to a specific cell based on Active Cell

I'm completely new to Java Scipt although over the years I've messed around with VBA & Macros in Excel. I am now using Google Sheets almost exclusively, hence needing to learn Java.

I'm trying to jump to a specific cell (E5) if the current cell is (C14). This is what I've put together so far using scripts 'borrrowed' from others.

ie On entry of data in Cell C13 and pressing Enter, focus goes to Cell C14. The next data is to go into Cell E5.

function onSelectionChange(e) {
  var sheetNames = ["Score Input"]; // Set the sheet name.
  var ranges = ["C14"]; // Set the range to run the script.

  var range = e.range;
  var sheet = range.getSheet();
  var check = ranges.some(r => {
    var rng = sheet.getRange(r);
    var rowStart = rng.getRow();
    var rowEnd = rowStart + rng.getNumRows();
    var colStart = rng.getColumn();
    var colEnd = colStart + rng.getNumColumns();
    return (range.rowStart >= rowStart && range.rowEnd < rowEnd && range.columnStart >= colStart && range.columnEnd < colEnd);
  });
  if (check) {
    jumpToDetails();
  }
};

  function jumpToDetails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Score Input");
  var goToRange = sheet.getRange('c14').getValue();
  //sheet.getRange(goToRange).activate();
 SpreadsheetApp.getActive().getRange('E5').activate();
}

It worked, or did until I inserted a row in the sheet, and even though I have changed the associated cell addresses, it now doesn't work?

Two questions. 'Why has it stopped working'? and 'Is there a simpler way to do it'?

Upvotes: 0

Views: 148

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

I prefer using onEdit(e) on range C13 and jump to E5

For instance here is a script that jump from one cell to the following in addresses'list

function onEdit(event){ 
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  if (sh.getName() == 'Score Input'){                          // adapt
    var addresses = ["C13","E5","E10","H10","E13","H13","E16"]; // adapt
    var values = addresses.join().split(",");
    var item = values.indexOf(rng.getA1Notation());
    if (item < addresses.length - 1){ 
      sh.setActiveSelection(addresses[item + 1]); // except last one
    }
  }
}

if you want to be able to add rows and columns, play with named ranges (for instance ranges names 'first', 'second, 'third')

function onEdit(event){ 
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  if (sh.getName() == 'Score Input'){ 
    var addresses = ["first","second","third"]; 
    var values = addresses
      .map(ad => myGetRangeByName(ad))
      .join().split(",");
    var item = values.indexOf(rng.getA1Notation());
    if (item < addresses.length - 1){ 
      sh.setActiveSelection(addresses[item + 1]); // except last one
    }
  }
}
function myGetRangeByName(n) {
  return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n).getA1Notation();
}

reference

class NamedRange

Upvotes: 1

Related Questions