Apps Script append values from two functions to the same row

Question

I have two functions in Apps Script, both write values to my Google Sheet. I wanted to have them on the same row. I tried to use

sheet.getLastRow().setValues(sheet.appendRow([obj.name, obj.mimeType, obj.id, Utilities.formatDate(new Date(), "GMT+8:00", "yyyy-MM-dd'T'HH:mm:ss'Z'")])

But it didn't work.

Here's my codes, which I've tried to adapt from my previous question (Multiple bulk import entries using Google Apps Script): Code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate(); 
}

function getAuth() {
  // DriveApp.createFile() // This is used for adding the scope of "https://www.googleapis.com/auth/drive".
  return ScriptApp.getOAuthToken();
}

function update(formObject) {
   SpreadsheetApp.getActiveSpreadsheet()
   .getSheets()[0]
   .appendRow([formObject.myName]);   
}

function putFileInf(obj) {
    SpreadsheetApp.getActiveSpreadsheet()
      .getSheets()[0]
      .appendRow([obj.name, obj.mimeType, obj.id, Utilities.formatDate(new Date(), "GMT+8:00", "yyyy-MM-dd'T'HH:mm:ss'Z'")]);
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form id="fr">
      <input type="text" name="myName">
      <input type="file" id="file1" />
      <input type="file" id="file2" />
      <input type="button" onclick="run()" value="Upload" />  
    </form>
    <div id="progress"></div>

<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
<script>
function run() {
  google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
  
}

function ResumableUploadForGoogleDrive(accessToken) {
  const f1 = document.getElementById("file1").files[0];
  const f2 = document.getElementById("file2").files[0];
  const fObj = document.getElementById("fr");
  [f1, f2].forEach((file, i) => {
    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" + ++i;
      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: accessToken, folderId: "1NbwjLmYXyXjkIV1SjQL3ThnO97kssrcc" };
      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.update(fObj);
            google.script.run.putFileInf(res.result);
            
          }

        document.getElementById(id).innerText = msg;
      });
    };
  });
}


</script>
</body>
</html>

An example of what I have in mind: Say user enters name (myName) as Linda, then upload a photo with filename of 'pic.jpg'. I want to print out 'Linda pic.jpg' in the Google Sheet. But what I have right now is:

Linda

pic.jpg

If you have any advice, I'll be very grateful! Have a good day :))

score 2 · Accepted Answer · answered Apr 13 '22 at 09:30

In your script, how about the following modification? In this modification, your HTML & javascript is modified.

Modified script:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form id="fr">
      <input type="text" id="name" name="myName"> <!-- Modified -->
      <input type="file" id="file1" />
      <input type="file" id="file2" />
      <input type="button" onclick="run()" value="Upload" />  
    </form>
    <div id="progress"></div>

<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
<script>
function run() {
  google.script.run.withSuccessHandler(accessToken => ResumableUploadForGoogleDrive(accessToken)).getAuth();
  
}

function ResumableUploadForGoogleDrive(accessToken) {
  const f1 = document.getElementById("file1").files[0];
  const f2 = document.getElementById("file2").files[0];
  const name = document.getElementById("name").value; // Modified
  [f1, f2].forEach((file, i) => {
    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" + ++i;
      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: accessToken, folderId: "root" };
      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") {
            res.result.name = name; // Added
            google.script.run.putFileInf(res.result);
          }

        document.getElementById(id).innerText = msg;
      });
    };
  });
}


</script>
</body>
</html>

In this modification, the value is retrieved from the text input tag. And, include it in res.result. By this, obj.name of .appendRow([obj.name, obj.mimeType, obj.id, Utilities.formatDate(new Date(), "GMT+8:00", "yyyy-MM-dd'T'HH:mm:ss'Z'")]) has the value of name.
In this case, your update of Google Apps Script side is not used.

Thank you for your swift response! It works, again! If it's not too much of a trouble, I was wondering if you tell me what other variables are contained in the res obj? Cuz I'm also looking to include the file URL in the sheet. I've just started to search for the right codes so I don't have any to show, but can you just drop me some hints? Thanks! — LauraEverdeen, Apr 13 '22 at 10:56
@LauraEverdeen Thank you for replying. I'm glad your issue was resolved. About your additional question, I have to apologize for my poor English skill. Unfortunately, I cannot understand your additional question. Can I ask you about the detail of it? — Tanaike, Apr 13 '22 at 11:46
Thanks for responding. I just meant to ask if you can suggest a way to modify the code so that I can 'get' the link to the uploaded file? It's like `file.getUrl();` when we use `createFile()` function to upload a file. Is there a similar function for resumable uploads? — LauraEverdeen, Apr 13 '22 at 12:40
@LauraEverdeen Thank you for replying. From your reply, I understood that you wanted the URL by `getUrl()`. `getUrl()` returns `https://drive.google.com/file/d/{fileId}/view?usp=drivesdk`. When the file is uploaded, you can retrieve the URL using the file ID of the uploaded file. If this was not useful, I apologize. — Tanaike, Apr 13 '22 at 13:29
Thank you so much! For your patience and everything. It definitely helps — LauraEverdeen, Apr 14 '22 at 00:52

Apps Script append values from two functions to the same row

1 Answers1

Modified script: