目前，我正在 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);
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/406031.html
標籤：
上一篇：Apps腳本：回傳陣列項