如何在Google Spreadsheet工作簿中创建所有工作表的侧边栏列表? [英] How to create a sidebar list of all sheets in a Google Spreadsheet workbook?
问题描述
由于我的Google电子表格工作簿包含很多工作表,因此在电子表格工作簿左下角的下拉菜单中都看不到它们,因此我尝试在"
Because I have a Google spreadsheet workbook with so many sheets that they are not all visible in the pulldown on the bottom left side of the spreadsheet workbook, I am attempting to modify the code at "How to get the list of all sheet in a dropdown list in a sidebar" from a standalone script to a bound script in order to be able to see a longer list. Also, I do not want my list in a dropdown. I want it simply as a bulleted list.
我收到一个格式错误的HTML错误,我认为该错误是从片段中生成的. (请参见下面的代码.)任何想法,此代码出了什么问题.
I am getting a malformed HTML error that I assume is being generated from within the scriplet. (See code below.) Any ideas what is going wrong with this code.
<html>
<head>
<base target="_top">
</head>
<body>
<select name="Sheets List" onchange="listSheets()")>
<? var sheets=spreadsheet.getSheets(); ?>
<? for(var i=0;i<sheets.length;i++) { ?>
<option value=<?=sheets[i].getName()?>> <?= sheets[i].getName()?></option>
<? } ?>
</select>
<script>
function listSheets(){
var name=document.getElementsByName("Sheets List")[0].value;
google.script.run.goToSheet(name);
};
</script>
</body>
</html>
GS
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom')
.addItem('Sheets List', 'sheetsList')
.addToUi();
function sheetsList(){
var html = HtmlService.createHtmlOutputFromFile('sheets').evaluate
.setTitle('Sheets in this Workbook')
.setWidth(300);
SpreadsheetApp.getUi()
.showSidebar(html);
}
}
错误消息
消息详细信息 格式错误的HTML内容:>函数listSheets(){var name = document.getElementsByName("Sheets List")[0] .value; google.script.run.goToSheet(name); };
Error Message
Message details Malformed HTML content: > function listSheets(){ var name=document.getElementsByName("Sheets List")[0].value; google.script.run.goToSheet(name); }; .
推荐答案
侧边栏中的页面链接列表
function allsheetslist() {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
var html='<table>';
shts.forEach(function(sh,i){
html+=Utilities.formatString('<tr><td><input type="button" value="%s" onClick="gotoSheet(%s);" /></td></tr>',sh.getName(),sh.getIndex());
});
html+='</table>';
html+='<script>function gotoSheet(index){google.script.run.gotoSheetIndex(index);}</script>';
var userInterface=HtmlService.createHtmlOutput(html)
SpreadsheetApp.getUi().showSidebar(userInterface);
}
function gotoSheetIndex(index) {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
shts[index-1].activate();
}
您可以通过这种方式在侧栏上获得更多按钮:
You can get a lot more buttons on the side bar this way:
function allsheetslist() {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
var html='<style>input[type="button"]{margin:2px 5px 2px 0;border:none;background:white;}</style>';
shts.forEach(function(sh,i){
html+=Utilities.formatString('<input type="button" value="%s" onClick="gotoSheet(%s);" />',sh.getName(),sh.getIndex());
});
html+='<script>function gotoSheet(index){google.script.run.gotoSheetIndex(index);}</script>';
var userInterface=HtmlService.createHtmlOutput(html).setTitle("Sheet Link List")
SpreadsheetApp.getUi().showSidebar(userInterface);
}
这篇关于如何在Google Spreadsheet工作簿中创建所有工作表的侧边栏列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!