存储在云 SQL 数据库中的 Google 表单数据 [英] Google Form data to store in cloud SQL database

问题描述

我是 Google 表单和 Google Apps 脚本的新手.我有十个 Google 表单，在提交表单时，它们会填充相应的 Google 表格.

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.

现在，这就是我想要的，在提交表单时，我希望该信息也存储在云 SQL 数据库中.哪些步骤将实现这一目标?

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?

推荐答案

接受响应并将其复制到电子表格的 Google 表单的正常数据流如下所示:

The normal data flow for a Google Form that is accepting responses and replicating them to a Spreadsheet looks like this:

您有两次机会让表单提交触发器复制 Cloud SQL 数据库中的表单响应；您可以从 Google 表单表单提交触发事件或Google表格表单提交 事件.

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.

无论哪种方式，您都会有一个为每次表单提交调用的脚本，以及一个包含响应值的事件对象.您的触发器函数应使用 JDBC 连接到数据库 -该链接包含一个介绍，可引导您了解亮点.

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.

假设我们有一个表单询问两个问题，姓名"和年龄".这将导致我们的电子表格中有 3 列；每个问题的时间戳"加一个.

Say 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.

为了匹配，我们使用相同的三列设置了一个 Cloud SQL 数据库.

To match that, we have a Cloud SQL database set up with the same three columns.

写入 Cloud SQL 数据库的 Google Sheets Form submit 触发器函数如下所示未经测试代码:

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();
}

这篇关于存储在云 SQL 数据库中的 Google 表单数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！