Upload CSV/Excel File as Sheet via Google App Script

Question

I see that the Drive Folder class has a createFile() which takes three arguments for name, content, and mimeType. Is it possible to use this as part of an upload call to have a user file uploaded and converted (to Google Docs) all in one go without having to call the REST API directly with convert=true?

For example, here's the HTML:

<html>
  <head>
    <base target="_top">
  <script>
  function handler(response) {
    document.getElementById('uploader').innerHTML = "Uploaded file! " + response;
  }
  </script>

</head>
  <body>
  <div id="uploader">
  <form>
   <input type="file" name="theFile">
   <input type="button" onclick="google.script.run.withSuccessHandler(handler).uploadFile(this.parentNode)" value="Upload!">
  </form></div>
</body>
</html>

And here's the Google Script code:

function uploadFile(e) {
  Logger.log("Uploading file!");
  var dfolder = DriveApp.getFolderById('abcdefgh');  // replace w/ Drive FolderID
  return dfolder.createFile(e.theFile).getName();
}

How would I go about changing that last line to something like:

return dfolder.createFile(newName, e.theFile, MimeType.GOOGLE_SHEETS);

Something I'm trying to figure out now is simply how to get the name of the file being uploaded (e.g. for newName). And then how to go about converting whatever form e.theFile is in to a string. If I try this as-is now I get the error:

Invalid argument: file.contentType at uploadFile(Code:31)

score 2 · Accepted Answer · answered Jul 08 '19 at 22:36

You want to upload CSV and Excel files using google.script.run.
When the file is uploaded, you want to convert to Google Spreadsheet.
You want to retrieve the filename of the uploaded file.

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

I think that in your script, it is required to modify the Google Apps Script.

Modification points:

Value of e.theFile of uploadFile(e) is the blob. So you can retrieve the filename using getName().
You can convert the CSV and Excel format to Google Spreadsheet using Drive API. In this case, I used Drive API v2 at Advanced Google Services.
The reason of the error of Invalid argument: file.contentType at uploadFile is that the mimeType of MimeType.GOOGLE_SHEETS cannot be used with createFile().

Modified script:

Please modify uploadFile() as follows. Before you use this script, please enable Drive API at Advanced Google Services, and set the folder ID to folderId.

function uploadFile(e) {
  Logger.log("Uploading file!");

  // I modified below script.
  var folderId = "###"; // Please set the folder ID here.
  var blob = e.theFile;
  var filename = blob.getName();
  var mimeType = blob.getContentType();
  if (mimeType == MimeType.CSV || mimeType == MimeType.MICROSOFT_EXCEL || mimeType == MimeType.MICROSOFT_EXCEL_LEGACY) {
    return Drive.Files.insert({title: filename, mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: folderId}]}, blob).title;
  }
  return "";
}

References:

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

Thanks, this is not what I expected but it doesn't require calling the REST API directly so I would say this answers my question. — Neil C. Obremski, Jul 09 '19 at 15:34
@Neil C. Obremski Thank you for replying. I'm glad your issue was resolved. I thought that I have to study more. Thank you, too. — Tanaike, Jul 09 '19 at 22:40
Ah ... I see this _is_ calling the REST API directly. This must mean that there is no way to do what I'm asking through the Google App Script classes — Neil C. Obremski, Jul 10 '19 at 16:03
I replaced the `Drive.Files.insert` call in your example w/ a call to `convertExcel2Sheets(blob, filename, mimeType, [folderId]).getName();` from https://gist.github.com/azadisaryev/ab57e95096203edc2741 (I added a mimeType parameter to the function) — Neil C. Obremski, Jul 10 '19 at 16:28
@Neil C. Obremski Thank you for your additional information. — Tanaike, Jul 10 '19 at 22:28

Upload CSV/Excel File as Sheet via Google App Script

1 Answers1

Modification points:

Modified script:

References: