Google-sheets – How to Jump to Column Based off of Cell Value

google sheetsgoogle-apps-scriptjavascript

My expected result is very simple – I have used data validation to place a list of column titles in a cell. When a user selects a column title and clicks the button, I want them to be taken to the COLUMN where that word is the title.

For example, user selects "Game 5", which is in column 34. They click "JUMP" and are taken to Column 34 Row 1.

Likewise, user selects "Game 52", which is in column 76. User clicks "JUMP" button and is taken to Column 76 Row 1.

Here's what I have so far:

MATCH function set up in Cell AP1, searching for the dropdown value location within the top row. This basically returns the Column # that I want the jump button to move to.

Then, I have my script:

function jump2() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = ss.getSheetByName("ClassicGames"); // change to sheet containing dates
      var r = s.getRange("AP1").getValue(); //change A1 to cell containing =match formula
      s.setActiveSelection(s.getRange(r)); 
}

I thought maybe this combination would just take me to whatever number the MATCH function finds. For instance, if r = 35, the user will move to the 35th column.

Best Answer

You want a user to select a column letter from a dropdown list, and then have the spreadsheet "jump" to that column. There may be several ways to solve your question. Consider this answer as one approach.

There are several elements required to achieve your goal:

  • You need a droplist list
  • The content of the dropdown list must be every column letter between A and the Last Column.
  • a script that will respond when the dropdown list is selected
  • A way to convert a column number to a letter and vice versa.

To Do

1 - paste the following scripts into your project.
2 - create a sheet called "ColumnList". This will be the source for the dropdown options.
3 - Run the script buildlist. You can run this whenever you like; I chose not to include it in onOpen because it might not execute before the user has chosen from dropdown.
4 - On sheet = "ClassicGames, create a dropdown in Cell "A2". From the menu: Data > Data Validation > Criteria=List from a Range > Select a Data Range="ColumnList!A1:A1000", Save. By specifying a long range, and new columns that are added to the list will automatically be added to the dropdown options.

The onEdit script uses Event Objects to capture the range, value , and the sheet selected. An if-based logic statement makes sure that the script only runs if the edited cells is "A2" and the edited sheet = "ClassicGames".

Two utility scripts, columnToLetter and letterToColumn, are used to convert column numbers to letters and vice versa.

function buildlist() {
  
  // this script builds a list of column letters 
  // the list is used for the Data Validation range.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "ClassicGames";
  var sheet = ss.getSheetByName(sourcename);
  var listname = "ColumnList";
  var list = ss.getSheetByName(listname);
  
  // get the last column from Classic Games
  var lCol = sheet.getLastColumn();
  
  var listarray=[];
  
  for (i=0;i<lCol;i++){  
    listarray.push([columnToLetter(i+1)]);
  }
  // sort the array
  listarray.sort();
  
  // update the list of column letters
  list.getRange(1, 1, lCol).setValues(listarray)
  
}

function onEdit(e){

  // this script will detect selection of the dropdown
  // and jump to the appropriate column
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "ClassicGames";
  var sheet = ss.getSheetByName(sourcename)
  var listname = "ColumnList";
  var list = ss.getSheetByName(listname);
  
  // Logger.log("DEBUG: the range is "+e.range.getA1Notation());
  // Logger.log("DEBUG: the sheet is "+e.source.getSheetName())
  // Logger.log("DEBUG: the value is "+e.value)
  
  
  //test for the edit cell = A2 and the sheet = ClassicGames
  if (e.range.getA1Notation() == "A2" && e.source.getSheetName() == sourcename){
  
    // Logger.log("DEBUG: right sheet, right cell")
    // convert the column letter to the column Number
    var columnNumb = letterToColumn(e.value);
    // create a range for the column
    var jumpCell = sheet.getRange(1, columnNumb);
    // make the range the active cell- 
    // the spreadsheet will jump to that range
    sheet.setCurrentCell(jumpCell);  
    
  }else{
    
    // Logger.log("DEBUG: not the right sheet or cell")
  }
  
}



/**
 * columnToLetter and letterToColumn
 * by AdamL
 * https://stackoverflow.com/a/21231012/1330560
 */

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}
Related Topic