Google工作表未收到表单提交 [英] Google sheet is not receiving form submits
问题描述
我创建了工作表: https://docs.google.com/spreadsheets/dUJWHWWWZUCWC edit?usp = sharing
，并通过脚本编辑器创建了以下表单:
and from the script editor I created the below form:
Code.gs
function doGet() {
return HtmlService.createTemplateFromFile('Form.html')
.evaluate() // evaluate MUST come before setting the Sandbox mode
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
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()
}
}
Form.html
Form.html
<!DOCTYPE html>
<html>
<body>
<style>
</style>
<form name="submit-to-google-sheet" id="form" method="POST" onsubmit="myFunction()">
<input name="TEXT" type="text" placeholder="text" required>
<input type="submit" value="Submit" name="submit" id="Submit">
</form>
<script>
function myFunction() {
alert("The form was submitted. Please press okay to reload the page");
}
</script>
<script>
const scriptURL = 'https://script.google.com/macros/s/AKfycbxdtFz3L5Zczor9v-CGvm1yLzTogasSF__22oadV80ZFMQFH18/exec'
const form = document.forms['submit-to-google-sheet']
form.addEventListener('submit', e => {
e.preventDefault()
window.open("URL after form submit", "_top")
fetch(scriptURL, { method: 'POST', body: new FormData(form)})
.then(response => console.log('Success!', response))
.catch(error => console.error('Error!', error.message))
})
</script>
</body>
</html>
同一代码在另一张工作表中对我有用，但是当我出于某种原因试图在此工作表中重复时，表单未提交到工作表中.有什么帮助吗?并且，请您以正确的方式给出说明，以将相同的代码复制到其他工作表中，以便当我创建具有不同列的另一个工作表并将相同的代码粘贴到脚本编辑器时，需要做些什么才能使脚本适用于新的工作表床单等.谢谢
The same code is working for me in another sheet but when I tried to repeat in this sheet for some reason the form is not submitting to the sheet. Any help? And please would you give instructions in the proper way to copy the same code to other sheets so when I create another sheet with different columns and pasting the same code to the script editor, what needs to be done to make the script works for the new sheet and so on. Thanks
推荐答案
您需要更改以下内容:
-
将脚本复制到另一个电子表格并作为WebApp发布时-
scriptURL将要更改-请使用新的电子表格进行更新.
When you copy your script to another spreadsheet and publish it as a WebApp -
scriptURLis going to change - please update it with the new one.
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))仅在为新脚本设置属性key时才起作用.作为解决方法，只需用var doc = SpreadsheetApp.getActive();替换此行-这将使脚本自动找到绑定到的电子表格.
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key')) is only going to work if you set up the property key for the new script. AS as workaround, just replace this line by var doc = SpreadsheetApp.getActive(); - this will make the script automatically find the spreadsheet it is bound to.
这篇关于Google工作表未收到表单提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
