英文:
How to add reset button in the dialog in Google Sheets
问题
我是你的中文翻译，以下是翻译好的部分：
我是新手脚本编写，但从这个网站学到了一些东西，我已经在Google Sheets中创建了一个支票余额表。
我有一个名为“AddCheck”的函数，附加到一个单元格上的按钮。它的作用是打开一个对话框，在对话框中我可以输入支票的详细信息，如日期、发票号、金额和供应商的名称。然后我点击提交按钮，Google Sheets会创建一行并添加这些值。
我的问题是，如何在提交按钮旁边添加一个按钮，允许我在不离开对话框的情况下添加新的支票详细信息。这样它将把值添加到单元格中，并清除对话框以进行另一次输入。
这是我的AddCheck函数：
function AddCheck() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('3:3').activate();
spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
spreadsheet.getRange('A3').activate();
spreadsheet.getCurrentCell().setFormula('=A4+1');
spreadsheet.getRange('G3').activate();
spreadsheet.getCurrentCell().setValue('Pending');
spreadsheet.getRange('B3').activate();
fncOpenMyDialog();
};
这是我的HTML对话框文件：
<!DOCTYPE html>
<html>
<body>
<form>
<label for="Date">日期：</label>
<input type='date' name='Date' id="Date" required="required"/><br>
<label for="Invoice">发票：</label>
<input type='text' name='Invoice' id="Invoice" required="required"/>
<label for="Amount">金额：</label>
<input type='text' name='Amount' id="Amount" required="required"/>
<label for="Company">公司：</label>
<select name="Class" id="vendor-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">
<script>
(function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById("vendor-selector");
for( var i=0; i<selectList.length; i++ ) {
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).getSelectList();
}());
</script>
</select>
<input type="submit" value="提交" onclick="myFunction(this.parentNode)">
</form>
<p id="CompanyName"></p>
<script>
function myFunction(obj) {
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(() => google.script.host.close())
.functionToRunOnFormSubmit(obj);
}
</script>
</body>
</html>
这个函数调用对话框：
function fncOpenMyDialog() {
// 打开对话框
var htmlDlg = HtmlService.createHtmlOutputFromFile('CheckDetails')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(200)
.setHeight(250);
SpreadsheetApp.getUi()
.showModalDialog(htmlDlg, '支票详情');
};
这个函数从Sheet2获取供应商列表：
function getSelectList() {
var sheet = SpreadsheetApp.openById("141mlnxJBjepKxYCGXHFhz5IIEVnp6T2DDsb_uRgnZzY").getSheetByName('Sheet2');
var lastRow = sheet.getLastRow();
var myRange = sheet.getRange("A2:A" + lastRow);
var data = myRange.getValues();
Logger.log("数据 = " + data);
return data;
};
function doGet() {
return HtmlService.createHtmlOutputFromFile('CheckDetails');
}
希望对你有所帮助。如果有任何问题，请随时提出。
英文:
I'm new to the scripting, but from what I have learned from this website, I have put together a check book balancesheet in google sheets.
I have a function "AddCheck" attached to the button on one of the cell. What it does is - opens a dialog box where I enter check details, like Date, Invoice number, amount and vendor's name. Then I click the submit button and Google sheets creates a row and adds those values.
My Question is how do I add a button next to the submit button that will allow me to add the New check details without leaving the dialog box. So that it will add the values to the cells and will clear the dialog box for another Entry.
This is my AddCheck function
function AddCheck() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('3:3').activate();
spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
spreadsheet.getRange('A3').activate();
spreadsheet.getCurrentCell().setFormula('=A4+1');
spreadsheet.getRange('G3').activate();
spreadsheet.getCurrentCell().setValue('Pending');
spreadsheet.getRange('B3').activate();
fncOpenMyDialog()
};
This is my HTML dialog file
<!DOCTYPE html>
<html>
<body>
<form>
<label for="Date">Date :</label>
<input type='date' name='Date' id="Date" required="required"/>
<br>
<label for="Invoice">Invoice</label>
<input type='text' name='Invoice' id="Invoice" required="required"/>
<label for="Amount">Amount</label>
<input type='text' name='Amount' id="Amount" required="required"/>
<label for="Company">Company</label>
<select name="Class" id="vendor-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">
<script>
(function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById("vendor-selector");
for( var i=0; i<selectList.length; i++ ) {
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).getSelectList();
}());
</script>
</select>
<input type="submit" value="Submit" onclick="myFunction(this.parentNode)">
</form>
<p id="CompanyName"></p>
<script>
function myFunction(obj) {
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(() => google.script.host.close())
.functionToRunOnFormSubmit(obj);
}
</script>
</body>
</html>
This Function calls the Dialog
function fncOpenMyDialog() {
//Open a dialog
var htmlDlg = HtmlService.createHtmlOutputFromFile('CheckDetails')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(200)
.setHeight(250);
SpreadsheetApp.getUi()
.showModalDialog(htmlDlg, 'Check Details');
};
function functionToRunOnFormSubmit(fromInputForm) {
Logger.log(fromInputForm);
var ss = SpreadsheetApp.getActive();
ss.getSheetByName("Checks").getRange(3,3).setValues([[fromInputForm.Class]]);
ss.getSheetByName("Checks").getRange(3,2).setValues([[fromInputForm.Date]]);
ss.getSheetByName("Checks").getRange(3,4).setValues([[fromInputForm.Invoice]]);
ss.getSheetByName("Checks").getRange(3,6).setValues([[fromInputForm.Amount]]);
};
and this Function gets the List of vendors from Sheet2
function getSelectList()
{
var sheet = SpreadsheetApp.openById("141mlnxJBjepKxYCGXHFhz5IIEVnp6T2DDsb_uRgnZzY").getSheetByName('Sheet2');
var lastRow = sheet.getLastRow();
var myRange = sheet.getRange("A2:A" + lastRow);
var data = myRange.getValues();
Logger.log("Data = " + data);
return data;
};
function doGet()
{
return HtmlService.createHtmlOutputFromFile('CheckDetails');
}
Thank you for your help.
答案1
得分: 0
以下是您要翻译的内容：
-
In order to implement this "Submit & Continue" feature, you only need to change the front-end code so that:
为了实现这个“提交并继续”功能，您只需要更改前端代码，以便：
-
There is a new button.
添加一个新按钮。
-
Upon clicking the new button, the same back-end (GAS) code gets executed.
单击新按钮后，将执行相同的后端（GAS）代码。
-
The dialog does not get closed after the GAS code execution, but the input elements are reset.
在GAS代码执行后，对话框不会关闭，但输入元素会被重置。
Code modifications
代码修改
-
Add an id to the form tag so that it may be easily selected.
在表单标签中添加一个id，以便可以轻松选择它。
-
Create a "Submit & Continue" button in your HTML. Assign an id to it so that it may be easily selected.
在您的HTML中创建一个“提交并继续”按钮。为它分配一个id，以便可以轻松选择它。
-
Within the
scripttag, add an event listener to the newly created button.在
script标签内，为新创建的按钮添加一个事件监听器。 -
Add the handler function for the newly created button's
onclickevent. This function will be very similar as the one you are using but: It will use thepreventDefaultto avoid the form being incorrectly sent and will clear the form data upon submission.为新创建的按钮的
onclick事件添加处理程序函数。这个函数将与您正在使用的函数非常相似，但它会使用preventDefault来防止表单被错误地发送，并在提交后清除表单数据。
Example dialog:
示例对话框：
Edit
编辑
Handling "submit and continue" new rows:
处理“提交并继续”新行：
-
Remove the row-insertion functionality from
AddCheck(). We will handle this logic afterwards:从
AddCheck()中删除插入行的功能。我们将在之后处理这个逻辑： -
Modify the
functionToRunOnFormSubmit()function so that it handles the row-insertion logic. I have also cleaned up the code a little bit. It would look like the following:修改
functionToRunOnFormSubmit()函数，以处理插入行的逻辑。我还稍微整理了一下代码。它将如下所示：
Full code
完整代码
CheckDetails.html
CheckDetails.html
<!DOCTYPE html>
<html>
<body>
<form id="CheckDetailsForm">
<label for="Date">Date :</label>
<input type='date' name='Date' id="Date" required="required"/>
<br>
<label for="Invoice">Invoice</label>
<input type='text' name='Invoice' id="Invoice" required="required"/>
<label for="Amount">Amount</label>
<input type='text' name='Amount' id="Amount" required="required"/>
<label for="Company">Company</label>
<select name="Class" id="vendor-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">
<script>
(function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById("vendor-selector");
for( var i=0; i<selectList.length; i++ ) {
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).getSelectList();
}());
</script>
</select>
<input type="submit" value="Submit" id="SubmitInput" />
<input type="submit" value="Submit and continue" id="SubmitAndContinueInput" />
</form>
<p id="CompanyName"></p>
<script>
document.getElementById("SubmitInput").addEventListener("click", myFunction);
document.getElementById("SubmitAndContinueInput").addEventListener("click", myFunctionContinue);
function clearForm() {
document.getElementById('Date').value = "";
document.getElementById('Invoice').value = "";
document.getElementById('Amount').value = "";
}
function myFunction(e) {
e.preventDefault();
var obj = document.getElementById("CheckDetailsForm");
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(() => google.script.host.close())
.functionToRunOnFormSubmit(obj);
}
function myFunctionContinue(e) {
e.preventDefault();
var obj = document.getElementById("CheckDetailsForm");
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(clearForm)
.functionToRunOnFormSubmit(obj);
}
</script>
</body>
</html>
Code.gs
Code.gs
function AddCheck() {
fncOpenMyDialog()
}
function fncOpenMyDialog() {
//Open a dialog
var htmlDlg = HtmlService.createHtmlOutputFromFile('CheckDetails')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(200)
.setHeight(250);
SpreadsheetApp.getUi()
.showModalDialog(htmlDlg, 'Check Details');
}
function functionToRunOnFormSubmit(fromInputForm) {
Logger.log(fromInputForm);
var sheet = SpreadsheetApp.getActive().getSheetByName("Checks");
sheet.insertRowBefore(3);
sheet.getRange("A3").setFormula("=A4+1");
sheet.getRange("B3").setValue(fromInputForm.Date);
sheet.getRange("C3").setValue(fromInputForm.Class);
sheet.getRange("D3").setValue(fromInputForm.Invoice);
sheet.getRange("F3").setValue(fromInputForm.Amount);
sheet.getRange("G3").setValue("Pending");
}
function getSelectList() {
var sheet = SpreadsheetApp.openById("141mlnxJBjepKxYCGXHFhz5IIEVnp6T2DDsb_uRgnZzY").getSheetByName('Sheet2');
var lastRow = sheet.getLastRow();
var myRange = sheet.getRange("A2:A" + lastRow);
var data = myRange.getValues();
Logger.log("Data = " + data);
return data;
}
function doGet() {
return HtmlService.createHtmlOutputFromFile('CheckDetails');
<details>
<summary>英文:</summary>
In order to implement this "Submit & Continue" feature, you only need to change the front-end code so that:
1. There is a new button.
2. Upon clicking the new button, the same back-end (GAS) code gets executed.
3. The dialog does not get closed after the GAS code execution, but the input elements are reset.
### Code modifications
1. Add an id to the form tag so that it may be easily selected.
```html
<form id="CheckDetailsForm">
```
2. Create a "Submit & Continue" button in your HTML. Assign an id to it so that it may be easily selected.
```html
<input type="submit" value="Submit and continue" id="SubmitAndContinueInput" />
```
3. Within the `script` tag, add an event listener to the newly created button.
```js
document.getElementById("SubmitAndContinueInput").addEventListener("click", myFunctionContinue);
```
4. Add the handler function for the newly created button's `onclick` event. This function will be very similar as the one you are using but: It will use the [`preventDefault`](https://developer.mozilla.org/en-US/docs/Web/API/Event/preventDefault) to avoid the form being incorrectly sent and will clear the form data upon submission.
```js
function clearForm() {
document.getElementById('Date').value = "";
document.getElementById('Invoice').value = "";
document.getElementById('Amount').value = "";
}
function myFunctionContinue(e) {
e.preventDefault();
var obj = document.getElementById("CheckDetailsForm");
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(clearForm)
.functionToRunOnFormSubmit(obj);
}
```
### Example dialog:
[![Example Sheets dialog][1]][1]
[1]: https://i.stack.imgur.com/yslGW.png
### Edit
Handling "submit and continue" new rows:
1. Remove the row-insertion functionality from `AddCheck()`. We will handle this logic afterwards:
```js
function AddCheck() {
fncOpenMyDialog();
}
```
2. Modify the `functionToRunOnFormSubmit()` function so that it handles the row-insertion logic. I have also cleaned up the code a little bit. It would look like the following:
```js
function functionToRunOnFormSubmit(fromInputForm) {
Logger.log(fromInputForm);
var sheet = SpreadsheetApp.getActive().getSheetByName("Checks");
sheet.insertRowBefore(3);
sheet.getRange("A3").setFormula("=A4+1");
sheet.getRange("B3").setValue(fromInputForm.Date);
sheet.getRange("C3").setValue(fromInputForm.Class);
sheet.getRange("D3").setValue(fromInputForm.Invoice);
sheet.getRange("F3").setValue(fromInputForm.Amount);
sheet.getRange("G3").setValue("Pending");
}
```
### Full code
*CheckDetails.html*
```html
<!DOCTYPE html>
<html>
<body>
<form id="CheckDetailsForm">
<label for="Date">Date :</label>
<input type='date' name='Date' id="Date" required="required"/>
<br>
<label for="Invoice">Invoice</label>
<input type='text' name='Invoice' id="Invoice" required="required"/>
<label for="Amount">Amount</label>
<input type='text' name='Amount' id="Amount" required="required"/>
<label for="Company">Company</label>
<select name="Class" id="vendor-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">
<script>
(function () {
google.script.run.withSuccessHandler(
function (selectList) {
var select = document.getElementById("vendor-selector");
for( var i=0; i<selectList.length; i++ ) {
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).getSelectList();
}());
</script>
</select>
<input type="submit" value="Submit" id="SubmitInput" />
<input type="submit" value="Submit and continue" id="SubmitAndContinueInput" />
</form>
<p id="CompanyName"></p>
<script>
document.getElementById("SubmitInput").addEventListener("click", myFunction);
document.getElementById("SubmitAndContinueInput").addEventListener("click", myFunctionContinue);
function clearForm() {
document.getElementById('Date').value = "";
document.getElementById('Invoice').value = "";
document.getElementById('Amount').value = "";
}
function myFunction(e) {
e.preventDefault();
var obj = document.getElementById("CheckDetailsForm");
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(() => google.script.host.close())
.functionToRunOnFormSubmit(obj);
}
function myFunctionContinue(e) {
e.preventDefault();
var obj = document.getElementById("CheckDetailsForm");
var x = document.getElementById("vendor-selector").value;
document.getElementById("CompanyName").innerHTML = x;
google.script.run
.withSuccessHandler(clearForm)
.functionToRunOnFormSubmit(obj);
}
</script>
</body>
</html>
Code.gs
function AddCheck() {
fncOpenMyDialog()
}
function fncOpenMyDialog() {
//Open a dialog
var htmlDlg = HtmlService.createHtmlOutputFromFile('CheckDetails')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setWidth(200)
.setHeight(250);
SpreadsheetApp.getUi()
.showModalDialog(htmlDlg, 'Check Details');
}
function functionToRunOnFormSubmit(fromInputForm) {
Logger.log(fromInputForm);
var sheet = SpreadsheetApp.getActive().getSheetByName("Checks");
sheet.insertRowBefore(3);
sheet.getRange("A3").setFormula("=A4+1");
sheet.getRange("B3").setValue(fromInputForm.Date);
sheet.getRange("C3").setValue(fromInputForm.Class);
sheet.getRange("D3").setValue(fromInputForm.Invoice);
sheet.getRange("F3").setValue(fromInputForm.Amount);
sheet.getRange("G3").setValue("Pending");
}
function getSelectList() {
var sheet = SpreadsheetApp.openById("141mlnxJBjepKxYCGXHFhz5IIEVnp6T2DDsb_uRgnZzY").getSheetByName('Sheet2');
var lastRow = sheet.getLastRow();
var myRange = sheet.getRange("A2:A" + lastRow);
var data = myRange.getValues();
Logger.log("Data = " + data);
return data;
}
function doGet() {
return HtmlService.createHtmlOutputFromFile('CheckDetails');
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论