Import zip data into google spreadsheet

Question

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

The zip 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 zip and extract the file first before uploading it into a Google Spreadsheet.

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

Answer 1

To answer your question, no, you cannot directly import a Zip file containing a CSV directly into a spreadsheet. To answer your second question:

This question is kind of broad and needs to be broken down into three bits.

1. Retrieving the Zip from the URL
2. Extracting the CSV from the Zip
3. Inserting the CSV into your sheet

I'll briefly cover each of these three areas to get you going in the right direction, I am not going to write out a full fledged solution for you. I provide some code examples to make it easier for you to get going, this is not meant to be an end-to-end solution.

Retrieving the Zip from the URL

You can do this with the URLFetchApp Service.

Something like:

var urlData = UrlFetchApp.fetch(link);
var zipBlob = urlData.getBlob();
var files = Utilities.unzip(blob);

Extracting the CSV from the Zip

You need to get the contents of the ZIP file, find the CSV file in the ZIP, then parse it as a CSV into an array . In my example I use regex to escape the CSV as the Apps Script CSV parser is buggy.

function GetCSVFromZip(zipBlob){
  var files = Utilities.unzip(zipBlob);
  var csvAttachment = FindBlobByName(files, 'myPartialName');
  if(csvAttachment !== -1){
    var dataString = csvAttachment.getDataAsString();
    var escapedString = dataString.replace(/(?=["'])(?:"[^"\\]*(?:\\[\s\S][^"\\]*)*"|'[^'\\]\r\n(?:\\[\s\S][^'\\]\r\n)*')/g, '\r\n'); //http://stackoverflow.com/a/29452781/3547347
    var csv = Utilities.parseCsv(escapedString);
  }
}

//Finds a blob by a partial name match, assumes no multiple matches
function FindBlobByName(blob, name){
  for(var i = 0; i < blob.length; i++){
    var blobName = blob[i].getName();
    var regex = new RegExp(name, 'i');
    var result = blobName.match(regex);
    if(result){
      return blob[i];
    }    
  }
  return -1;
}

Inserting the CSV into your sheet

You need to use the SpreadsheetApp Service for this. Get your spreadsheet, get a data range, and set it's values to your CSV array. Something along these lines:

var sheet = SpreadsheetApp.openById(id).getSheetByName(name);
var range = sheet.getRange(1, 1, csv.length, csv[0].length);
range.setValues(csv);

Answer 2

Thanks Douglas for your inputs. With the help of your code i could manage to simplify it and read the zip from the link directly to spreadsheets. Here is my code:

function testzip(){

var url = "url goes here" 
var zipblob = UrlFetchApp.fetch(url).getBlob(); 
var unzipblob = Utilities.unzip(zipblob); 
var unzipstr=unzipblob[0].getDataAsString();
var csv = Utilities.parseCsv(unzipstr);

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
ss.getRange(1, 1, csv.length, csv[0].length).setValues(csv);

}