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.