form-to-google-sheets:将HTML表单提交的文件存储在Google表格中

  • R5_414173
    了解作者
  • 14.3KB
    文件大小
  • zip
    文件格式
  • 0
    收藏次数
  • VIP专享
    资源类型
  • 0
    下载次数
  • 2022-05-21 07:03
    上传日期
提交表单至Google表格| 如何使用普通的'ol JavaScript(ES6), , 和创建HTML表单,以将提交的表单数据存储在Google表格中。 1.创建一个新的Google表格 首先,转到然后使用Blank模板Start a new spreadsheet 。 将其重命名为“ Email Subscribers 。 无论如何,都没关系。 将以下标题放入第一行: 一种 乙 C ... 1个 时间戳记 电子邮件 要了解如何添加其他输入字段,请。 2.创建一个Google Apps脚本 单击Tools > Script Editor… ,这将打开一个新选项卡。 将其重命名为“将Submit Form to Google Sheets 。 在编辑脚本之前,请确保等待它实际保存和更新标题。 现在,在Code.gs标签中删除function myFunction() {}块。
form-to-google-sheets-master.zip
  • form-to-google-sheets-master
  • demo
  • index.html
    2.8KB
  • favicon.png
    975B
  • CNAME
    39B
  • main.css
    2.3KB
  • index.html
    516B
  • form-script.js
    1.2KB
  • form-script-commented.js
    4.3KB
  • LICENSE
    11.1KB
  • README.md
    8.7KB
内容介绍
# Submit a Form to Google Sheets | [Demo](https://form-to-google-sheets.surge.sh) #### How to create an HTML form that stores the submitted form data in Google Sheets using plain 'ol JavaScript (ES6), [Google Apps Script](https://developers.google.com/apps-script/), [Fetch](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API) and [FormData](https://developer.mozilla.org/en-US/docs/Web/API/FormData). ## 1. Create a new Google Sheet - First, go to [Google Sheets](https://docs.google.com/spreadsheets) and `Start a new spreadsheet` with the `Blank` template. - Rename it `Email Subscribers`. Or whatever, it doesn't matter. - Put the following headers into the first row: | | A | B | C | ... | |---|:---------:|:-----:|:-:|:---:| | 1 | timestamp | email | | | > To learn how to add additional input fields, [checkout section 7 below](#7-adding-additional-form-data). ## 2. Create a Google Apps Script - Click on `Tools > Script Editor…` which should open a new tab. - Rename it `Submit Form to Google Sheets`. _Make sure to wait for it to actually save and update the title before editing the script._ - Now, delete the `function myFunction() {}` block within the `Code.gs` tab. - Paste the following script in it's place and `File > Save`: ```js var sheetName = 'Sheet1' var scriptProp = PropertiesService.getScriptProperties() function intialSetup () { var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet() scriptProp.setProperty('key', activeSpreadsheet.getId()) } function doPost (e) { var lock = LockService.getScriptLock() lock.tryLock(10000) try { var doc = SpreadsheetApp.openById(scriptProp.getProperty('key')) var sheet = doc.getSheetByName(sheetName) var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0] var nextRow = sheet.getLastRow() + 1 var newRow = headers.map(function(header) { return header === 'timestamp' ? new Date() : e.parameter[header] }) sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]) return ContentService .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow })) .setMimeType(ContentService.MimeType.JSON) } catch (e) { return ContentService .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e })) .setMimeType(ContentService.MimeType.JSON) } finally { lock.releaseLock() } } ``` > If you want to better understand what this script is doing, checkout the [`form-script-commented.js`](https://github.com/jamiewilson/form-to-google-sheets/blob/master/form-script-commented.js) file in the repo for a detailed explanation. ## 3. Run the setup function - Next, go to `Run > Run Function > initialSetup` to run this function. - In the `Authorization Required` dialog, click on `Review Permissions`. - Sign in or pick the Google account associated with this projects. - You should see a dialog that says `Hi {Your Name}`, `Submit Form to Google Sheets wants to`... - Click `Allow` ## 4. Add a new project trigger - Click on `Edit > Current project’s triggers`. - In the dialog click `No triggers set up. Click here to add one now.` - In the dropdowns select `doPost` - Set the events fields to `From spreadsheet` and `On form submit` - Then click `Save` ## 5. Publish the project as a web app - Click on `Publish > Deploy as web app…`. - Set `Project Version` to `New` and put `initial version` in the input field below. - Leave `Execute the app as:` set to `Me(your@address.com)`. - For `Who has access to the app:` select `Anyone, even anonymous`. - Click `Deploy`. - In the popup, copy the `Current web app URL` from the dialog. - And click `OK`. > **IMPORTANT!** If you have a custom domain with Gmail, you _might_ need to click `OK`, refresh the page, and then go to `Publish > Deploy as web app…` again to get the proper web app URL. It should look something like `https://script.google.com/a/yourdomain.com/macros/s/XXXX…`. ## 6. Input your web app URL Open the file named `index.html`. On line 12 replace `<SCRIPT URL>` with your script url: ```js <form name="submit-to-google-sheet"> <input name="email" type="email" placeholder="Email" required> <button type="submit">Send</button> </form> <script> const scriptURL = '<SCRIPT URL>' const form = document.forms['submit-to-google-sheet'] form.addEventListener('submit', e => { e.preventDefault() fetch(scriptURL, { method: 'POST', body: new FormData(form)}) .then(response => console.log('Success!', response)) .catch(error => console.error('Error!', error.message)) }) </script> ``` As you can see, this script uses the the [Fetch API](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API), a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses [FormData](https://developer.mozilla.org/en-US/docs/Web/API/FormData) to pass in our data as URL paramters. Because Fetch and FormData aren't fully supported, you'll likely want to include their respective polyfills. [See section #8](#8-related-polyfills). > **Fun fact!** The `<html>`, `<head>`, and `body` tags are actually among a handful of optional tags, but since the [rules around how the browser parses a page are kinda complicated](https://www.w3.org/TR/2011/WD-html5-20110525/syntax.html#optional-tags), you'd probably not want to omit them on real websites. ## 7. Adding additional form data To capture additional data, you'll just need to create new columns with titles matching exactly the `name` values from your form inputs. For example, if you want to add first and last name inputs, you'd give them `name` values like so: ```html <form name="submit-to-google-sheet"> <input name="email" type="email" placeholder="Email" required> <input name="firstName" type="text" placeholder="First Name"> <input name="lastName" type="text" placeholder="Last Name"> <button type="submit">Send</button> </form> ``` Then create new headers with the exact, case-sensitive `name` values: | | A | B | C | D | ... | |---|:---------:|:-----:|:---------:|:--------:|:---:| | 1 | timestamp | email | firstName | lastName | | ## 8. Related Polyfills Some of this stuff is not yet fully supported by browsers or doesn't work on older ones. Here are some polyfill options to use for better support. - [Promise Polyfill](https://github.com/taylorhakes/promise-polyfill) - [Fetch Polyfill](https://github.com/github/fetch) - [FormData Polyfill](https://github.com/jimmywarting/FormData) Since the FormData polyfill is published as a Node package and needs to be compiled for browsers to work with, a good option for including these is using [Browserify's CDN called wzrd.in](https://wzrd.in/). This service compiles, minifies and serves the latest version of these scripts for us. You'll want to make sure these load before the main script handling the form submission. e.g.: ```html <script src="https://wzrd.in/standalone/formdata-polyfill"></script> <script src="https://wzrd.in/standalone/promise-polyfill@latest"></script> <script src="https://wzrd.in/standalone/whatwg-fetch@latest"></script> <script> const scriptURL = '<SCRIPT URL>' const form = document.forms['submit-to-google-sheet'] ... </script> ``` # Have feedback/requests/issues? Please [create a new issue](https://github.com/jamiewilson/form-to-google-sheet/issues). PRs are definitely welcome, but please run your ideas by me before putting in a lot of work. Thanks! #### Related/Inspirational Articles - [Google Spreadsheets as a Database – INSERT with Apps Script form POST/GET submit method](https://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/) - [Step by step setup to send form data to Google Sheets](http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/) - [Google Sheet Form Post](https://gist.g
评论
    相关推荐
    • JavaScript es6
      es6笔记整理
    • es6入门.zip
      es6入门,最新版,由网上开源博客转换,暗黑主题,护眼
    • es6-star-wars:使用 ES6 的 SWAPI React 实现
      ES6 星球大战 使用 、 和玩新 ES6 功能的存储
    • react-es6-starter
      #React ES6 Starter我的第一个基于React的存储库。 另外添加了对ES6和browserify的支持以加载库。 谢谢2: :
    • es6-practice
      …或在命令行上创建新的存储库echo“#es6-practice” >> README.md git init git add README.md git commit -m“首次提交” git branch -M main git remote add origin git push -u origin main…或从命令行推送...
    • cls-es6-promise:es6-promise 的延续本地存储垫片
      cls-es6-promise 提供了一个垫片层,使其将与。 它通过绑定传递给then所有回调并使用 CLS 命名空间catch来实现这一点。 var cls = require ( 'continuation-local-storage' ) ; var ns = cls . createNamespace ...
    • es6-cheatsheet
      除了var之外,我们现在还可以访问两个用于存储值的新标识符let和const 。 与var不同, let和const语句不会放在其封闭范围的顶部。 使用var的示例: var snack = 'Meow Mix' ; function getFood ( food ) { if ( ...
    • es6features-runtime:ES6 特性运行时
      存储库到使用 Babel 而不是 od Traceur。
    • es6-cycles-comparison
      这个存储库通过各种转译器在 ES6 中运行一个简单的模块循环测试用例。 如果您只是想知道哪些失败了,哪些通过了,请单击上面的 travis 徽章。 如果转译器正确支持循环,则输出代码应该运行而不会出错。 构建输出:...
    • es6-todo-app:另一个带有ES6的待办事项应用
      ES6待办事项清单应用程序 :memo: 另一个待办事项列表应用...您的任务存储在localStorage上 您可以添加和管理多个任务(目前,您只能将其标记为已完成并删除) 另外,您可以在离线时使用应用程序 该应用程序React灵敏