Through this code

function onEdit(e) {
  if(e.source.getActiveSheet() && e.range.getRow() === 1 && e.range.getColumn() === 1) {
    e.source.getSheetByName(e.value).activate();
  }
}

function sheetnames() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i  ) out.push( [ sheets[i].getName() ] )
  return out 
}

I can switch tabs via the drop-down list. That is, if I select the name 'sheet 2' from the drop-down list, it redirects me to sheet 2

here is an example of how this code works:

https://docs.google.com/spreadsheets/d/1Cf8ipDqFMJ2cIdkabGdprJGOp3kTE1EfGkKrO8OBGLY/edit#gid=0

I wanted to know if it is possible to modify this code, so that when I select a certain option in the drop-down list, it takes me to another worksheet (instead of a tab)

Example

In this worksheet: https://docs.google.com/spreadsheets/d/1ewhIZ37f-dVPaKW8gG7pnuzQ1CVTFGgf5uoIUH96pkA/edit?usp=sharing

(first drop-down list only) If I select from the first drop-down list (Destino), for example, "Sucesso do Cliente"

I want it to redirect me to this:

https://docs.google.com/spreadsheets/d/1BbuJfPPOSdbvHZ5b8xVTMndeydNfslDhPLm9ftL1pLU/edit?usp=sharing

What would be the best way to do this?

CodePudding user response：

I believe your goal is as follows.

• When a dropdown list of the cell "D7" on the sheet "Formulário" is changed to Sucesso do Cliente, you want to open https://docs.google.com/spreadsheets/d/1BbuJfPPOSdbvHZ5b8xVTMndeydNfslDhPLm9ftL1pLU/edit?usp=sharing.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet of "In this worksheet:". And, please install OnEdit trigger to the function installedOnEdit. When you use this script, please change a dropdown list of the cell "D7" on the sheet "Formulário" to Sucesso do Cliente. By this, the script is run.

function installedOnEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Formulário" || range.getA1Notation() != "D7" || range.getValue() != "Sucesso do Cliente") return;
  const url = "https://docs.google.com/spreadsheets/d/1BbuJfPPOSdbvHZ5b8xVTMndeydNfslDhPLm9ftL1pLU/edit?usp=sharing";
  const html = `<script>window.open('${url}', '_blank');google.script.host.close();</script>`;
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(html), "sample");
}

• When you change a dropdown list of the cell "D7" on the sheet "Formulário" to Sucesso do Cliente, a dialog is opened, and the URL of https://docs.google.com/spreadsheets/d/1BbuJfPPOSdbvHZ5b8xVTMndeydNfslDhPLm9ftL1pLU/edit?usp=sharing is opened by Javascript of the dialog.

Note:

• In this sample script, the URL you expected is opened. But, the 1st Spreadsheet cannot be closed.

Reference:

• Dialogs and Sidebars in Google Workspace Documents

CodePudding user response：

Combining the onEdit functions

This function allow you to jump to other sheets in the same spreadsheet using the datavalidation of A1 and to different sheets of other spreadsheet using datavalidation of B1

function onMyEdit(e) {
  //e.source.toast("Entry");
  //Logger.log(JSON.stringify(e));
  const sheet = e.range.getSheet();
  if (e.range.columnStart == 2 && e.range.rowStart == 1 && e.value ) {
    const shts = {"Sheet1":"1553288907","Sheet2":"1273539231"};
    //e.source.toast('Flag1');
    const url = `https://docs.google.com/spreadsheets/d/11yNxdh/edit#gid=${shts[e.value]}?usp=sharing`;
    //Logger.log(url);
    const html = `<script>window.open('${url}', '_blank');google.script.host.close();</script>`;
    SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "sample");
  }
  if(e.range.rowStart == 1 && e.range.columnStart == 1 && e.value) {
    e.source.getSheetByName(e.value).activate();
  }
}

In my example I used data validation from a list Sheet1,Sheet2 and you could add id to the shts object and insert it into the url with something like ${shts[e.value][id]}