Home > Mobile >  Apps Script append values from two functions to the same row
Apps Script append values from two functions to the same row

Time:04-13

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 :))

CodePudding user response:

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.

  • Related