如何使用JDBC将响应发送到Cloud SQL？

I work for a non-profit that holds free sports and physical activity events. I'm am trying to set up a system to store and collect information about our membership and the events we hold, using Google Docs as the user interface and Google Cloud SQL for data storage.

我为一个持有免费体育和体育活动的非营利组织工作。我正在尝试建立一个系统来存储和收集有关我们的会员资格和我们举办的活动的信息,使用Google Docs作为用户界面,使用Google Cloud SQL进行数据存储。

Like most non-profits, we do not have a lot of resources available for advanced computer programming. I would like to use Google Forms as a simple UI that our research and evaluation staff can use to build data collection tools without coding. The ease of access to the responses in Google Sheets is great; however, we hold a lot of events and will quickly exceed the 2 million cell limit. So, I think we will need to store the responses in a SQL database.

像大多数非营利组织一样,我们没有很多资源可用于高级计算机编程。我想将Google表单用作一个简单的UI,我们的研究和评估人员可以使用它来构建数据收集工具而无需编码。在Google表格中轻松访问回复非常有用;但是,我们举办了很多活动,很快就会超过200万的细胞限制。所以,我认为我们需要将响应存储在SQL数据库中。

What I would like to do is modify the action performed on submit, such that the form:

我想做的是修改提交时执行的操作,例如:

Does NOT submit to a sheet

不提交到工作表

Connects to a SQL table (I've set this up on Google Cloud SQL)

连接到SQL表(我在Google Cloud SQL上设置了这个)

Dumps the responses into the correct columns

将响应转储到正确的列中

If the form was modified, add any new columns to the table (like Forms does with attached sheets).

如果表单已修改,请向表中添加任何新列(如表单附加表单)。

I know that Google Apps Script can connect to external databases through the JDBC service (https://developers.google.com/apps-script/guides/jdbc) and I know that I will need to use the getItemResponse method (https://groups.google.com/d/msg/google-appengine-*/laLkcneaqZo/PfPKKYlmva8J) to execute this.

我知道Google Apps脚本可以通过JDBC服务(https://developers.google.com/apps-script/guides/jdbc)连接到外部数据库,我知道我需要使用getItemResponse方法(https:/ /groups.google.com/d/msg/google-appengine-*/laLkcneaqZo/PfPKKYlmva8J)执行此操作。

I have very little experience with Google Apps Script or JavaScript in general....basically, the most I've done is finish the Codecademy JavaScript course. However, I am a quick learner and I'm looking for a little example code that might get me started (remembering that I have almost no experience).

我对Google Apps脚本或JavaScript的总体经验很少....基本上,我所做的最多就是完成Codecademy JavaScript课程。但是,我是一个快速学习者,我正在寻找可能让我开始的一些示例代码(记住我几乎没有经验)。

I've searched through this forum, GitHub and everywhere else I can think of, but cannot find something that is quite right. I'm mostly pointed to the JBDC documentation, but do not know how to use it. Any help you can provide would be much appreciated.

我搜索了这个论坛,GitHub和我能想到的其他地方,但找不到一些非常正确的东西。我主要指向JBDC文档,但不知道如何使用它。您可以提供的任何帮助将不胜感激。

Brett

1 个解决方案

#1

I wrote some articles about this at my blog, Bit Vectors. This article might cover most of what you need. That specific article explains how to build a Google Apps Script solution tied to a Google Sheet file. The GAS solution front-ends Cloud SQL. The end-user sees a web page / form, and if you want, the Google Sheet itself. To answer your list items:

我在我的博客Bit Vectors上写了一些关于这个的文章。本文可能涵盖您需要的大部分内容。该特定文章介绍了如何构建与Google Sheet文件相关联的Google Apps脚本解决方案。 GAS解决方案前端Cloud SQL。最终用户可以看到网页/表单,如果需要,还可以看到Google表格本身。要回答您的清单项目:

1) The web form does write to a sheet, but in the software, you can easily prevent this

1)Web表单写入表单,但在软件中,您可以轻松地防止这种情况发生

2) The web form definitely connects to your Cloud SQL table / database

2)Web表单肯定连接到您的Cloud SQL表/数据库

3) The solution writes to the table(s) / column(s) you want through stored procedures

3)解决方案通过存储过程写入您想要的表/列

4) The solution will not modify the data table structure if you change the form - you would have to handle this change yourself

4)如果更改表单,解决方案将不会修改数据表结构 - 您必须自己处理此更改

See a demo here.

在这里看一个演示。

HTH!

Frank

#1