retrieving a basic auth-protected CSV with Google Spreadsheets and Google App Scripting

gistfile1.js

// this function assumes the CSV has no fields with commas,
// and strips out all the 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 we know how)
    sheet.getRange(1, 1, csvContent.length /* rows */, csvContent[0].length /* columns */).setValues(csvContent);

}

Thanks

Been messing around with this code for a while now, but do you have any idea as to how to fudge this code to pull an html table into a spreadsheet instead of a csv?

Thanks, very useful.
The csv I am using has '!' as a separator. I am assuming that if I replace ',' with '!' in line 11 this should work.
Will then try and build the sep as an argument within the function call so that I can use it in different scenarios.

Hi, I hope you can help.
I am not very familiar with gs but I copied your script and ran it but the error that I keep getting is:
TypeError: Cannot find function clearContents in object Sheet2. (line 32).

Please could you help. I tried to the run the script again by deleting that line and working with an empty sheet so that clearContents() isn't required. But then I get a similar error saying:

TypeError: Cannot find function getRange in object Sheet2. (line 35).

It looks like Google Sheets aren't recognising any of the inbuilt script functions. Please can you help?

Having the same issues as rachitkinger - any updates?

Thanks

I was able to get this working and add some additional functionality. See fork here. Hope this helps.