1

I have as series of Google Apps Scripts that seem to work well with converting .xlsx attachments from my Gmail and saving them as Google Sheets in my Drive.

Unfortunately, I'm not having luck duplicating that process to an .xlsx file that I'm trying to download from a specific URL.

Here's the code:

// Fetch the URL and convert it to a blob
var url = ** URL with the file **;
var response = UrlFetchApp.fetch(url);
var uniqueFeed = Utilities.newBlob(response, "application/vnd.ms-excel", "Unique.xlsx");
var fileToImport = DriveApp.createFile(uniqueFeed);

That DOES create the file in my Drive. However, it's corrupt. When I console.log the blob as a string, I get a bunch of random junk (for example):

[20-06-29 15:35:49:776 CDT] PK�����!�v��������[Content_Types].xml �(������������������������������������������������������������������������������������������������������������������������U�N�0�#����q�!Ԕ�$�L�M�:���B���MA�C��$�cg�c{2�^t����ƻJ��CQ���6�������RH�ie��J,����d���W;�DK��ĺ�Na�8���)�fldP�L5 G�ᅬ

My code to convert the .xlsx file then fails, I'm ASSuming because the newly created file is no good. I also don't get why it mentions .xml. The URL and the file are definitely .xlsx.

Just to clarify one thing: If I manually download the file from the URL to my computer, and then drop it into my Drive, I CAN convert it to a Google Sheet without issue. I'm trying to automate that manual step out... hence the problem.

Any insights would be much appreciated. I can PM the URL upon request -- just don't want it floating around the interwebs.

KevinNeedsHelp
  • 13
  • 2
  • 1
    Does this answer your question? [Script to convert .XLSX to Google Sheet and move converted file](https://stackoverflow.com/questions/56063156/script-to-convert-xlsx-to-google-sheet-and-move-converted-file) – Kos Jun 29 '20 at 21:10
  • Thank you, but no. That appears to be to convert EXISTING .xlsx Drive files, and I'm trying to download a .xlsx file from a URL to blob. – KevinNeedsHelp Jun 29 '20 at 22:46

1 Answers1

1

I believe your goal and situation as follows.

  • You want to download a XLSX file from the URL and save it to your Google Drive using Google Apps Script.
  • Your URL is the direct link of the XLSX file.

For this, how about this answer?

Modification point:

  • UrlFetchApp.fetch(url) returns the object of HTTPResponse. In this case, you can directly retrieve the file blob using getBlob() from the object of HTTPResponse.

When this point is reflected to your script, it becomes as follows.

Modified script:

From: 
var response = UrlFetchApp.fetch(url);
var uniqueFeed = Utilities.newBlob(response, "application/vnd.ms-excel", "Unique.xlsx");
To: 
var uniqueFeed = UrlFetchApp.fetch(url).getBlob().setName("Unique.xlsx");

Note:

  • If your URL doesn't directly return the XLSX file, this modification cannot be used. So please be careful this.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165