如何在Google表格中添加类似于Excel中的表单控件滚动条的水平滚动条? [英] How can I add a horizontal scrollbar in the google sheet similar to form control scrollbar in excel?
问题描述
我想添加一个水平滚动条，该滚动条将根据其位置提供0-365的值，并使用该值进行计算.
I would like to add a horizontal scrollbar that will provide the values 0-365 based on its position and use this value for calculation.
我正在创建甘特图，与页面 https相同://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html
I am creating a Gantt chart, the same as is on the page https://www.vertex42.com/ExcelTemplates/excel-gantt-chart.html
有一个水平滚动条，可帮助移动"日历.
There is a horizontal scrollbar that helps "to move" with the calendar.
我在此处输入了一些必须手动更改的数字.我尚未在互联网上找到任何解决方案.
I have put there a number that I must change manually. I haven't found any solution on the internet.
推荐答案
这种控件根本无法在Google表格中本地使用.我发现了 Google支持论坛的近期回复，表明情况仍然如此.
This kind of control is simply not available natively within Google Sheets. I found a recent response from the Google Support Forums to indicate this is still the case.
如果您想自己构建此功能，实际上现在可以通过充分利用 Google表格和 HTML服务脚本API .您可以使用HTML和JS创建一个对话框，该对话框可以包含所需的任何输入(包括范围滑块)，并且可以将值发送回Google Sheet脚本，然后可以将其保存到单元格中.我进行了准系统测试以确保它可以正常工作，并且确实如此:
If you want to build this functionality out yourself, it is actually now possible to do so, by taking full advantage of the Apps Script platform and their Google Sheets and HTML Service scripting APIs. You can create a dialog box with HTML and JS, which can have any inputs you want, including range sliders, and it can send the values back to the Google Sheet script, which can then save it to a cell. I put together a barebones test to make sure it would work, and it does:
我使用了Menu -> Tools -> Script Editor，然后创建了这两个文件:
I used Menu -> Tools -> Script Editor, and then created these two files:
Code.gs:
// Trigger dialog to be added to menu on file open
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Dialog')
.addItem('Open', 'openDialog')
.addToUi();
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index');
SpreadsheetApp.getUi()
.showModalDialog(html, 'Dialog title');
}
function saveSliderVal(updatedVal){
var sheetToSaveTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Gantt');
if (!sheetToSaveTo){
sheetToSaveTo = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
}
var cellToSaveTo = sheetToSaveTo.getRange('A1:A1');
cellToSaveTo.setValue(updatedVal);
}
index.html:
index.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<script>
function update() {
document.getElementById('val').innerText = document.getElementById('input').value;
}
function save() {
// Call sheet function
google.script.run.saveSliderVal(parseInt(document.getElementById('input').value, 10));
}
</script>
<input id="input" type="range" min="0" max="365" onchange="update()" oninput="update()" value="0"
step="1" /> <span id="val">0</span>
<br />
<button id="save" onclick="save()">Save to Gantt</button>
</body>
</html>
然后在保存脚本后重新加载工作表，您应该将Dialog -> Open视为新菜单项.单击它会显示您的自定义HTML.
Then reload the sheet after saving your script, and you should see Dialog -> Open as a new menu item. Clicking it will bring up your custom HTML.
这篇关于如何在Google表格中添加类似于Excel中的表单控件滚动条的水平滚动条?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
