CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ilya
ilya

Reputation: 631

How to add file upload to spreadsheet as custom menu option?

I want to make a spreadsheet CMS - that is to read/write data from firebase and vice versa. I reached a point where I need to upload files directly from the spreadsheet and not any other page.

I have added a custom menu with a htmlService to output a template where the user may click and upload a file and that file must get handled in google script, but the problem is that I'm getting only the fake path of a file "c:/fakepath/avatar.png" and not a blob.

my files in google script:

upload.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body>
    <div>
        <div id="progress" ></div>

        <input type="file" name="upload" id="file">
        <input type="submit" value="Submit" class="action" onclick="form_data()" >
        <input type="button" value="Close" onclick="google.script.host.close()" />
    </div>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
    </script>
    <script>
      function form_data(){
        var values = [{
          "file":$("#upload").val(),
        }];

        google.script.run.withSuccessHandler(closeIt).upload(values);
      };

      function closeIt(){
        google.script.host.close()
      };
    </script>
  </body>
</html>

test.gs

function upload(values){
  //Display the values submitted from the dialog box in the Logger. 
  Logger.log(values); // here I'm getting file = c/fakepath/avatar.png while I 
       //need the file to send it as a post request or save it in google drive
};

I believe I should use FileReader but I have tried and failed:

      var file, 
        reader = new FileReader();

// Upload the file to Google Drive
  reader.onloadend = function(e) {
    google.script.run
      .upload(
         e.target.result, file.name
      );
  };
      function form_data(){
    reader.readAsDataURL(file);
}

custom menu file upload

Upvotes: 1

Views: 2192

Answers (1)

Tanaike
Tanaike

Reputation: 201408

  • You want to upload a file using a dialog box on Google Docs to Google Drive.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modified script:

Google Apps Script:

function upload(obj) {
  var file = DriveApp.createFile(obj.upload);
  return {
    fileId: file.getId(),
    mimeType: file.getMimeType(),
    fileName: file.getName(),
  };
}

HTML:

Please replace <body>...</body> as follows. In this modification, jquery is not used.

<body>
  <form> <!-- Modified -->
    <div id="progress" ></div>
    <input type="file" name="upload" id="file">
    <input type="button" value="Submit" class="action" onclick="form_data(this.parentNode)" >
    <input type="button" value="Close" onclick="google.script.host.close()" />
  </form>
  <script>
    function form_data(obj){ // Modified
      google.script.run.withSuccessHandler(closeIt).upload(obj);
    };
    function closeIt(e){ // Modified
      console.log(e);
      google.script.host.close();
    };
  </script>
</body>

Note:

  • When you uploaded a file, the file ID, mimeType and filename of the created file are returned. You can see them on the console.
  • In this method, because blob is used, the maximum file size is 50 MB. Please be careful this.

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 3

Related Questions