How do I make use of an external html form to upload file to google drive and save other form data to google sheets?

Question

I am trying to adapt this tutorial: https://tanaikech.github.io/2020/08/13/uploading-file-to-google-drive-from-external-html-without-authorization/

that is specifically for uploading files to google drive, to include text field data to be saved in a google sheet.

Can any kind soul help me on this :(

This is what I have in my spread sheet:

Here is the GAS code:

    var sheetName = 'Sheet1'
    var scriptProp = PropertiesService.getScriptProperties()

    function intialSetup () {
      var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
      scriptProp.setProperty('key', activeSpreadsheet.getId())
    }

   function doPost(e) {

      const folderId = "root"; // Folder ID which is used for putting the file.
      const blob = Utilities.newBlob(
        JSON.parse(e.postData.contents),
        e.parameter.mimeType,
        e.parameter.filename
      );
  const file = DriveApp.getFolderById(folderId).createFile(blob);
  const responseObj = {
    filename: file.getName(),
    fileId: file.getId(),
    fileUrl: file.getUrl(),
  };

  var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var range = sheet.getRange("A1:A").getValues();
    var filtered_r = range.filter(String).length;

    var newRow = headers.map(function(header) { // newRow is possibly a literally object where the keys are the headers and values are the user input
      if(header === "file"){
        e.parameter[header] = responseObj.fileUrl;
      }
      return e.parameter[header];
    })
  
    sheet.getRange(filtered_r + 1, 1, 1, newRow.length).setValues([newRow]);





  return ContentService.createTextOutput(
    JSON.stringify(responseObj)
  ).setMimeType(ContentService.MimeType.JSON);
}

Here is the external html code(not made in GAS project, which is why I cant use the google.script.run method)

<form id="form">
  <input type="text" name="hobby">
  <input name="file" id="uploadfile" type="file" />
  <input id="submit" type="submit" />
</form>

<script>
  const form = document.getElementById("form");
  form.addEventListener("submit", (e) => {
    e.preventDefault();
    const file = form.file.files[0];
    const fr = new FileReader();
    fr.readAsArrayBuffer(file);
    fr.onload = (f) => {

        
      const url = "https://script.google.com/macros/s/###/exec"; // Please set the URL of Web Apps.

      const qs = new URLSearchParams({
        filename: file.name,
        mimeType: file.type,
      });

      
      fetch(`${url}?${qs}`, {
        method: "POST",
        body: JSON.stringify([...new Int8Array(f.target.result)]), // is it possible to change this value to  new FormData(form)?
      })
        .then((res) => res.json())
        .then(console.log)
        .catch(console.log);
    };
  });
</script>

Answer 1

I believe your goal is as follows.

• You want to upload a file from <input name="file" id="uploadfile" type="file" /> and the value from <input type="text" name="hobby">.

When I saw your script, the file is sent to Web Apps. But, value from <input type="text" name="hobby"> is not sent. I thought that this might be the reason for your issue. In order to remove this issue, the modified script is as follows.

Modified script:

Google Apps Script side:

In this modification, doPost is modified.

function doPost(e) {
  const obj = JSON.parse(e.postData.contents);
  const folderId = "root"; // Folder ID which is used for putting the file.
  const blob = Utilities.newBlob(
    obj.file,
    e.parameter.mimeType,
    e.parameter.filename
  );
  const file = DriveApp.getFolderById(folderId).createFile(blob);
  const responseObj = {
    filename: file.getName(),
    fileId: file.getId(),
    fileUrl: file.getUrl(),
  };
  obj.file = responseObj.fileUrl;
  var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
  var sheet = doc.getSheetByName(sheetName)
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
  var range = sheet.getRange("A1:A").getValues();
  var filtered_r = range.filter(String).length;
  var newRow = headers.map(function (header) {
    return obj[header];
  });
  sheet.getRange(filtered_r + 1, 1, 1, newRow.length).setValues([newRow]);
  return ContentService.createTextOutput(
    JSON.stringify(responseObj)
  ).setMimeType(ContentService.MimeType.JSON);
}

HTML & Javascript side:

<form id="form">
  <input type="text" name="hobby" id="sample" /> <!-- Modified -->
  <input name="file" id="uploadfile" type="file" />
  <input id="submit" type="submit" />
</form>

<script>
  const form = document.getElementById("form");
  form.addEventListener("submit", (e) => {
    e.preventDefault();
    const file = form.file.files[0];
    const fr = new FileReader();
    fr.readAsArrayBuffer(file);
    fr.onload = (f) => {
      const url = "https://script.google.com/macros/s/###/exec"; 
      const qs = new URLSearchParams({
        filename: file.name,
        mimeType: file.type,
      });
      fetch(`${url}?${qs}`, {
        method: "POST",
        body: JSON.stringify({file: [...new Int8Array(f.target.result)], hobby: document.getElementById("sample").value}),
      })
        .then((res) => res.json())
        .then(console.log)
        .catch(console.log);
    };
  });
</script>

Note:

• When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
• You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

Added:

After I published the sample script https://tanaikech.github.io/2020/08/13/uploading-file-to-google-drive-from-external-html-without-authorization/ you use, I also have published a Javascript library for parsing HTML form for using with Google Apps Script. Ref When this library is used, your script is as follows.

Sample script:

Google Apps Script side:

In this modification, doPost is modified.

function doPost(e) {
  const obj = JSON.parse(e.postData.contents);
  const folderId = "root"; // Folder ID which is used for putting the file.
  const f = obj.file[0].files[0];
  const blob = Utilities.newBlob(
    f.bytes,
    f.mimeType,
    f.filename
  );
  const file = DriveApp.getFolderById(folderId).createFile(blob);
  const responseObj = {
    filename: file.getName(),
    fileId: file.getId(),
    fileUrl: file.getUrl(),
  };
  obj.file = [{ value: responseObj.fileUrl }];
  var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
  var sheet = doc.getSheetByName(sheetName)
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
  var range = sheet.getRange("A1:A").getValues();
  var filtered_r = range.filter(String).length;
  var newRow = headers.map(function (header) {
    return obj[header][0].value;
  })
  sheet.getRange(filtered_r + 1, 1, 1, newRow.length).setValues([newRow]);
  return ContentService.createTextOutput(
    JSON.stringify(responseObj)
  ).setMimeType(ContentService.MimeType.JSON);
}

HTML & Javascript side:

<form id="form">
  <input type="text" name="hobby" id="sample" /> <!-- Modified -->
  <input name="file" id="uploadfile" type="file" />
  <input id="submit" type="submit" />
</form>

<script src="https://cdn.jsdelivr.net/gh/tanaikech/HtmlFormObjectParserForGoogleAppsScript_js@master/htmlFormObjectParserForGoogleAppsScript_js.min.js"></script>
<script>
  const url = "https://script.google.com/macros/s/###/exec";
  const form = document.getElementById("form");
  form.addEventListener("submit", async (e) => {
    e.preventDefault();
    const obj = await ParseFormObjectForGAS(form);
    fetch(url, {
      method: "POST",
      body: JSON.stringify(obj),
    })
    .then((res) => res.json())
    .then(console.log)
    .catch(console.log);
  });
</script>

Reference:

• HtmlFormObjectParserForGoogleAppsScript_js