jQuery AJAX 发布到谷歌电子表格

AJAX post to google spreadsheet

提问by Stuart Nelson

I am attempting to post form data to a google spreadsheet. Currently, if the form is validated, then the following occurs:

我正在尝试将表单数据发布到谷歌电子表格。目前，如果表单经过验证，则会发生以下情况：

if (validateForm === true) {
        $.ajax({
            type: 'post',
            url: 'https://docs.google.com/spreadsheet/ccc?key=0AlwuDjMUxwhqdGp1WU1KQ0FoUGZpbFRuUDRzRkszc3c',
            data: $("#workPLZ").serialize(),
            success: alert($("#workPLZ").serialize())
        });
    }
    else {}

I used the success setting to verify my form data is being serialized properly (it is) and that it is successful. However, my google spreadsheet is not being updated (no data is going through). I used the example code here, changing doGet to doPost (http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/), and have made the google spreadsheet publicly available (and editable by anyone). I followed the instructions, copying in the code to googledocs and then running the setUp twice (first time asked for permission, second time I ran it I didn't notice anything happen). Can anyone help me? I feel like I am super close.

我使用成功设置来验证我的表单数据是否被正确序列化（它是）并且它是成功的。但是，我的谷歌电子表格没有更新（没有数据通过）。我在这里使用了示例代码，将 doGet 更改为 doPost ( http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit- method/），并已公开提供谷歌电子表格（任何人都可以编辑）。我按照说明，将代码复制到 googledocs，然后运行 ​​setUp 两次（第一次请求许可，第二次运行它我没有注意到任何事情发生）。谁能帮我？我觉得我超级接近。

回答by Stuart Nelson

Alright, I came up with a solution. After being informed of the cross-domain AJAX issues, I decided to go ahead and follow to a "t" the method used by the article author at http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/.

好吧，我想出了一个解决方案。在得知跨域 AJAX 问题后，我决定继续遵循文章作者在http://mashe.hawksey.info/2011/10/google-spreadsheets-as 上使用的方法-a-database-insert-with-apps-script-form-postget-submit-method/。

To post data to your google spreadsheet, first make the spreadsheet and change the sheet name (lower left hand corner) to DATA. Next, open the script editor (Tools ==> Script Editor) in your spreadsheet and paste the script from the article. Change "doGet(e)" to "doPost(e)". Run the setUp script twice. The first time it will ask for permission to run (grant it), then the second time you select to run it you won't get any popup indication it has run (I ran mine in the editor so it said "running setUp" above the code entry area, but that was all). After that, select "Publish" in the script editor, and then select "Publish as Service". Click the "Allow anyone to invoke this service" radio button and the "Allow anonymous access" check box. Copy the URL (IMPORTANT!) and click "Enable Service". This was the "difficult part".

要将数据发布到您的 google 电子表格，首先制作电子表格并将工作表名称（左下角）更改为 DATA。接下来，在电子表格中打开脚本编辑器（工具 ==> 脚本编辑器）并粘贴文章中的脚本。将“doGet(e)”更改为“doPost(e)”。运行设置脚本两次。第一次它会请求运行权限（授予它），然后第二次选择运行它时，您将不会看到它已运行的任何弹出指示（我在编辑器中运行了我的，所以上面说“正在运行 setUp”代码输入区域，但仅此而已）。之后，在脚本编辑器中选择“发布”，然后选择“发布为服务”。单击“允许任何人调用此服务”单选按钮和“允许匿名访问”复选框。复制网址（重要！) 并单击“启用服务”。这是“困难的部分”。

In your HTML form, each element that you're submitting must have a "name" attribute (e.g. ) This name is how the data is sent - each entry is attached to its name. Make sure that for every piece of form data you're collecting, it has a name, and that name is entered as a column on your spreadsheet (that's how it maps the data from your form to your spreadsheet). For your form, set the method to post and the action to your "Publish as Service" URL (which I told you to save) like this:

在您的 HTML 表单中，您提交的每个元素都必须有一个“名称”属性（例如 ）。这个名称是数据的发送方式 - 每个条目都附加到其名称。确保您收集的每条表单数据都有一个名称，并且该名称作为电子表格中的一列输入（这就是将表单中的数据映射到电子表格的方式）。对于您的表单，将方法设置为 post 并将操作设置为您的“发布为服务”URL（我告诉您保存），如下所示：

<form id="formID" method="post" action="URL" target="hidden_iframe">

I included a form id so I can select the form and submit it with jquery. In your HTML, before the above form, add your hidden iframe:

我包含了一个表单 ID，所以我可以选择表单并使用 jquery 提交它。在您的 HTML 中，在上述表单之前，添加您隐藏的 iframe：

<iframe name="hidden_iframe" id="hidden_iframe" style="display:none;"></iframe>

Set some sort of form validation (not necessary, but if every field isn't filled out you'll get incomplete data in your spreadsheet), and if it is validated have it call a jquery .submit(). e.g.:

设置某种形式的验证（不是必需的，但如果每个字段都没有填写，你会在电子表格中得到不完整的数据），如果它被验证了，让它调用 jquery .submit()。例如：

    if (formValidation === true){
           $("#formID").submit();
    }
    else {}

So that's that. Good luck!

所以就是这样。祝你好运！

回答by mhawksey

As Google Apps Script now has a ContentServicethat can return JSON responses it is possible to make ajax requests without using the hidden iframe. As the article author of the original solution I've published an updated version of this technique which includes an ajax example

由于 Google Apps Script 现在有一个可以返回 JSON 响应的ContentService，因此可以在不使用隐藏的 iframe 的情况下发出 ajax 请求。作为原始解决方案的文章作者，我发布了此技术的更新版本，其中包括一个 ajax 示例

Users may also want to consider switching to this new version as it utilises other new Google Apps Script Services, in particular:

用户可能还需要考虑切换到这个新版本，因为它使用了其他新的 Google Apps 脚本服务，特别是：

• using LockServiceto prevent concurrent write access; and
• switching to the new PropertiesServiceto store script data

• 使用LockService来防止并发写访问；和
• 切换到新的PropertiesService来存储脚本数据

回答by user5807327

I will show you the EASY WAY to send data to a Google SpreadSheet without AJAX neither Google Form, or PHP...just Google SpreadSheet and HTML or even Android.

我将向您展示在没有 AJAX 的情况下将数据发送到 Google SpreadSheet 的简单方法，既不是 Google Form，也不是 PHP ...只是 Google SpreadSheet 和 HTML 甚至 Android。

1. Create a new Google SpreadSheet.
2. Open Tools/script editor

1. 创建一个新的 Google 电子表格。
2. 打开工具/脚本编辑器

You just need two files in the editor a HTML and a Code.gs:

你只需要编辑器中的两个文件，一个 HTML 和一个 Code.gs：

as an example:

举个例子：

1. Go to File/new HTML name this file=Index.html:

   <!DOCTYPE html>
   <html>
   <head>
   <base target="_top">
   
   <script>
   function Enviar(){
   
   var txt1=document.getElementById("txt1").value;
   var txt2=document.getElementById("txt2").value;
   var txt3=document.getElementById("txt3").value;
   google.script.run.doSomething(txt1,txt2,txt3);
   }
   </script>
   </head>
   
   <body>
   Prueba de Envio de informacion<br>
   <input type="text" value="EMAIL" name="txt1" id="txt1"><br>
   <input type="text" value="CEDULA" name="txt2" id="txt2"><br>
   <input type="text" value="NOMBRE" name="txt3" id="txt3"><BR>
   <Button name="btn1" onClick="Enviar();">Enviar</button>
   </body>
   
   </html>
   

3. 转到文件/新 HTML 命名此文件=Index.html：

   <!DOCTYPE html>
   <html>
   <head>
   <base target="_top">
   
   <script>
   function Enviar(){
   
   var txt1=document.getElementById("txt1").value;
   var txt2=document.getElementById("txt2").value;
   var txt3=document.getElementById("txt3").value;
   google.script.run.doSomething(txt1,txt2,txt3);
   }
   </script>
   </head>
   
   <body>
   Prueba de Envio de informacion<br>
   <input type="text" value="EMAIL" name="txt1" id="txt1"><br>
   <input type="text" value="CEDULA" name="txt2" id="txt2"><br>
   <input type="text" value="NOMBRE" name="txt3" id="txt3"><BR>
   <Button name="btn1" onClick="Enviar();">Enviar</button>
   </body>
   
   </html>
   

there are 3 fields to send: EMAIL, CEDULA, NOMBRE

有 3 个字段要发送：EMAIL、CEDULA、NOMBRE

1. In the same ScriptEditor go to de Code.gs file and type:

   function doGet() {
   return HtmlService.createHtmlOutputFromFile('Index')
   .setSandboxMode(HtmlService.SandboxMode.IFRAME);
   }
   
   function doSomething(s1,s2,s3){
   
   Logger.log('datos:'+s1+"  "+s2+"  "+s3);
   var enlace="https://docs.google.com/spreadsheets/d/
   1XuAXmUeGz2Ffr11R8YZNihLE_HSck9Hf_mRtFSXjWGw/edit";
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var ss = SpreadsheetApp.openByUrl(enlace);
   var sheet = ss.getSheets()[0];
   sheet.appendRow([s1, s2, s3]);
   Logger.log(ss.getName());
   }
   

4. 在同一个 ScriptEditor 中，转到 de Code.gs 文件并键入：

   function doGet() {
   return HtmlService.createHtmlOutputFromFile('Index')
   .setSandboxMode(HtmlService.SandboxMode.IFRAME);
   }
   
   function doSomething(s1,s2,s3){
   
   Logger.log('datos:'+s1+"  "+s2+"  "+s3);
   var enlace="https://docs.google.com/spreadsheets/d/
   1XuAXmUeGz2Ffr11R8YZNihLE_HSck9Hf_mRtFSXjWGw/edit";
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var ss = SpreadsheetApp.openByUrl(enlace);
   var sheet = ss.getSheets()[0];
   sheet.appendRow([s1, s2, s3]);
   Logger.log(ss.getName());
   }
   

where enlace is the url of your SpreadSheet

其中 enlace 是您的电子表格的网址

1. Publish as Application App and get the url of your new Script. Now you can use this url as embed in your HTML application or android app. That's it. The user will be asked for permission to open this script

5. 发布为应用程序并获取新脚本的 URL。现在，您可以将此 url 嵌入到您的 HTML 应用程序或 android 应用程序中。就是这样。将要求用户授予打开此脚本的权限