WebApp Google Script to collect data and upload a file

Question

I have got some code from another user in this fourm, but the code is uploading the file but not appending the textbox filed data and uploaded file link to the sheet. i just want to have file upload and details regarding the file to be entered in the textboxes (present code is for name and email- that can be modified later for capturing other data) and the text box data along with the link of the file being uploded to be appended in the file containing the script.

form.html is as below:

<!-- Include the Google CSS package -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">

<!-- You can also include your own CSS styles -->
<style>
 form { margin: 40px auto; }
 input { display:inline-block; margin: 20px; }
 </style>

 <script>

 // The function will be called after the form is submitted
  function uploadFile() {
document.getElementById('uploadFileButton').value = "Uploading File..";
google.script.run
   .withSuccessHandler(fileUploaded)
   .uploadFiles(document.getElementById("labnol"));
return false;
}

// This function will be called after the Google Script has executed
 function fileUploaded(status) {
document.getElementById('labnol').style.display = 'none';
document.getElementById('output').innerHTML = status;
}

</script>

<!-- This is the HTML form -->
<form id="labnol">

 <!-- Text input fields -->
 <input type="text" id="nameField" name="myName" placeholder="Your name..">
 <input type="email" id="emailField" name="myEmail" placeholder="Your email..">

 <!-- File input filed -->
 <input type="file" name="myFile">

 <!-- The submit button. It calls the server side function uploadfiles() on click -->
 <input type="submit" id="uploadFileButton" value="Upload File"
     onclick="this.value='Uploading..';uploadFile();return false;">

</form>

<!-- Here the results of the form submission will be displayed -->
<div id="output"></div>

code.gs is a s below:

/* The script is deployed as a web app and renders the form */
 function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('form.html')
        .setSandboxMode(HtmlService.SandboxMode.NATIVE);
    // This is important as file upload fail in IFRAME Sandbox mode.
   }

   /* This function will process the submitted form */
 function uploadFiles(form) {

 try {

/* Name of the Drive folder where the files should be saved */
var DMS = "Test Form Submissions";
var folder, folders = DriveApp.getFoldersByName(DMS);

/* Find the folder, create if the folder does not exist */
if (folders.hasNext()) {
  folder = folders.next();
} else {
  folder = DriveApp.createFolder(DMS);
} 

/* Get the file uploaded though the form as a blob */
var blob = form.myFile;
var file = folder.createFile(blob);

//Allocate variables for submissions in sheet
var namerecord = form.myName;
var emailrecord = form.myEmail;

/* Set the file description as the name of the uploader */
file.setDescription("Uploaded by " + form.myName);

/* Return the download URL of the file once its on Google Drive */
return "File uploaded successfully " + file.getUrl();

var uploadURL = file.getUrl();

 var url = "https://docs.google.com/spreadsheets/d/1Kk8uvMHC506UVuhjzsvKka-RTp5OEjWV-yeoWxeV5b4/edit#gid=0";
 var ss = SpreadsheetApp.openByUrl(url);
 var ws = ss.getSheetByName("Inward");

 ws.appendRow([namerecord,emailrecord,uploadURL]);


 /*var googsheet = 



SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1N80542YrEAT6Llq6k2vzDdkSO938csVv28UtUCNlpFU/edit?usp=sharing');
  return "started";
  var sheet = googsheet.getSheetName('Inward');
  sheet.appendRow([namerecord,emailrecord,uploadURL]);
  return "completed";
*/
  //

  } catch (error) {

    /* If there's an error, show the error message */
    return error.toString();
  }

        //Open spreadsheet based on URL
    
}

Please help in this regard. I am new to scripting and web apps.

Answer 1

Issue:

Function doGet contains a return statement after uploading the file and before appending the data to the spreadsheet:

return "File uploaded successfully " + file.getUrl();

This statement ends this function execution, so the script never get to the append data part.

Solution:

Assuming that you have access to this spreadsheet (variable url) and that this spreadsheet contains a sheet called Inward, your data should be successfully appended to the spreadsheet as long as the return statement is commented out/removed, or, if you want the page to return File uploaded successfully {fileUrl}, moved to the end of your try block:

ws.appendRow([namerecord,emailrecord,uploadURL]);
return "File uploaded successfully " + file.getUrl();