Google表格未被setValues更新

huangapple
go评论143阅读模式
英文:

Google sheet not being updated by setValues

问题

我正在为Google Drive编写一个大文件上传器，当我尝试实现将一些数据写入Google表格时，我遇到了困难，出于某种原因，我无法使其写入数据，也没有出现错误信息。我决定开始一个全新的项目，尽可能简化它，所以它只是获取与我将要获取的相似的数据并进行写入，但仍然没有运气。

我对Google Apps的流程或使用它们的语法不太熟悉，所以我可能只是做了一些非常愚蠢的事情。

旧代码已移除

我尝试移除一些变量，如文件和电子邮件，以确保它们需要以不同的方式进行写入，并更改将表单传递给函数的方式，但我能得到的最佳结果是当我传递一个不存在的表单时出现"无法读取Null"错误。

更新:

一旦我使它工作，我尝试将它插入到我正在使用的主要脚本中（基本上是一个这个的副本），但现在它不起作用，我意识到这可能超出了我的能力范围，不管我尝试什么，它都是一样的，运行并上传文件没有问题，但不会更新表单。

Google脚本:

  1. function doGet(e) {
  2.   return HtmlService.createHtmlOutputFromFile('Form.html');
  3. }
  5. function getAuth() {
  6.   return { accessToken: ScriptApp.getOAuthToken(), folderId: "1sFxs3Ga4xWFCgIXRUnQzCAAp_iRX-wdj" };
  7. }
  9. function setDescription({fileId, description}) {
  10.   DriveApp.getFileById(fileId).setDescription(description);
  11. }
  13. function updateform(formObject) {
  14.   try {
  15.     var ss = SpreadsheetApp.openById('1iCTNZ6RERnes1Y-ocfXzPN3jviwdIEK_dBKQ4LIu5KI');
  16.     var sheet = ss.getSheets()[0];
  17.     sheet.appendRow([myFile.getName(), myFile.getUrl(), formObject.myName], "If This Shows Up It Worked");
  18.   } catch (error) {
  19.     return error.toString();
  20.   }
  21. }

HTML:

  1. <form id="myForm" align="center" onsubmit="updatesheet(This)">
  2.   <input type="text" name="myName" placeholder="Your name..">
  3.   <input type="file" name="myFile">
  4.   <input type="submit" value="Submit Form" onclick="run(); return false;">                 
  5. </form>
  6. <div id="progress"></div>
  7. <div id="output"></div>
  8. <script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
  9. <script>
  10. function onSuccess() {
  11.   var div = document.getElementById('output');
  12.   div.innerHTML = '<a href="Spreadsheet Updated</a>';
  13. }
  15. function onFailure(error) {
  16.   alert(error.message);
  17. }
  19. function updatesheet(form) {
  20.   google.script.run.withSuccessHandler(onSuccess).withFailureHandler(onFailure).updateform(form); 
  21. }
  23. function run() {       
  24.   google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
  25. }
  27. function ResumableUploadForGoogleDrive({accessToken, folderId}) {
  28.   const myName = document.getElementsByName("myName")[0].value;
  29.   const file = document.getElementsByName("myFile")[0].files[0];
  30.   if (!file) return;
  31.   let fr = new FileReader();
  32.   fr.fileName = file.name;
  33.   fr.fileSize = file.size;
  34.   fr.fileType = file.type;
  35.   fr.readAsArrayBuffer(file);
  36.   fr.onload = e => {
  37.     var id = "p";
  38.     var div = document.createElement("div");
  39.     div.id = id;
  40.     document.getElementById("progress").appendChild(div);
  41.     document.getElementById(id).innerHTML = "Initializing.";
  42.     const f = e.target;
  43.     const resource = { fileName: f.fileName, fileSize: f.fileSize, fileType: f.fileType, fileBuffer: f.result, accessToken, folderId };
  44.     const ru = new ResumableUploadToGoogleDrive();
  45.     ru.Do(resource, function (res, err) {
  46.       if (err) {
  47.         console.log(err);
  48.         return;
  49.       }
  50.       console.log(res);
  51.       let msg = "";
  52.       if (res.status == "Uploading") {
  53.         msg = Math.round((res.progressNumber.current / res.progressNumber.end) * 100) + "% (" + f.fileName + ")";
  54.       } else {
  55.         msg = res.status + " (" + f.fileName + ")";
  56.       }
  57.       if (res.status == "Done") {
  58.         google.script.run.withSuccessHandler(_ => {
  59.           document.getElementById('myForm').style display = 'none';
  60.           document.getElementById('p').style.display = 'none';
  61.           document.getElementById('output').innerHTML = "All information submitted, thank you!";
  62.         }).setDescription({fileId: res.result.id, description: "Uploaded by " + myName});
  63.       }
  64.       document.getElementById(id).innerText = msg;
  65.     });
  66.   }
  67. }
  68. </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:

  1. function doGet(e) {
  2. return HtmlService.createHtmlOutputFromFile(&#39;Form.html&#39;);
  3. }
  4. function getAuth() {
  5. return { accessToken: ScriptApp.getOAuthToken(), folderId: &quot;1sFxs3Ga4xWFCgIXRUnQzCAAp_iRX-wdj&quot; };
  6. }
  7. function setDescription({fileId, description}) {
  8. DriveApp.getFileById(fileId).setDescription(description);
  9. }
  10. function updateform(formObject) {
  11. try {
  12. var ss = SpreadsheetApp.openById(&#39;1iCTNZ6RERnes1Y-ocfXzPN3jviwdIEK_dBKQ4LIu5KI&#39;);
  13. var sheet = ss.getSheets()[0];
  14. sheet.appendRow([myFile.getName(), myFile.getUrl(), formObject.myName], &quot;If This Shows Up It Worked&quot;);
  15. } catch (error) {
  16. return error.toString();
  17. }
  18. }

HTML:

  1. &lt;form id=&quot;myForm&quot; align=&quot;center&quot; onsubmit=&quot;updatesheet(This)&quot;&gt;
  2.   &lt;input type=&quot;text&quot; name=&quot;myName&quot; placeholder=&quot;Your name..&quot;&gt;
  3.   &lt;input type=&quot;file&quot; name=&quot;myFile&quot;&gt;
  4.   &lt;input type=&quot;submit&quot; value=&quot;Submit Form&quot; onclick=&quot;run(); return false;&quot;&gt;                 
  5. &lt;/form&gt;
  6. &lt;div id=&quot;progress&quot;&gt;&lt;/div&gt;
  7. &lt;div id=&quot;output&quot;&gt;&lt;/div&gt;
  8. &lt;script src=&quot;https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js&quot;&gt;&lt;/script&gt;
  9. &lt;script&gt;
  10. function onSuccess() {
  11.   var div = document.getElementById(&#39;output&#39;);
  12.   div.innerHTML = &#39;&lt;a href=&quot;Spreadsheet Updated&lt;/a&gt;&#39;;
  13. }
  15. function onFailure(error) {
  16.   alert(error.message);
  17. }
  18. function updatesheet(form) {
  19.   google.script.run.withSuccessHandler(onSuccess).withFailureHandler(onFailure).updateform(form); 
  20. }
  22. function run() {       
  23.   google.script.run.withSuccessHandler(accessToken =&gt; ResumableUploadForGoogleDrive(accessToken)).getAuth();
  24. }
  26. function ResumableUploadForGoogleDrive({accessToken, folderId}) {
  27.   const myName = document.getElementsByName(&quot;myName&quot;)[0].value;
  28.   const file = document.getElementsByName(&quot;myFile&quot;)[0].files[0];
  29.   if (!file) return;
  30.   let fr = new FileReader();
  31.   fr.fileName = file.name;
  32.   fr.fileSize = file.size;
  33.   fr.fileType = file.type;
  34.   fr.readAsArrayBuffer(file);
  35.   fr.onload = e =&gt; {
  36.     var id = &quot;p&quot;;
  37.     var div = document.createElement(&quot;div&quot;);
  38.     div.id = id;
  39.     document.getElementById(&quot;progress&quot;).appendChild(div);
  40.     document.getElementById(id).innerHTML = &quot;Initializing.&quot;;
  41.     const f = e.target;
  42.     const resource = { fileName: f.fileName, fileSize: f.fileSize, fileType: f.fileType, fileBuffer: f.result, accessToken, folderId };
  43.     const ru = new ResumableUploadToGoogleDrive();
  44.     ru.Do(resource, function (res, err) {
  45.       if (err) {
  46.         console.log(err);
  47.         return;
  48.       }
  49.       console.log(res);
  50.       let msg = &quot;&quot;;
  51.       if (res.status == &quot;Uploading&quot;) {
  52.         msg = Math.round((res.progressNumber.current / res.progressNumber.end) * 100) + &quot;% (&quot; + f.fileName + &quot;)&quot;;
  53.       } else {
  54.         msg = res.status + &quot; (&quot; + f.fileName + &quot;)&quot;;
  55.       }
  56.       if (res.status == &quot;Done&quot;) {
  57.         google.script.run.withSuccessHandler(_ =&gt; {
  58.           document.getElementById(&#39;myForm&#39;).style.display = &#39;none&#39;;
  59.           document.getElementById(&#39;p&#39;).style.display = &#39;none&#39;;
  60.           document.getElementById(&#39;output&#39;).innerHTML = &quot;All information submitted, thank you!&quot;;
  61.         }).setDescription({fileId: res.result.id, description: &quot;Uploaded by &quot; + myName});
  62.       }
  63.       document.getElementById(id).innerText = msg;
  64.     });
  65.   }
  66. }
  67. &lt;/script&gt;

答案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 确实执行。所以这段代码对我来说有效。

  1. <!DOCTYPE html>
  2. <html>
  3.   <head>
  4.     <base target="_top">
  5.   </head>
  6.   <body>
  7.     <form id="myForm" align="center" onsubmit="updatesheet(this)">
  8.       <input type="text" name="myName" placeholder="Your name..">
  9.       <input type="text" name="myFile">
  10.       <input type="submit" value="Submit Form">                 
  11.     </form>
  12.     <div id="progress"></div>
  13.     <div id="output"></div>
  14.     <script>
  15.       function onSuccess(error) {
  16.         if( error ) {
  17.           alert(err);
  18.           return;
  19.         }
  20.         alert("onSuccess");
  21.         var div = document.getElementById('output');
  22.         div.innerHTML = "<p>Spreadsheet Updated</p>";
  23.       }
  25.       function run() {
  26.         alert("run");   
  27.       }
  29.       function updatesheet(form) {
  30.         alert("updatesheet");
  31.         google.script.run.withSuccessHandler(onSuccess).updateform(form);
  32.         run();
  33.       }
  34.     </script>
  35.   </body>
  36. </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 &lt;p&gt; instead of an anchor &lt;a&gt;. 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.

  1. &lt;!DOCTYPE html&gt;
  2. &lt;html&gt;
  3. &lt;head&gt;
  4. &lt;base target=&quot;_top&quot;&gt;
  5. &lt;/head&gt;
  6. &lt;body&gt;
  7. &lt;form id=&quot;myForm&quot; align=&quot;center&quot; onsubmit=&quot;updatesheet(this)&quot;&gt;
  8. &lt;input type=&quot;text&quot; name=&quot;myName&quot; placeholder=&quot;Your name..&quot;&gt;
  9. &lt;input type=&quot;text&quot; name=&quot;myFile&quot;&gt;
  10. &lt;input type=&quot;submit&quot; value=&quot;Submit Form&quot;&gt;                 
  11. &lt;/form&gt;
  12. &lt;div id=&quot;progress&quot;&gt;&lt;/div&gt;
  13. &lt;div id=&quot;output&quot;&gt;&lt;/div&gt;
  14. &lt;script&gt;
  15. function onSuccess(error) {
  16. if( error ) {
  17. alert(err);
  18. return;
  19. }
  20. alert(&quot;onSuccess&quot;);
  21. var div = document.getElementById(&#39;output&#39;);
  22. div.innerHTML = &quot;&lt;p&gt;Spreadsheet Updated&lt;/p&gt;&quot;;
  23. }
  24. function run() {
  25. alert(&quot;run&quot;);   
  26. }
  27. function updatesheet(form) {
  28. alert(&quot;updatesheet&quot;);
  29. google.script.run.withSuccessHandler(onSuccess).updateform(form);
  30. run();
  31. }
  32. &lt;/script&gt;
  33. &lt;/body&gt;
  34. &lt;/html&gt;

huangapple
  • 本文由 发表于 2023年2月18日 22:59:04
  • 转载请务必保留本文链接：https://go.coder-hub.com/75494188.html
  • google-apps-script
  • google-sheets
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定