英文:
Google App Script - Web App - Not saving forum data in Google Sheets in the correct format
问题
这是一个我创建的用于将提交保存到Google表格的Google App Script表单。
Code.gs
function doGet() {
var template = HtmlService.createTemplateFromFile('Page');
return template.evaluate();
}
function processForm(form) {
var ss = SpreadsheetApp.openById('SPREADSHEET_ID');
var sheet = ss.getSheetByName('Submissions');
var ref = form.reference;
var agentIds = form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");
var values = sheet.getRange('B:B').getValues().flat();
if (values.includes(ref)) {
return "此参考编号已在先前的名单提交中使用过";
} else {
sheet.appendRow([new Date(), ref, agentIds]);
return "谢谢！您的名单已成功提交";
}
}
Page.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="myForm">
<label for="reference">课程参考号</label>
<input type="text" name="reference" id="reference"><br><br>
<label for="agentIds">代理人ID：</label>
<textarea name="agentIds" id="agentIds" rows="10"></textarea><br><br>
<input type="button" value="提交" onclick="submitForm()">
</form>
<div id="result"></div>
<script>
function submitForm() {
var form = document.getElementById("myForm");
google.script.run.withSuccessHandler(showResult).processForm(form);
}
function showResult(result) {
document.getElementById("result").innerHTML = result;
}
</script>
</body>
</html>
工作原理
该表单由两个字段组成。
- 课程参考号（分配给每个课程的唯一编号）
- 代理人/员工ID的文本区域
用户可以在文本区域中输入代理人ID时使用以下格式，包括可能的拼写错误，如额外的空格、逗号或三种格式的混合。
A) 用空格分隔。
A123456 B001234 TMP00123456
B) 用逗号分隔（可以包含或不包含空格）。
A123456,B001234, TMP00123456
C) 每行一个。
A123456
B001234
TMP00123456
问题
代理人ID保存为 12,345,600,123,400,100,000
期望的结果应为 123456,001234,00123456
当提交表单时，会发生以下情况...
- 正则表达式将清理代理人ID数据，使其输出值为
123456,001234,00123456- 此部分存在问题。 - 脚本将检查是否已提交课程参考号。如果没有，它将继续。
- 时间戳、参考号和代理人ID现在已添加到Google表格中。
| 时间戳 | 参考号 | 代理人ID |
|---|---|---|
| 2023年3月1日0:04:12 | Class105 | 12,345,600,123,400,100,000 |
感谢您的帮助！
英文:
Here is a Google App Script Form that I created to save submissions into a Google Sheet.
Code.gs
function doGet() {
var template = HtmlService.createTemplateFromFile('Page');
return template.evaluate();
}
function processForm(form) {
var ss = SpreadsheetApp.openById('SPREADSHEET_ID');
var sheet = ss.getSheetByName('Submissions');
var ref = form.reference;
var agentIds = form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");
var values = sheet.getRange('B:B').getValues().flat();
if (values.includes(ref)) {
return "This reference number has already been used for a previous roster submission";
} else {
sheet.appendRow([new Date(), ref, agentIds]);
return "Thanks! Your roster has been submitted successfully";
}
}
Page.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="myForm">
<label for="reference">Class Reference #</label>
<input type="text" name="reference" id="reference"><br><br>
<label for="agentIds">Agent IDs:</label>
<textarea name="agentIds" id="agentIds" rows="10"></textarea><br><br>
<input type="button" value="Submit" onclick="submitForm()">
</form>
<div id="result"></div>
<script>
function submitForm() {
var form = document.getElementById("myForm");
google.script.run.withSuccessHandler(showResult).processForm(form);
}
function showResult(result) {
document.getElementById("result").innerHTML = result;
}
</script>
</body>
</html>
HOW IT WORKS
The form consists of 2 fields.
- Class Reference # (A unique number assigned to each class)
- Agent/Employee ID's Textarea
The users may use the following formats when entering the Agent ID's into the textarea, including typos such as extra spaces, commas, or a mixture of all 3 formats.
A) Separated by a space.
A123456 B001234 TMP00123456
B) Separated by a comma (with or without a space too)
A123456,B001234, TMP00123456
C) One / line.
A123456
B001234
TMP00123456
THE PROBLEM
The Agent ID's are being saved as 12,345,600,123,400,100,000
The expected results should be 123456,001234,00123456
When the form is submitted, the following occurs...
- REGEX will clean the Agent ID data so it has the output value of
123456,001234,00123456- This part is broken. - The script will check to see if the class reference has already been submitted. IF not, it will continue.
- The timestamp, Reference, and Agent ID's are now added to the Google Sheet.
| Timestamp | Reference # | Agent IDs' |
|---|---|---|
| 3/1/2023 0:04:12 | Class105 | 12,345,600,123,400,100,000 |
Thanks for your help!
答案1
得分: 1
Pattern 1:
在这种模式下，用于放置 agentIds 的单元格的数字格式更改为文本。
从：
sheet.appendRow([new Date(), ref, agentIds]);
到：
var row = sheet.getLastRow() + 1;
sheet.getRange(row, 3).setNumberFormat("@"");
sheet.getRange(row, 1, 1, 3).setValues([[new Date(), ref, agentIds]]);
Pattern 2:
在这种模式下，在文本顶部添加一个单引号。
从：
var agentIds = form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "").join(",");
到：
var agentIds = "'" + form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "").join(",");
注意：
-
当您修改Web应用的Google Apps脚本时，请将部署设置为新版本。通过这样做，修改后的脚本将反映在Web应用中。请注意这一点。
-
您可以在我的报告 "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)" 中查看详细信息。
英文:
I think that the reason for your current issue is that the value is automatically formatted as a number value. In this case, how about the following 2 patterns?
Pattern 1:
In this pattern, the number format of the cell for putting agentIds is changed as text.
From:
sheet.appendRow([new Date(), ref, agentIds]);
To:
var row = sheet.getLastRow() + 1;
sheet.getRange(row, 3).setNumberFormat("@");
sheet.getRange(row, 1, 1, 3).setValues([[new Date(), ref, agentIds]]);
Pattern 2:
In this pattern, a single quote is added to the top of the text.
From:
var agentIds = form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");
To:
var agentIds = "'" + form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");
Note:
-
When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
-
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论