英文:

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:**

[![image](https://i.stack.imgur.com/ZyZJa.png)](https://i.stack.imgur.com/ZyZJa.png)

**Sample Form:**

[![image](https://i.stack.imgur.com/qWxrc.png)](https://i.stack.imgur.com/qWxrc.png)

Make sure this is set

[![image](https://i.stack.imgur.com/nVH7h.png)](https://i.stack.imgur.com/nVH7h.png)

And also set this function on Form Submit under Triggers in App Script:

[![image](https://i.stack.imgur.com/KN0dE.png)](https://i.stack.imgur.com/KN0dE.png)

Here is a sample email I received when I submitted the form:

[![image](https://i.stack.imgur.com/RMPYh.png)](https://i.stack.imgur.com/RMPYh.png)

</details>