英文:
Google form answers search in google sheets
问题
可以创建一个Google表单，要求输入姓氏和身份证号码，然后在Google表格中搜索这些值。如果找到具有相同姓氏和身份证号码的值，可以回复一个肯定的响应；如果没有找到，则回复一个否定的响应。
英文:
Is it possible to create a Google Form thats asks for surname e idnumber, then search those values into a google sheet and, if a value with that surname and idnumber is found, respond with a positive response or negative if not found?
答案1
得分: 1
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responseSh = ss.getSheetByName("Form Responses 1"); //获取与Google表单链接的工作表上的“Form Responses 1”工作表
var dataSh = ss.getSheetByName("Data"); //在同一工作表中获取姓氏和ID的数据
var responses = responseSh.getRange(responseSh.getLastRow() ,2, 1, 3).getValues().flat();
var data = dataSh.getRange(2,1, dataSh.getLastRow() - 1, 2).getValues();
data.map(x => responses[0] == x[0] && responses[1] == x[1] ? MailApp.sendEmail(responses[2],"测试主题","你好，世界"): x);
}
这段代码的作用是 var responses = responseSh.getRange(responseSh.getLastRow() ,2, 1, 3).getValues().flat(); 获取“Form Responses 1”工作表中的最后一次回复。
然后这行代码 data.map(x => responses[0] == x[0] && responses[1] == x[1] ? MailApp.sendEmail(responses[2],"测试主题","你好，世界"): x); 判断回复是否已经包含了数据中的姓氏和ID。
示例数据：
示例表单：
确保进行了以下设置：
并在App Script的触发器中将此函数设置为表单提交时执行：
这是我提交表单后收到的示例电子邮件：
<details>
<summary>英文:</summary>
**Suggestion:**
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responseSh = ss.getSheetByName("Form Responses 1"); //get the sheet Form Responses 1 on the linked sheet of your Google Forms
var dataSh = ss.getSheetByName("Data"); //data of surnames and ID's in the same sheet
var responses = responseSh.getRange(responseSh.getLastRow() ,2, 1, 3).getValues().flat();
var data = dataSh.getRange(2,1, dataSh.getLastRow() - 1, 2).getValues();
data.map(x => responses[0] == x[0] && responses[1] == x[1] ? MailApp.sendEmail(responses[2],"Test Subject","Hello World"): x);
}
What this does is `var responses = responseSh.getRange(responseSh.getLastRow() ,2, 1, 3).getValues().flat();` gets the last response under the Form Responses 1 sheet.
And then this line of code `data.map(x =\> responses[0] == x[0] && responses[1] == x[1] ? MailApp.sendEmail(responses[2],"Test Subject","Hello World"): x);` evaluates if the response already contains the Surname and ID within the data.
**Sample data:**
[](https://i.stack.imgur.com/ZyZJa.png)
**Sample Form:**
[](https://i.stack.imgur.com/qWxrc.png)
Make sure this is set
[](https://i.stack.imgur.com/nVH7h.png)
And also set this function on Form Submit under Triggers in App Script:
[](https://i.stack.imgur.com/KN0dE.png)
Here is a sample email I received when I submitted the form:
[](https://i.stack.imgur.com/RMPYh.png)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论