Import gzipped data into Google Sheets

4
votes

I am trying to fetch a gzip from a URL and import it automatically into a Google Sheets. The gzip contains one file of CSV data.

I know I can import the CSV data into a Google Sheets but I would like to be able to cut out the step of me having to download the gzip and extract the file first before uploading it into a Google Sheets.

So my queries:

Is it possible to import a gzipped CSV file from a URL directly into a Google Sheets?

If not, how would this be done with a Google Apps Script?

2
google-apps-scriptgoogle-sheetsgzipimport-from-csv
It's reported on the issue tracker code.google.com/p/google-apps-script-issues/issues/… – Eduardo
thanks for the info Eduardo, I didn't catch this one ;-) so I guess my suggestion won't give any result ... too bad... – Serge insas

2 Answers

5
votes

I was able to inflate (decompress) a gzipped file using a third party javascript library, pako. Here is the code:

  eval(UrlFetchApp.fetch('https://cdn.rawgit.com/nodeca/pako/master/dist/pako.js').getContentText());

  var charData = UrlFetchApp.fetch("<url to a .gz file>").getContent();

  var binData = [];
  for (var i = 0; i < charData.length; i++) {
    binData.push(charData[i] < 0 ? charData[i] + 256 : charData[i]);
  }

  var data = pako.inflate(binData);

  var decoded = '';
  for (var i = 0; i < data.length; i++) {
    decoded += String.fromCharCode(data[i]);
  }
4
votes

There is an undocumented utility in GAS called unzip, I know it can unzip ordinary .zip files but have no idea if GNU zip are supported... maybe it's worth give it a try ? here is a quick test I wrote for a zip that was in my google documents/drive

function testzip(){
var files=DocsList.getRootFolder().find('Sans titre.txt.zip');
var zipblob=files[0].getBlob();
var unzipblob = Utilities.unzip(zipblob);
var unzipstr=unzipblob[0].getDataAsString();// it is a text file
DocsList.createFile('Sans titre.txt',unzipstr);// I kept the original name to make it simple
}

Don't blame me if it doesn't work for you... it's just a proposal ;-)