CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
JimmyBuffet_Express
JimmyBuffet_Express

Reputation: 311

create button to navigate to a given sheet name

I am trying to create a button to more easily navigate a google sheet with many tabs. The aim is to have many buttons on one sheet (one button per tab).

My following script works but i want to make it dynamic instead of hard coding a sheet name ('Sheet1' for example).

function gotoSheet() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Sheet1');

  sheet.activate();
}

This is what i currently have:

function gotoSheet(cellReference) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(ss.getRange(cellReference));

  sheet.activate();
}

The parameter cellReference ideally refer to a cell that contains the sheet name (eg: cell "A1" has the value "Sheet1")

Where am I going wrong?

Upvotes: 0

Views: 408

Answers (2)

muds
muds

Reputation: 526

Edit

Having understood your issue better, I am updating my answer. Consider an index sheet "Index", with its first column containing a list of sheet names.

The function setLinks() below can be run once to replace all values in that column with a link to their sheet (if it exists). The onEdit() function will then update all future additions to that column with the link to the referenced sheet.

function onEdit(e) {

  const indexSheetName = "Worksheet"; // @TODO: Set index sheet name 
  const sheetNamesCol = 1; // @TODO: Set column number of the sheet names 
  
  const ss = e.source, 
        sheet = e.range.getSheet(),
        row = e.range.getRow(),
        col = e.range.getColumn();
  
  if(sheet.getName() == indexSheetName && col == sheetNamesCol) { // If the edit took place in the index sheet
    
    // Fetching the sheet
    let linkedSheetName = e.value; 
    let linkedSheet = ss.getSheetByName(linkedSheetName); 
    
    // If the sheet exists, set formula
    if(!!linkedSheet) {
      e.range.setValue(`=hyperlink("#gid=${linkedSheet.getSheetId()}", "${linkedSheetName}")`);
    }  
  }
}

function setLinks() {
  
  const indexSheetName = "Worksheet"; // @TODO: Set index sheet name 
  const sheetNamesCol = 1; // @TODO: Set column number of the sheet names 
  const startRow = 2;
  
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(indexSheetName);
  const range = sheet.getRange(startRow, sheetNamesCol, sheet.getLastRow()-startRow+1);
  const values = range.getValues();
  
  for(let i=0; i<values.length; i++) {
    let linkedSheetName = values[i][0]; 
    let linkedSheet = ss.getSheetByName(linkedSheetName); 
    
    // If the sheet exists, set formula
    if(!!linkedSheet) {
      values[i][0] = `=hyperlink("#gid=${linkedSheet.getSheetId()}", "${linkedSheetName}")`;
    } 
  } 
  
  range.setValues(values);
}

Original Post

Assuming you mean cellReference would be a Range (cell) which contains text "Sheet1" (or any sheet name), and you want to activate that sheet.

function gotoSheet(cellReference) {
  var ss = SpreadsheetApp.getActive();
  var sheetName = cellReference.getValue();
  var sheet = ss.getSheetByName(sheetName);

  sheet.activate();
}

Or simpler,

function goToSheet (cellReference) {
  SpreadsheetApp.getActive().getSheetByName(cellReference.getValue()).activate();
}

Upvotes: 0

roma
roma

Reputation: 1580

Probably you need

var sheet = ss.getSheetByName(ss.getRange(cellReference).getValue());

Upvotes: 1

Related Questions