Move sheet when drop-down menu is selected-CodePudding

I want to implement a function that moves to the selected sheet when selecting the drop-down menu (sheet name) in Google spreadsheet, but it's not working well.

The menu shows all the seat names, but if you select that particular seat, nothing will happen.

I keep searching and searching, but I don't see a clear solution. What should I do? Please help me.

The code I wrote is as follows.

code.gs

function onOpen() {
  createMenu()
}

function createMenu(){
  const ui = SpreadsheetApp.getUi()
  const menu = ui.createMenu("Sidebar")
  menu.addItem("Open Sidebar", "openSidebar");
  menu.addToUi()
}

function openSidebar() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
  var html = HtmlService.createTemplateFromFile('sidebar')
  .evaluate()
  .setTitle('Salesforce')
  .setWidth(400);
  
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .showSidebar(html);
}

sidebar.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>

    <body>
    <button id = "moveButton" >Move</button>
   
    <select name="Available sheets" onchange="moveSheet()" style="width:280px;height:30px;">
      <? var sheets=ss.getSheets(); ?>
      <? for(var i=0;i<sheets.length;i  ) { ?>
        <option value=<?=sheets[i].getName()?>> <?= sheets[i].getName()?></option>
      <? } ?>
    </select>
  
  <script>
    function myJsFunction(){
      var name = document.getElementsByName("Available sheets")[0].value;
    }
    
    function moveSheet(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(name);
      
      if(sheet !=null){
        ss.moveActiveSheet(sheet);
        
      }else{
       //
      }
    } 
     
    document.getElementById("moveButton").addEventListener("click",moveSheet());
     //google.script.run.withSuccessHandler(name);
    };
  </script>
  </body>
</html>

CodePudding user response：

I believe your goal is as follows.

You want to move the sheet by selecting the sheet name from the dropdown list at the sidebar of Spreadsheet.

When I saw your showing script, I thought that you are trying to run the Google Apps Script at the sidebar. At the sidebar and dialog of Spreadsheet, the Javascript is run with the client browser. On the other hand, Google Apps Script is run on the internal server side of Google. I thought that this might be the reason for your issue.

When your showing script is modified for achieving your goal, how about the following modification?

Google Apps Script side: `code.gs`

In this modification, your function of openSidebar is modified and a new function of moveSheet is added.

Here, in order to move the active sheet, activate() is used.

function moveSheet(e) {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e).activate();
}

function openSidebar() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var options = ss.getSheets().map(s => {
    var sheetName = s.getSheetName();
    return `<option value="${sheetName}">${sheetName}</option>`;
  }).join("");
  var html = HtmlService.createTemplateFromFile('sidebar');
  html.options = options;
  var h = html.evaluate().setTitle('Salesforce').setWidth(400);
  SpreadsheetApp.getUi().showSidebar(h);
}

HTML side: `sidebar.html`

I modified select tag and removed moveSheet().

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <button id="moveButton" >Move</button>
  <select name="Available sheets" onchange="google.script.run.moveSheet(this.value)" style="width:280px;height:30px;">
    <?!= options ?>
  </select>

  <script>
    document.getElementById("moveButton").addEventListener("click",moveSheet());
     //google.script.run.withSuccessHandler(name);
    };
  </script>
</body>

</html>

When you run openSidebar, a sidebar is opened. And, you can see the dropdown list including the sheet names. When you change the dropdown list, the active sheet is changed to the sheet of the selected sheet name.

Google Apps Script side: code.gs

HTML side: sidebar.html

References:

Google Apps Script side: `code.gs`

HTML side: `sidebar.html`