英文:
Google sheet not being updated by setValues
问题
我正在为Google Drive编写一个大文件上传器,当我尝试实现将一些数据写入Google表格时,我遇到了困难,出于某种原因,我无法使其写入数据,也没有出现错误信息。我决定开始一个全新的项目,尽可能简化它,所以它只是获取与我将要获取的相似的数据并进行写入,但仍然没有运气。
我对Google Apps的流程或使用它们的语法不太熟悉,所以我可能只是做了一些非常愚蠢的事情。
旧代码已移除
我尝试移除一些变量,如文件和电子邮件,以确保它们需要以不同的方式进行写入,并更改将表单传递给函数的方式,但我能得到的最佳结果是当我传递一个不存在的表单时出现"无法读取Null"错误。
更新:
一旦我使它工作,我尝试将它插入到我正在使用的主要脚本中(基本上是一个这个的副本),但现在它不起作用,我意识到这可能超出了我的能力范围,不管我尝试什么,它都是一样的,运行并上传文件没有问题,但不会更新表单。
Google脚本:
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Form.html');
}
function getAuth() {
return { accessToken: ScriptApp.getOAuthToken(), folderId: "1sFxs3Ga4xWFCgIXRUnQzCAAp_iRX-wdj" };
}
function setDescription({fileId, description}) {
DriveApp.getFileById(fileId).setDescription(description);
}
function updateform(formObject) {
try {
var ss = SpreadsheetApp.openById('1iCTNZ6RERnes1Y-ocfXzPN3jviwdIEK_dBKQ4LIu5KI');
var sheet = ss.getSheets()[0];
sheet.appendRow([myFile.getName(), myFile.getUrl(), formObject.myName], "If This Shows Up It Worked");
} catch (error) {
return error.toString();
}
}
HTML:
<form id="myForm" align="center" onsubmit="updatesheet(This)">
<input type="text" name="myName" placeholder="Your name..">
<input type="file" name="myFile">
<input type="submit" value="Submit Form" onclick="run(); return false;">
</form>
<div id="progress"></div>
<div id="output"></div>
<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
<script>
function onSuccess() {
var div = document.getElementById('output');
div.innerHTML = '<a href="Spreadsheet Updated</a>';
}
function onFailure(error) {
alert(error.message);
}
function updatesheet(form) {
google.script.run.withSuccessHandler(onSuccess).withFailureHandler(onFailure).updateform(form);
}
function run() {
google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
}
function ResumableUploadForGoogleDrive({accessToken, folderId}) {
const myName = document.getElementsByName("myName")[0].value;
const file = document.getElementsByName("myFile")[0].files[0];
if (!file) return;
let fr = new FileReader();
fr.fileName = file.name;
fr.fileSize = file.size;
fr.fileType = file.type;
fr.readAsArrayBuffer(file);
fr.onload = e => {
var id = "p";
var div = document.createElement("div");
div.id = id;
document.getElementById("progress").appendChild(div);
document.getElementById(id).innerHTML = "Initializing.";
const f = e.target;
const resource = { fileName: f.fileName, fileSize: f.fileSize, fileType: f.fileType, fileBuffer: f.result, accessToken, folderId };
const ru = new ResumableUploadToGoogleDrive();
ru.Do(resource, function (res, err) {
if (err) {
console.log(err);
return;
}
console.log(res);
let msg = "";
if (res.status == "Uploading") {
msg = Math.round((res.progressNumber.current / res.progressNumber.end) * 100) + "% (" + f.fileName + ")";
} else {
msg = res.status + " (" + f.fileName + ")";
}
if (res.status == "Done") {
google.script.run.withSuccessHandler(_ => {
document.getElementById('myForm').style display = 'none';
document.getElementById('p').style.display = 'none';
document.getElementById('output').innerHTML = "All information submitted, thank you!";
}).setDescription({fileId: res.result.id, description: "Uploaded by " + myName});
}
document.getElementById(id).innerText = msg;
});
}
}
</script>
英文:
I am writing a large file uploader for Google Drive and when I tried to implement writing some data to a Google Sheet I ran into a brick wall, for whatever reason I could not get it to ever write or even give a error as to why. I decided to start a whole new project and made it as simple as possible so all it does is grab similar data to what I will be grabbing and write it, but still no luck.
I am not super familiar with the Google Apps processes or the syntax of using them so I am probably just doing something really stupid.
Old code removed
I have tried removing some variables like file and email in case they needed to be written differently and changing how the form is passed to the function but the best I ever got was a "Cannot read Null" error when I passed it a form that didn't exist.
UPDATE:
Once I had it working I tried to slip it into the main script I am using (Which is basically a copy of this but now its not working, I am realizing this may be over my head unfortunately cause no matter what I try its doing the same, runs and uploads the file fine, but does not update the form.
Google Scripts:
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Form.html');
}
function getAuth() {
return { accessToken: ScriptApp.getOAuthToken(), folderId: "1sFxs3Ga4xWFCgIXRUnQzCAAp_iRX-wdj" };
}
function setDescription({fileId, description}) {
DriveApp.getFileById(fileId).setDescription(description);
}
function updateform(formObject) {
try {
var ss = SpreadsheetApp.openById('1iCTNZ6RERnes1Y-ocfXzPN3jviwdIEK_dBKQ4LIu5KI');
var sheet = ss.getSheets()[0];
sheet.appendRow([myFile.getName(), myFile.getUrl(), formObject.myName], "If This Shows Up It Worked");
} catch (error) {
return error.toString();
}
}
HTML:
<form id="myForm" align="center" onsubmit="updatesheet(This)">
<input type="text" name="myName" placeholder="Your name..">
<input type="file" name="myFile">
<input type="submit" value="Submit Form" onclick="run(); return false;">
</form>
<div id="progress"></div>
<div id="output"></div>
<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
<script>
function onSuccess() {
var div = document.getElementById('output');
div.innerHTML = '<a href="Spreadsheet Updated</a>';
}
function onFailure(error) {
alert(error.message);
}
function updatesheet(form) {
google.script.run.withSuccessHandler(onSuccess).withFailureHandler(onFailure).updateform(form);
}
function run() {
google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
}
function ResumableUploadForGoogleDrive({accessToken, folderId}) {
const myName = document.getElementsByName("myName")[0].value;
const file = document.getElementsByName("myFile")[0].files[0];
if (!file) return;
let fr = new FileReader();
fr.fileName = file.name;
fr.fileSize = file.size;
fr.fileType = file.type;
fr.readAsArrayBuffer(file);
fr.onload = e => {
var id = "p";
var div = document.createElement("div");
div.id = id;
document.getElementById("progress").appendChild(div);
document.getElementById(id).innerHTML = "Initializing.";
const f = e.target;
const resource = { fileName: f.fileName, fileSize: f.fileSize, fileType: f.fileType, fileBuffer: f.result, accessToken, folderId };
const ru = new ResumableUploadToGoogleDrive();
ru.Do(resource, function (res, err) {
if (err) {
console.log(err);
return;
}
console.log(res);
let msg = "";
if (res.status == "Uploading") {
msg = Math.round((res.progressNumber.current / res.progressNumber.end) * 100) + "% (" + f.fileName + ")";
} else {
msg = res.status + " (" + f.fileName + ")";
}
if (res.status == "Done") {
google.script.run.withSuccessHandler(_ => {
document.getElementById('myForm').style.display = 'none';
document.getElementById('p').style.display = 'none';
document.getElementById('output').innerHTML = "All information submitted, thank you!";
}).setDescription({fileId: res.result.id, description: "Uploaded by " + myName});
}
document.getElementById(id).innerText = msg;
});
}
}
</script>
答案1
得分: 3
有关您更新的代码有几个事项。
首先,应该是 this
而不是 This
。
其次,您为同一个表单同时使用了 onsubmit
和 onclick
事件。我认为 onclick
会抑制提交事件。请完全移除 onclick
。
第三,您在 updateform
中使用了一个 try-catch 块,因此 withFailureHandler
永远不会执行。相反,错误消息或 null 会被返回到成功处理程序 onSuccess(error)
。
第四,我使用了段落 <p>
而不是锚点 <a>
。您的锚点中的 href 格式不正确。
最后,run()
可以在 updatesheet(form)
中执行。请注意,run()
是异步的,这意味着它在 google.script.run
执行完成之前不会等待。
我可以简单地告诉您,所有的警报都会显示,执行日志显示 updateform
确实执行。所以这段代码对我来说有效。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="myForm" align="center" onsubmit="updatesheet(this)">
<input type="text" name="myName" placeholder="Your name..">
<input type="text" name="myFile">
<input type="submit" value="Submit Form">
</form>
<div id="progress"></div>
<div id="output"></div>
<script>
function onSuccess(error) {
if( error ) {
alert(err);
return;
}
alert("onSuccess");
var div = document.getElementById('output');
div.innerHTML = "<p>Spreadsheet Updated</p>";
}
function run() {
alert("run");
}
function updatesheet(form) {
alert("updatesheet");
google.script.run.withSuccessHandler(onSuccess).updateform(form);
run();
}
</script>
</body>
</html>
希望这有所帮助。
英文:
Several things about your updated code.
First it should be this
not This
.
Second you have onsubmit
and onclick
events for the same form. I believe the onclick
is suppressing the submit event. Remove onclick
entirely.
Third you use a try catch block in updateform
so withFailureHandler
will never execute. Instead the error message or null is returned to the success handler onSuccess(error)
.
Forth, I use a paragraph <p>
instead of an anchor <a>
. The href is malformed in your anchor.
Last, run()
can be executed in updatesheet(form)
. Note run()
is asynchronous which means it doesn't wait for google.script.run
to finish before executing.
I can simply tell you that all the alerts are displayed and the execution log shows updateform
did execute. So this code works for me.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form id="myForm" align="center" onsubmit="updatesheet(this)">
<input type="text" name="myName" placeholder="Your name..">
<input type="text" name="myFile">
<input type="submit" value="Submit Form">
</form>
<div id="progress"></div>
<div id="output"></div>
<script>
function onSuccess(error) {
if( error ) {
alert(err);
return;
}
alert("onSuccess");
var div = document.getElementById('output');
div.innerHTML = "<p>Spreadsheet Updated</p>";
}
function run() {
alert("run");
}
function updatesheet(form) {
alert("updatesheet");
google.script.run.withSuccessHandler(onSuccess).updateform(form);
run();
}
</script>
</body>
</html>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论