目前，我正在 Google 表格上制作费用跟踪器。这是一个预览：
我目前有一个功能可以检查在特定品牌上花费了多少钱（以百分比计）。
像这样：
这是它的代码块：
function perCent(categ){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
var total = 0;
var sum = 0;
values.forEach(function(row){
total =row[1];
if (row[7]==categ){sum =row[1]}
})
return (sum/total)*100 "%" " of your income has been spent on " categ;
}
但是，要使此功能起作用，用户必须手动输入一个单元格=perCent(categ)，这对用户不友好。
例如：
为了增加用户友好性，我引入了一个 HTML 侧边栏，如下所示：
这是侧边栏的代码块：
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Main Menu')
.addItem('My sidebar 1', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('Sidebar')
.setTitle('Clothing Main Menu');
SpreadsheetApp.getUi()
.showSidebar(html);
}
这是 HTML 代码块：
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1 class = "align ">Welcome to the clothing expense custom menu bar!</h1>
<h2 class = "align">Here are the custom functions you can use:</h2>
<p class = "align"> Per Cent Function</p>
<form>
<input class = "margin" type = "text" placeholder="Enter brand name">
<div>
<button type = "submit" class="align">Submit
</button>
</div>
</form>
</body>
<style>
body{
background-color: black;
color: red;
}
.align{
text-align: center;
}
.margin{
margin-bottom: 10px;
}
</html>
有什么方法可以perCent通过单击Submit按钮使功能运行？
谢谢！
uj5u.com热心网友回复：
您需要google.script.run在 html 的脚本部分中添加。这将在您的Code.gs 中运行该函式。要提示perCent()函式计算的值，可以使用UI.alert().
例子：
边栏.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<script>
function runFunc(){
var brand = document.getElementById("brand").value;
google.script.run.perCent(brand);
}
</script>
<body>
<h1 class = "align ">Welcome to the clothing expense custom menu bar!</h1>
<h2 class = "align">Here are the custom functions you can use:</h2>
<p class = "align"> Per Cent Function</p>
<form onsubmit="runFunc()">
<input class = "margin" id="brand" type = "text" placeholder="Enter brand name">
<div>
<button type="submit" >Submit</button>
</div>
</form>
</body>
<style>
body{
background-color: black;
color: red;
}
.align{
text-align: center;
}
.margin{
margin-bottom: 10px;
}
</html>
代码.gs
function perCent(categ){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
var total = 0;
var sum = 0;
values.forEach(function(row){
total =row[1];
if (row[7]==categ){sum =row[1]}
})
var val = (sum/total)*100 "%" " of your income has been spent on " categ;
var ui = SpreadsheetApp.getUi();
ui.alert(val)
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Main Menu')
.addItem('My sidebar 1', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('Sidebar')
.setTitle('Clothing Main Menu');
SpreadsheetApp.getUi()
.showSidebar(html);
}
输出：
参考
- 类 google.script.run
- ui.alert（提示）
uj5u.com热心网友回复：
我能够让它作为一个 UI 弹出视窗作业。我确信它可以作为侧边栏，但这是完成您想要做的事情的快速方法：
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.createMenu('Clothing Main Menu')
.addItem('Calculate % spend per brand', 'showPrompt')
.addToUi();
}
function showPrompt() {
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.prompt(
'Check percentage of clothing budget',
'Please enter a clothing brand:',
ui.ButtonSet.OK_CANCEL);
// Process the user's response.
var button = result.getSelectedButton();
var text = result.getResponseText();
if (button == ui.Button.OK) {
// User clicked "OK".
ui.alert('Results: ' perCent(text) '.');
} else if (button == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('Please input a clothing brand');
} else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}
}当您插入此应用程序脚本片段时，将添加一个名为“主选单”的选单，唯一的选项将称为“计算每个品牌的支出百分比”。如果用户点击，他们将被提示输入品牌名称。当他们这样做然后点击 OK 时，将向他们显示 perCent 函式结果。
编辑：您可以将其用作侧边栏，但据我所知，我无法在 html 侧边栏中获取表单栏位值以通过应用程序脚本函式进行处理。这是尽可能接近的，它弹出了与上次相同的输入框，但这次结果显示在侧边栏中。显然可以对其进行样式化和扩展，但这与您希望的非常接近：
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.createMenu('Clothing Main Menu')
.addItem('Calculate % spend per brand', 'showPrompt')
.addToUi();
}
function showPrompt() {
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.prompt(
'Check percentage of clothing budget',
'Please enter a clothing brand:',
ui.ButtonSet.OK_CANCEL);
// Process the user's response.
var button = result.getSelectedButton();
var text = result.getResponseText();
if (button == ui.Button.OK) {
// User clicked "OK".
var html = HtmlService.createHtmlOutput("<h1> Results: " perCent(text) "</h1>");
SpreadsheetApp.getUi().showSidebar(html);
} else if (button == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('Please input a clothing brand');
} else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}
}
编辑2：
这是一个完全在 HTML 侧边栏中作业的版本：
function perCent(categ){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var values = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
var total = 0;
var sum = 0;
values.forEach(function(row){
total =row[1];
if (row[7]==categ){sum =row[1]}
})
var rtrnStr = (sum/total)*100 "%" " of your income has been spent on " categ;
var html2 = HtmlService.createHtmlOutput("<h1> Results: " rtrnStr "</h1>");
SpreadsheetApp.getUi().showSidebar(html2);
}
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.createMenu('Clothing Main Menu')
.addItem('My sidebar 1', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createHtmlOutput('<script> function processSub(){ var result = document.getElementById("brand").value; google.script.run.perCent(result);}</script> <form id="myForm" onsubmit="event.preventDefault(); processSub();"> <input type="text" label="Clothing Brand" id="brand"><br><input type="submit"></form> ')
.setTitle('Clothing Main Menu');
SpreadsheetApp.getUi()
.showSidebar(html);
}
0 评论