在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称(OpenSource Name):jamiewilson/form-to-google-sheets开源软件地址(OpenSource Url):https://github.com/jamiewilson/form-to-google-sheets开源编程语言(OpenSource Language):JavaScript 91.7%开源软件介绍(OpenSource Introduction):DemoSubmit a Form to Google Sheets |Google Apps Script, Fetch and FormData.How to create an HTML form that stores the submitted form data in Google Sheets using plain 'ol JavaScript (ES6),1. Create a new Google Sheet
2. Create a Google Apps Script
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()
}
}
3. Run the setup function
4. Add a new project trigger
5. Publish the project as a web app
6. Input your web app URLOpen the file named <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, a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses 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.
7. Adding additional form dataTo capture additional data, you'll just need to create new columns with titles matching exactly the <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
8. Related PolyfillsSome 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. 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. 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.: <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. PRs are definitely welcome, but please run your ideas by me before putting in a lot of work. Thanks! Related/Inspirational Articles
Documentation
|
2023-10-27
2022-08-15
2022-08-17
2022-09-23
2022-08-13
请发表评论