CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheetsgoogle-drive-apigoogle-forms
Eliza Matthew
Eliza Matthew

Reputation: 27

Google Script - Get Google Drive Link to Sheet on custom form submit (file upload field)

I am new on Google Scripts. I am trying to retrieve the uploaded Google Drive's file link to the Sheet upon form submission, on a custom Google Form. However on form submit, only the file name is added to the sheet. Is there a way to get the file's Google Drive link to the sheet on form submit?

Here's what I have so far:

Page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!--Import Google Icon Font-->
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <!-- Compiled and minified CSS -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
  </head>
  <body>

<form id="form" action="https://docs.google.com/forms/u/0/d/e/####/formResponse" method="POST">
  <div class="file-field input-field">
    <input type="file" id="uploadfile" name="file">
    <input id="upload_MC" name="entry.685812703" class="file-path validate" type="text" placeholder="Upload MC or other reference if applicable" onchange="uploadMC()">
  </div>
  
  <input id="submit" type="submit"/>
</form>

<script>
  function uploadMC(){
    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"; //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)]),
      })
        .then((res) => res.json())
        .then(console.log)
        .catch(console.log);
    };
  }
</script>

<!-- Compiled and minified JavaScript -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
  </body>
  
</html>

Code.gs

function doGet(e){
  return HtmlService.createTemplateFromFile("page").evaluate().addMetaTag("viewport", "width=device-width, initial-scale=1.0");
}

function doPost(e) {
  const folderId = "###"; // 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 || "root").createFile(blob);
  const responseObj = file.getUrl();
  return ContentService.createTextOutput(
    JSON.stringify(responseObj)
  ).setMimeType(ContentService.MimeType.JSON);
}

The page.html is my custom Google Form that sends the data to the linked Sheet. Right now it does upload the file and the file name are submitted in the Sheet, but is there a way to get the file's viewing link to be inserted into the Sheet instead of the file's name?

Thank you in advance and have a nice day!

Upvotes: 0

Views: 475

Answers (1)

idfurw
idfurw

Reputation: 5852

The url to the file is returned by Code.gs

  const responseObj = file.getUrl();
  return ContentService.createTextOutput(
    JSON.stringify(responseObj)
  ).setMimeType(ContentService.MimeType.JSON);
  1. Add the url field to both your custom Google Form and the original Google Form.
<input id="uploadUrl" type="hidden" name="entry.XXX">
  1. Modify the function to pass the url to the input field
        .then((res) => res.json())
        .then(json => {
          console.log(json);
          uploadUrl.value = JSON.stringify(json);
        })
        .catch(console.log);

Upvotes: 1

Related Questions