Import gzipped data into google spreadsheet

问题:

I am trying to fetch a gzip from a URL and import it automatically into a Google Spreadsheet.

The gzip contains one file of CSV data.

I know I can import the CSV data into a Google Spreadsheet 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 Spreadsheet.

So my queries, is it possible to import a gzipped CSV file from a URL directly into a Google Spreadsheet? If not, how would this be done with a Google Apps Script?

回答1:

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 ;-)

回答2:

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]);
  }