Import CSV data from authenticated URL into Google Sheets

Other Questions Google Sheets
1

I’m trying to import CSV data from a URL that needs basic authentication into my Google Sheets document.

I found some code online that should work but I’m having trouble with it. The code seems outdated and has some bugs. For example, I think clear.contents() should actually be clear.content() but I’m not completely sure.

Even when I hardcode the sheet data, I still can’t get it working properly. Has anyone successfully done this before?

Here’s the code I’m working with:

// function to parse CSV data without handling comma-separated fields
// removes all quotation marks from the data
function convertCsvToArray(csvData) {
    var resultArray = [];
    
    var dataRows = csvData.split(/\n/g);
    var totalRows = dataRows.length;
    for (var j = 0; j < totalRows; j++) {
        var currentRow = dataRows[j];
        if (currentRow != '') {
            resultArray.push(currentRow.replace(/"/g, "").split(/,/));
        }
    }
    
    return resultArray;
}

function importCsvToSheet(targetSheet, csvEndpoint, username, password) {
    
    // fetch the CSV data
    var response = UrlFetchApp.fetch(csvEndpoint, {
        headers: {
            // basic authentication header
            'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + password, Utilities.Charset.UTF_8)
        }
    });
    
    // convert response to array format
    var parsedData = convertCsvToArray(response.getContentText());
    
    // clear existing data and formatting
    targetSheet.clearContents().clearFormats();
    
    // populate sheet with new data
    targetSheet.getRange(1, 1, parsedData.length, parsedData[0].length).setValues(parsedData);
    
}
2

Had this exact problem last year pulling sales data from our CRM. Your auth looks right, but you need error handling to see what’s going wrong. Wrap your UrlFetchApp.fetch in a try-catch and log the response code plus any errors. For me, the server wanted a specific User-Agent header on top of basic auth. Add ‘User-Agent’: ‘GoogleAppsScript’ to your headers object. Also log response.getContentText() before parsing to check if you’re actually getting CSV data. Sometimes auth works but you get back an HTML error page instead. One last thing - empty rows at the end of your CSV will create arrays with different lengths and break your setValues call.

3

The code looks mostly right, but there are a few issues. First, clearContents() and clearFormats() are separate calls, so that part’s fine. The main problem is your CSV parsing function - it’s too basic and will break if your CSV has quoted fields with commas in them. I’ve hit similar auth issues with protected endpoints. Double-check your credentials and make sure the server actually supports basic auth. Test the auth separately by logging the response status code first. Also, some servers don’t like the charset parameter in base64Encode - try removing the Utilities.Charset.UTF_8 part and see if that helps. Another thing: check if your CSV has consistent column counts across all rows. The getRange call assumes every row has the same number of columns as the first row, so it’ll throw an error if that’s not true.

4

Hit this exact issue yesterday with an API endpoint. Your code looks fine, but try adding Utilities.sleep(1000) before the fetch call - some auth servers don’t like immediate requests. Also check if the URL redirects after login, that completely broke mine. And double-check your sheet reference when calling the function.