1

I'm attempting to add a menu option in a Google Sheet that uploads a file to drive, then inserts a link to that file in the Sheet. I'm having problems with the upload function, called from HTML.

Code.gs

function onOpen(e){
  Logger.log('Opened spreadsheet');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "File", functionName: "openHTMLUploadDialogue"});
  ss.addMenu("Attach!", menuEntries);
}

function openHTMLUploadDialogue(e) {
  var html = HtmlService.createHtmlOutputFromFile('UploadForm');
  SpreadsheetApp.getUi().showModalDialog(html, 'Upload file');
  // This is printed
  Logger.log('dialog opened');
}

function upload(obj) {

  // This is never printed
  Logger.log('Obj received: ', obj.fname);

  //Retrieve the input data of the Form object.
  var newFileName = obj.fname;
  var rowNum = obj.position;
  var blob = obj.file;

  var upFile = DriveApp.getFolderById(getParentFolderID()).createFile(blob).setName(newFileName);
  var fileUrl = upFile.getUrl();

  var urlCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(rowNum,5);
  urlCell.setValue('=HYPERLINK("' + fileUrl + '","View file")');

}

function getParentFolderID(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var file = DriveApp.getFileById(ss.getId());
  var folderinDrive = file.getParents().next();
  Logger.log(folderinDrive.getName());

  return folderinDrive.getId();
}

UploadForm.HTML

<!DOCTYPE html>
<html>
  <head>
    <base target="_center">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
        <script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
  </head>
  <body>
  <form id="myForm">
      Please upload image below.<br /><br />
    <input type="hidden" name="fname" id="fname" value="<?= fName ?>"/>
    <input type="hidden" name="position" id="position" value="<?= position ?>"/>
    <input type="file" name="file" id="file" accept="image/jpeg,.pdf" />
    <input type="button" value="Submit" class="action" onclick="formData(this.parentNode)" />
    <input type="button" value="Close" onclick="google.script.host.close()" />
  </form>
  <script>
  //Disable the default submit action  using “func1”
   window.onload=func1;
   function func1() {
      document.getElementById('myForm').addEventListener('submit', function(event) {
        console.log('Submit pressed!');
            event.preventDefault();
          });  
   }

   function formData(obj){
     // This line is printed
      console.log('Received obj: ', obj);
      // This line always fails
      google.script.run
      .withSuccessHandler(closeIt)
      .withFailureHandler(onFailure)
      .upload(obj);
   } 

   function onFailure(e){
      console.log(e.name);
      console.log(e.message);
   } 

  function closeIt(e){
      console.log(e);
      google.script.host.close();
  };   

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

The output of the Sheet inspector console is:

Fo {message: "There was an error during the transport or process…this request. Error code = 10, Path = /wardeninit", name: "TransportError", stack: "TransportError: There was an error during the tran…4020103-warden_bin_i18n_warden__en_gb.js:195:263)"}
userCodeAppPanel:13 Received obj:  <form id=​"myForm">​…​</form>​
4094020103-warden_bin_i18n_warden__en_gb.js:89 Net state changed from IDLE to BUSY
4094020103-warden_bin_i18n_warden__en_gb.js:89 Net state changed from BUSY to IDLE
4094020103-warden_bin_i18n_warden__en_gb.js:89 Net state changed from IDLE to BUSY
4094020103-warden_bin_i18n_warden__en_gb.js:89 Net state changed from BUSY to IDLE
userCodeAppPanel:22 ScriptError
userCodeAppPanel:23 We're sorry, a server error occurred. Please wait a bit and try again.

And the output of the Google Script console only shows Opened spreadsheet and dialog opened.

The Sheets and Drive services are enabled, it seems like it never manages to return to the Code.gs upload function.

Tim Windsor Brown
  • 4,069
  • 5
  • 25
  • 33
  • The "There was an error during the transport..." appears always, so you can ignore that. What is logged in the Google Apps Script execution page? Have you tried using Chrome in incognito mode with all the extensions disabled and signing-in in a single account? – Rubén May 03 '21 at 21:54
  • It looks that you forgot to add `preventDefault()` to your form. – Rubén May 03 '21 at 21:56
  • I think [this answer](https://stackoverflow.com/a/60748334/7215091) will help you to solve your problem – Cooper May 04 '21 at 02:37
  • @Rubén thanks, I've run it in incognito added the execution page output, no change in output. Also, the `preventDefault()` in `func1` is supposed to be doing that, but clearly isn't firing. What is a good way of adding `preventDefault()` to the form? – Tim Windsor Brown May 06 '21 at 08:20

0 Answers0