When importData isn’t good enough: Retrieving CSV files behind basic auth with a Google Apps Script

(This post assumes you have some familiarity with Google Spreadsheets)

For CSV files that are unprotected, Google Spreadsheets provides a really handy function: importData(url). There's a short description of it in the list of Google Spreadsheet functions. If you just use this lovely function in cell A1 and pass it the URL of a CSV file, you’ll find that your spreadsheet is semi-instantly filled with the data from your CSV. It’s totally awesome! Unfortunately, it won't work if your CSV is protected by Basic Auth.

Chrome’s Basic Auth login prompt

That’s right: in some dark corners of the Internet, people still protect their data using Basic Authentication. Since it’s still being used, at some point in the future you might find yourself needing to import a CSV protected by Basic Auth into a Google Spreadsheet. If this future sounds like yours, you’ve come to the right place!

For background, Basic Authentication is just that: basic. Wikipedia has a pretty good write-up of Basic Authentication, but in general your perform authorization by including a header with the request that looks something like:

Authorization: Basic cm9iZXJ0Om9sZHRpcmVz

Let’s break that down:

• Authorization -- this the header name
• Basic -- hopefully self-explanatory - we’re asking the server for basic authentication
• cm9iZXJ0Om9sZHRpcmVz -- this is a base 64-encoded string of the form <username>:<password>. In this case, the value represents the username 'robert' and the password 'oldtires', so the string prior to encoding is: robert:oldtires

Since we presumably have the URL of the CSV already, all we need to do is find some way to include the Authorization header in the request to retrieve it, and we're golden. You can't do it with importData, but you can do it with a relatively straightforward Google Apps script.

If you open up your spreadsheet and click Tools -> Script Editor, you’ll be taken to a Javascript “IDE” where you can write a little code to manipulate your spreadsheet. To retrieve a CSV via basic authentication, you might use code that looks something like this:

// 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);
}
The workhorse is populateSheetWithCSV(sheet, csvUrl, user, pw). It takes in the sheet you want to fill, the url of the CSV file and the username and password for basic authentication. It then calls a function that base 64-encodes the username and password, and uses Google’s UrlFetchApp.fetch function to retrieve the CSV and includes the Authorization header that makes Basic Auth work. It then parses the CSV and sets that data in the given sheet. And that’s all there is to it (except for actually calling it).
If you wanted to import this CSV on a timer, I’d suggest setting up a new function that calls populateSheetWithCSV and passes it the appropriate arguments. You can find more information about setting up a function to run on a trigger over in the Google Apps Script Docs. If you're interested in filling the spreadsheet on-demand, you might want to check out the section on creating a button in Google Spreadsheetsin the Google Apps Script developer docs.
Enjoy!
PS: there are a couple caveats around the parseCsvResponse function. Namely, we knew in advance that our CSV file was going to be in a sort of simplified format -- it won’t be sparse, and there won’t be any commas in any of the column data, so it’s safe to split on them without worrying about accidentally splitting the data in the columns. Feel free to use the function however you want, but just know that it might require a little hardening to make it support a more generic format of CSV.