如何在侧边栏的下拉列表中获取所有工作表的列表 [英] How to get the list of all sheet in a dropdown list in a sidebar
问题描述
我正在Google工作表的侧边栏菜单中设置脚本中所需的一些变量.
I'm working in an sidebar menu in an google sheet, to set some variables that i need in my script.
我的脚本已经可以运行了.很简单，在表单提交时，它将在我的日历中创建一个事件.
My script is already working. It is easy, on form submit, it will create an event in my calendar.
但是，为了简化脚本的安装，我想在边栏中显示一个下拉列表，其中包含工作表中所有工作表的名称.
However, in order to simplify the installation of my script, I would like to show a dropdown list in my sidebar with the names of all the sheet of my worksheet.
我看到了各种各样的帖子，它们显示了如何执行像这样的下拉列表:
I see various post, that show how to do a dropdown list like this :
- 如何创建放置输入框中的下拉列表?
- 但这不是我的问题，我的问题是如何获取工作表列表并将其放入下拉列表中.
But this is not my problem, my problem is how to get the list of the sheet and put it in a dropdown list.
感谢您的帮助，
function onOpen() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{name: "Sync Calendar Now", functionName: "syncActive"}]; entries.push(null); // adding line separator entries.push({name: "Settings", functionName: "paramID"}); spreadsheet.addMenu(sheetName, entries); }; function paramID() { //TODO : Mettre ID calendrier ici :) var html = HtmlService.createHtmlOutputFromFile('SideBar') .setTitle('My custom sidebar') .setWidth(300); SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp. .showSidebar(html); }这是我如何获取工作表的列表名称
There is how i get the list names of the sheets
function getListNames(){ var out = new Array() var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); for (var i=0 ; i<sheets.length ; i++) out.push( sheets[i].getName() ) Logger.log(out); return out }例如，我的工作表中有:工作表1，工作表2，工作表3
For exemple, I have in my worksheet : Sheet 1 , Sheet 2 , Sheet 3
打开侧边栏时，我想显示一个带有以下内容的下拉列表:工作表1，工作表2和工作表3
When my sidebar open, i want to display a dropdown list with : Sheet 1, Sheet 2 and Sheet 3
接下来，我要返回选择的值.
Edit : Next I want to return the value selected.
推荐答案
您可以使用这允许您在
html代码中使用Apps脚本功能:This allows you to use Apps Script functions within the
htmlcode:<body> <select name="Available sheets" onchange="myJsFunction()")> <? var sheets=spreadsheet.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; google.script.run.goToSheet(name); }; </script> </body>通过
google.script.run ，您可以通过将选定的工作表添加到Apps脚本功能.
google.script.run allows you to pass the selected sheet to an Apps Script function.
然后，在.gs文件中，您需要根据所选工作表在Apps脚本函数中定义要执行的操作，例如:
In the .gs file you then need to define what you want to do within the Apps Script function based on the selected sheet, example:
function goToSheet(selectedSheetName){ Logger.log('The selected sheet is: '+ selectedSheetName); }此外，您需要更改行
var html = HtmlService.createHtmlOutputFromFile('SideBar')到
var html = HtmlService.createTemplateFromFile('SideBar').evaluate()允许脚本评估scriptlet的内容.
to allow the script to evaluate the content of the scriptlets.
这篇关于如何在侧边栏的下拉列表中获取所有工作表的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
