Get CSV in ZIP from a site with Basic Auth using Google Apps Script (google sheets)

0

I'm trying to get a csv that is zipped in a url, which needs basic auth. I can not achieve it, since it throws a syntax error in the argument of the variable 'files'. This is what I tried:

function importdatastreamcsv() {

  var user = 'user';
  var pw = 'pass';

  var csvUrl = 'https://example.com/api/datastream/download/58350?tableIds=67595&usePreferences=false';
  var csvContent = UrlFetchApp.fetch(csvUrl,{headers: {'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)}});
  var files = Utilities.unzip(csvContent)[0]; //error de sintaxis en el argumento! :(
  var csvData = Utilities.parseCsv(files.getDataAsString());


  var sheet = SpreadsheetApp.getActiveSheet().getActiveCell();
  sheet.setValues(csvData);


}

To make sure I tried other sample files on the web and I was able to do it correctly, as I show here:

function importdatastreamcsv() {

  var user = 'x';
  var pw = 'x';
  var csvUrl = 'http://spatialkeydocs.s3.amazonaws.com/FL_insurance_sample.csv.zip'  
  var csvContent = UrlFetchApp.fetch(csvUrl)//,{headers: {'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)}});
  Logger.log(typeof(csvContent));
  var files = Utilities.unzip(csvContent)[0];
  var csvData = Utilities.parseCsv(files.getDataAsString());
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

   
}

Looking for other alternatives, I realized that I could bring the same data in html format (without zip-csv). I wrote another script for this, which works partially, since I receive all the data in a single cell and in flat html format. But it should be in several cells table format ... here goes the code:

function importdatastreamhtml() {

  var user = 'user';
  var pw = 'pass';
  var csvUrl = 'https://example.com/api/datastream/download/58350?tableIds=67595&usePreferences=false&format=web'; 
  var csvContent = UrlFetchApp.fetch(csvUrl,{headers: {'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)}});
  var html = csvContent.getContentText();

  var sheet = SpreadsheetApp.getActiveSheet().getRange(1, 1);
  sheet.setValue(html);


}

I need at least one of the two codes to work for me, better if it is first. What can I be missing? The final result should be a simple table.

    
asked by César Requena 17.11.2017 в 10:45
source

0 answers