Looking to retrieve a CSV from a URL that requires basic authentication.
I have come across some code that works, but I am having trouble with parsing the CSV, as well as clearing and setting the cells. There are some bugs in the old code that need fixing, for example changing clear.contents() to clear.content().
Even after hard coding the data into the sheets, I am still struggling to make it function. Has anyone else found a solution?
// This function assumes the CSV has no fields with commas,
// and removes all double quotes
function parseCsvResponse(csvString) {
var retArray = [];
var strLines = csvString.split(/\n/g);
var strLineLen = strLines.length;
for (var i = 0; i < strLineLen; i++) {
var line = strLines[i];
if (line != '') {
retArray.push(line.replace(/"/g, "").split(/,/));
}
}
return retArray;
}
function populateSheetWithCSV(sheet, csvUrl, user, pw) {
// Request the CSV!
var resp = UrlFetchApp.fetch(csvUrl, {
headers: {
// Use basic auth
'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)
}
});
// Parse the response as a CSV
var csvContent = parseCsvResponse(resp.getContentText());
// Clear everything in the sheet
sheet.clearContents().clearFormats();
// Set the values in the sheet (as efficiently as possible)
sheet.getRange(1, 1, csvContent.length /* rows */, csvContent[0].length /* columns */).setValues(csvContent);
}