我不熟悉Google表单和Google Apps脚本。我有十个Google表单,并在提交表单中填充相应的Google表单。
现在,这是我想要的,在提交表单时,我希望这些信息也可以存储在云端SQL数据库中。什么步骤可以实现这一目标?为解决方案
正常的数据流为正在接受响应并将其复制到Spreadsheet外观的Google表单像这样:
您有两种表单提交触发器在Cloud SQL数据库中复制表单响应;您可以从 Google表单表单提交触发活动或 Google表格表单提交 event 。 无论哪种方式,您都会为每个表单提交调用一个脚本,以及一个包含响应值的事件对象。您的触发器功能应使用 JDBC 连接到数据库 - 这个链接包括一个介绍,让你了解亮点。
示例假设我们有一个表单,问题,名称和年龄。这将导致我们的电子表格中有3列; 时间戳加上每个问题一个。
为了达到这个目的,我们有一个Cloud SQL数据库设置了相同的三列。
Google表格提交 触发器函数写入云端SQL数据库看起来像这样未经测试的代码:
//用此值替换此块中的变量。 var address ='database_IP_address'; var user ='user_name'; var userPwd ='user_password'; var db ='database_name'; var dbUrl ='jdbc:mysql://'+ address +'/'+ db; //接收表单响应并复制到SQL表中的一行 function handleFormSubmit(event){ var conn = Jdbc.getConnection(dbUrl,user,userPwd); var stmt = conn.prepareStatement('INSERT INTO entries' +'(Timestamp,Name,Age)values(?,?,?)'); stmt.setString(1,event.namedValues ['Timestamp']); stmt.setString(2,event.namedValues ['Name']); stmt.setString(3,event.namedValues ['Age']); stmt.execute(); }
I'm new to Google Forms and Google Apps Script. I have ten Google Forms, and on submitting forms they populate a corresponding Google Sheet.
Now, here is what I want, on submitting form I want that information to also be stored in the cloud SQL database. What steps will accomplish that?
解决方案The normal data flow for a Google Form that is accepting responses and replicating them to a Spreadsheet looks like this:
You've got two opportunities for a Form Submission Trigger to replicate the form responses in your Cloud SQL database; you can either trigger from the Google Form Form submit event or the Google Sheets Form submit event.
Either way, you will have a script that gets called for every form submission, and an event object that contains the response values. Your trigger function should use the JDBC to connect to the database - that link includes an intro that walks you through the highlights.
ExampleSay we have a form asking two questions, "Name" and "Age". That would result in 3 columns in our spreadsheet; "Timestamp" plus one for each question.
To match that, we have a Cloud SQL database set up with the same three columns.
A Google Sheets Form submit trigger function that wrote to a Cloud SQL database would look like this untested code:
// Replace the variables in this block with real values. var address = 'database_IP_address'; var user = 'user_name'; var userPwd = 'user_password'; var db = 'database_name'; var dbUrl = 'jdbc:mysql://' + address + '/' + db; // Receive form response and replicate to a row in SQL table function handleFormSubmit( event ) { var conn = Jdbc.getConnection(dbUrl, user, userPwd); var stmt = conn.prepareStatement('INSERT INTO entries ' + '(Timestamp, Name, Age) values (?, ?, ?)'); stmt.setString(1, event.namedValues['Timestamp']); stmt.setString(2, event.namedValues['Name']); stmt.setString(3, event.namedValues['Age']); stmt.execute(); }
更多推荐
Google表单数据存储在云端SQL数据库中
发布评论