图纸提示框脚本 [英] Sheets Prompt boxes Script
问题描述
我想创建一个快速的演练，向Google工作表的特定列(即一组必填字段)输入文本
I want to create a prompt walk through of entering text into specific columns of google sheet (i.e. a set of Mandatory fields)
首先，我对编码没有经验，所以到目前为止，我的尝试是基于我的在线研究.我确信我已经通过重复脚本的元素来使脚本复杂化了.
Firstly I'm very inexperienced in coding, so my attempt so far has is based on my research online. I'm sure I have over complicated the script by repeating elements of the script.
我希望它能够执行以下操作
I want it to be able to do the following
1-单击自定义菜单. 2-单击添加新风险. 3-提示框询问4个问题序列-(例如，姓名，年龄，地址和职位). 4-捕获响应并将其放入工作表的下一个可用行. 5-提示框通过单击十字或取消按钮来取消整个过程.
1 - Click on custom menu. 2 - Click on Add new Risk. 3 - Prompt box asks 4 sequence of questions - (As an example Name, Age, Address & Job). 4 - The responses are captured and put into the next available row in the sheet. 5 - The prompt box cancels the whole process by clicking the cross or the cancel button.
需要克服的问题
1-到目前为止，我可以让脚本询问问题的顺序，但是它只会将最后一个问题的答复粘贴到下一个可用行.
1 - So far I can get the script to ask the sequence of questions however it only pastes the last question response the next available row.
2-我无法弄清楚如何取消该过程，它只是显示一条消息并进行命令的顺序
2 - I cant work out how to cancel the process, it just brings up a message and carries on the sequence of commands
3-我需要将响应固定在特定的列上，即-并非全部都粘贴到A2，B2，C2，D2中.例如姓名(A2)，年龄(G2)，地址(H2)，工作(X2)
3 - I need my responses to be fixed to specific columns i.e - Not all pasted into A2,B2,C2,D2. For example Name(A2), Age (G2), Address (H2) , Job (X2)
任何帮助将不胜感激
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.createMenu('Risk Menu')
.addItem('Add New Risk', 'showPrompt')
.addToUi();
}
function showPrompt() {
var ui = SpreadsheetApp.getUi(); // Same variations.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var targetSheet = ss.getSheetByName("Sheet1");
var range = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
var result = ui.prompt(
'Add New Risk - Madantory Field 1/4',
'Please enter your name:',
ui.ButtonSet.OK_CANCEL);
var button = result.getSelectedButton();
var text = result.getResponseText();
if (button == ui.Button.OK) {
// User clicked "OK".
range.setValue(text);
} else if (button == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('New risk cancelled');
} else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}
var result2 = ui.prompt(
'Add New Risk - Mandatory Field 2/4',
'Please enter your address:',
ui.ButtonSet.OK_CANCEL);
var button2 = result2.getSelectedButton();
var text2 = result2.getResponseText();
if (button2 == ui.Button.OK) {
// User clicked "OK".
range.setValue(text2);
} else if (button2 == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('New risk cancelled');
} else if (button2 == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}
var result3 = ui.prompt(
'Add New Risk - Mandatory Field 3/4',
'Please enter your age:',
ui.ButtonSet.OK_CANCEL);
var button3 = result3.getSelectedButton();
var text3 = result3.getResponseText();
if (button3 == ui.Button.OK) {
// User clicked "OK".
range.setValue(text3);
} else if (button3 == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('New risk cancelled');
} else if (button3 == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}
var result4 = ui.prompt(
'Add New Risk - Mandatory Field 4/4',
'Please enter your job role:',
ui.ButtonSet.OK_CANCEL);
var button4 = result4.getSelectedButton();
var text4 = result4.getResponseText();
if (button4 == ui.Button.OK) {
// User clicked "OK".
range.setValue(text4);
} else if (button4 == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('New risk cancelled.');
} else if (button4 == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}
}
推荐答案
带有文本输入和选择的自定义提示表单
由于包含了doGet()，因此可以将其用作对话框或Web应用程序.仅有几个次要的mod可以作为webapp运行.一个在script.html文件中标识为带有webapp和/或对话框的注释，另一个仅用于将取消"按钮的名称更改为取消/重新加载".就是这样.
A Custom Prompt Form with text input and a select
You can use this as a dialog or a webapp as the doGet() is included. Theres just a couple of minor mods to run as a webapp. One is identified in the script.html file with comments for webapp and/or dialog and the other is just to change the name of the Cancel button to Cancel/Reload. And that's it.
Code.gs:
function onOpen(){
SpreadsheetApp.getUi().createMenu('My Menu')
.addItem("Show Dialog", 'showMyDialog')
.addToUi()
}
function saveData(dObj) {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Sheet1');
var rg=sh.getRange(1,1,1,sh.getLastColumn());
var hA=rg.getValues()[0];
var vA=[];
for(var i=0;i<hA.length;i++) {
vA.push((dObj[hA[i]])?dObj[hA[i]]:'');//Column headers must agree with form names
}
sh.appendRow(vA);
return;
}
function showMyDialog(){
var ui=HtmlService.createTemplateFromFile('TheHtml').evaluate();
SpreadsheetApp.getUi().showModelessDialog(ui, 'Form Data');
}
function doGet(e){
return HtmlService.createTemplateFromFile('TheHtml').evaluate();
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getSelectOptions() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Options');
var rg=sh.getDataRange();
var vA=rg.getValues();
return vA;
}
TheHtml.html:
TheHtml.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= include('resources') ?>
<?!= include('css') ?>
</head>
<body>
<?!= include('form') ?>
<?!= include('script') ?>
</body>
</html>
script.html:
script.html:
<script>
$(function(){
google.script.run
.withSuccessHandler(function(vA) {
$('#sel1').css('background-color','#ffffff');
updateSelect(vA);
})
.getSelectOptions();
});
function updateSelect(vA,id){
var id=id || 'sel1';
var select = document.getElementById(id);
select.options.length = 0;
for(var i=0;i<vA.length;i++)
{
select.options[i] = new Option(vA[i][0],vA[i][1]);
}
}
function getInputObject(obj) {
var rObj={};
for(var i=0;i<Object.keys(obj).length;i++){
//console.log('Name: %s Type: %s',obj[i].name,obj[i].type);
if(obj[i].type=="text"){
rObj[obj[i].name]=obj[i].value;
}
if(obj[i].type=="select-one"){
rObj[obj[i].name]=obj[i].options[obj[i].selectedIndex].value;
}
}
return rObj;
}
function processForm(obj){
var fObj=getInputObject(obj);
//console.log(JSON.stringify(fObj));
google.script.run
.withSuccessHandler(function(rObj){
document.getElementById("btn").disabled=true;
var html='<br /><h1>Data Saved.</h1>';
$(html).appendTo("body");
google.script.host.close();
})
.saveData(fObj);
}
function cancel() {
google.script.host.close();//as dailog
//google.script.run.withSuccessHandler(function(url){window.open(url,'_top');}).getScriptURL();//as a webapp
//https://stackoverflow.com/a/47754851/7215091
}
console.log('My Code');
</script>
form.html
form.html
<h3>Please enter Name, Age, Address and Job Title in the text areas adjacent to the text box descriptions.</h3>
<form id="myForm" onsubmit="event.preventDefault();processForm(this);" >
<br /><select name="Number" id="sel1"></select>
<br /><input type="text" id="txt1" name="Name" /> Name
<br /><input type="text" id="txt2" name="Age" /> Age
<br /><input type="text" id="txt3" name="Address" /> Address
<br /><input type="text" id="txt4" name="Job" /> Job
<br /><input id="btn" type="submit" value="Submit" />
<br />
</form>
<br /><input type="button" value="Cancel/Reload" onClick="cancel();" />
css.html:
<style>
body {background-color:#ffffff;}
input[type="button"],input[type="text"]{margin:0 0 2px 0;}
</style>
resources.html:
resources.html:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
Dailog:
电子表格:
这篇关于图纸提示框脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
