Google表格未被setValues更新

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

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(&#39;Form.html&#39;);
}
function getAuth() {
return { accessToken: ScriptApp.getOAuthToken(), folderId: &quot;1sFxs3Ga4xWFCgIXRUnQzCAAp_iRX-wdj&quot; };
}
function setDescription({fileId, description}) {
DriveApp.getFileById(fileId).setDescription(description);
}
function updateform(formObject) {
try {
var ss = SpreadsheetApp.openById(&#39;1iCTNZ6RERnes1Y-ocfXzPN3jviwdIEK_dBKQ4LIu5KI&#39;);
var sheet = ss.getSheets()[0];
sheet.appendRow([myFile.getName(), myFile.getUrl(), formObject.myName], &quot;If This Shows Up It Worked&quot;);
} catch (error) {
return error.toString();
}
}

HTML:

&lt;form id=&quot;myForm&quot; align=&quot;center&quot; onsubmit=&quot;updatesheet(This)&quot;&gt;
  &lt;input type=&quot;text&quot; name=&quot;myName&quot; placeholder=&quot;Your name..&quot;&gt;
  &lt;input type=&quot;file&quot; name=&quot;myFile&quot;&gt;
  &lt;input type=&quot;submit&quot; value=&quot;Submit Form&quot; onclick=&quot;run(); return false;&quot;&gt;                 
&lt;/form&gt;
&lt;div id=&quot;progress&quot;&gt;&lt;/div&gt;
&lt;div id=&quot;output&quot;&gt;&lt;/div&gt;
&lt;script src=&quot;https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js&quot;&gt;&lt;/script&gt;
&lt;script&gt;
function onSuccess() {
  var div = document.getElementById(&#39;output&#39;);
  div.innerHTML = &#39;&lt;a href=&quot;Spreadsheet Updated&lt;/a&gt;&#39;;
}

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 =&gt; ResumableUploadForGoogleDrive(accessToken)).getAuth();
}

function ResumableUploadForGoogleDrive({accessToken, folderId}) {
  const myName = document.getElementsByName(&quot;myName&quot;)[0].value;
  const file = document.getElementsByName(&quot;myFile&quot;)[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 =&gt; {
    var id = &quot;p&quot;;
    var div = document.createElement(&quot;div&quot;);
    div.id = id;
    document.getElementById(&quot;progress&quot;).appendChild(div);
    document.getElementById(id).innerHTML = &quot;Initializing.&quot;;
    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 = &quot;&quot;;
      if (res.status == &quot;Uploading&quot;) {
        msg = Math.round((res.progressNumber.current / res.progressNumber.end) * 100) + &quot;% (&quot; + f.fileName + &quot;)&quot;;
      } else {
        msg = res.status + &quot; (&quot; + f.fileName + &quot;)&quot;;
      }
      if (res.status == &quot;Done&quot;) {
        google.script.run.withSuccessHandler(_ =&gt; {
          document.getElementById(&#39;myForm&#39;).style.display = &#39;none&#39;;
          document.getElementById(&#39;p&#39;).style.display = &#39;none&#39;;
          document.getElementById(&#39;output&#39;).innerHTML = &quot;All information submitted, thank you!&quot;;
        }).setDescription({fileId: res.result.id, description: &quot;Uploaded by &quot; + myName});
      }
      document.getElementById(id).innerText = msg;
    });
  }
}
&lt;/script&gt;

答案1

得分: 3

有关您更新的代码有几个事项。

首先,应该是 this 而不是 This

其次,您为同一个表单同时使用了 onsubmitonclick 事件。我认为 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 &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.

&lt;!DOCTYPE html&gt;
&lt;html&gt;
&lt;head&gt;
&lt;base target=&quot;_top&quot;&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;form id=&quot;myForm&quot; align=&quot;center&quot; onsubmit=&quot;updatesheet(this)&quot;&gt;
&lt;input type=&quot;text&quot; name=&quot;myName&quot; placeholder=&quot;Your name..&quot;&gt;
&lt;input type=&quot;text&quot; name=&quot;myFile&quot;&gt;
&lt;input type=&quot;submit&quot; value=&quot;Submit Form&quot;&gt;                 
&lt;/form&gt;
&lt;div id=&quot;progress&quot;&gt;&lt;/div&gt;
&lt;div id=&quot;output&quot;&gt;&lt;/div&gt;
&lt;script&gt;
function onSuccess(error) {
if( error ) {
alert(err);
return;
}
alert(&quot;onSuccess&quot;);
var div = document.getElementById(&#39;output&#39;);
div.innerHTML = &quot;&lt;p&gt;Spreadsheet Updated&lt;/p&gt;&quot;;
}
function run() {
alert(&quot;run&quot;);   
}
function updatesheet(form) {
alert(&quot;updatesheet&quot;);
google.script.run.withSuccessHandler(onSuccess).updateform(form);
run();
}
&lt;/script&gt;
&lt;/body&gt;
&lt;/html&gt;

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

发表评论

匿名网友

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

确定