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

google_csv.js

/** Import CSV with basic auth

  * https://modjeska.us/csv-google-sheets-basic-auth/

  * Synopsis:
    populateSheetWithCSV(foo, csvUrl, base64pw, ignoreHeaders, bustCache)

  * Usage:
    =populateSheetWithCSV("Any Value", "https://csv-url", "base64pw", TRUE, TRUE)

  * Variables:

  * foo:            Any value. Google Sheets doesn't always recalculate your formula. 
                    To force it, you can manually or formulaically change this value,
                    which will trigger a fresh fetch of the CSV data from the source.

  * csvUrl:         Location of the CSV.

  * base64pw:       Pre-encoded base-64 password. You can use something like 
                    https://www.base64encode.net/ to get the encoded version of
                    username:password.

  * ignoreHeaders:  If TRUE, doesn't return the first row of the CSV.

  * bustCache:      If TRUE, appends a random token to the CSV URL to traverse
                    a cache on the host serving your CSV. 

**/

// Helper function: generate a random number for a cache busting token 
function cacheBust() {
  return Math.floor((Math.random() * 100000) + 1);
}

// Helper function: parse the CSV response
function parseCsvResponse(csvString, ignoreHeaders) {
  var retArray = [];
  var strLines = csvString.split(/\n/g);
  startLine = ignoreHeaders ? 1 : 0;
  for (var i = startLine; i < strLines.length; i++) {
    var line = strLines[i];
    if (line != '') {
      retArray.push(line.split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/));
    }
  }
  return retArray;
}

// Main function: retrieve the CSV and poppulate the data in-place
function populateSheetWithCSV(foo, csvUrl, base64pw, ignoreHeaders, bustCache) {
  var url = cacheBust ? csvUrl .concat(cacheBust()) : csvUrl;
  var resp = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Basic '.concat(base64pw)
    }
  });
  var csvContent = parseCsvResponse(resp.getContentText(), ignoreHeaders);
  return csvContent;  
}

after some trail and error and with the help of my dear friend Lars I found a solution to correctly handle the double quotes in the csv. Feel free to include it in your version if you like it.

On top of that I implemented context help for the function and made an optional change that enables you to supply the unencrypted string

/**
Import CSV with basic auth
  * https://modjeska.us/csv-google-sheets-basic-auth/
  * Synopsis:
    populateSheetWithCSV(foo, csvUrl, base64pw, ignoreHeaders, bustCache)
  * Usage:
    =populateSheetWithCSV("Any Value", "https://csv-url", "base64pw", TRUE, TRUE)
  * Variables:
  * foo:            Any value. Google Sheets doesn't always recalculate your formula. 
                    To force it, you can manually or formulaically change this value,
                    which will trigger a fresh fetch of the CSV data from the source.
  * csvUrl:         Location of the CSV.
  * base64pw:       Pre-encoded base-64 password. You can use something like 
                    https://www.base64encode.net/ to get the encoded version of
                    username:password.
  * ignoreHeaders:  If TRUE, doesn't return the first row of the CSV.
  * bustCache:      If TRUE, appends a random token to the CSV URL to traverse
                    a cache on the host serving your CSV. 

*/

// Helper function: generate a random number for a cache busting token 
function cacheBust() {
  return Math.floor((Math.random() * 100000) + 1);
}

// Helper function: parse the CSV response
function parseCsvResponse(csvString, ignoreHeaders) {
  var retArray = [];
  var row = [];
  var strLines = csvString.split(/\n/g);
  startLine = ignoreHeaders ? 1 : 0;
  for (var i = startLine; i < strLines.length; i++) {
    var line = strLines[i];
    if (line != '') {
      row = line.split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/)
      for (var j = 0; j < row.length; j++) {
        row[j] = row[j].replace(/^"(.*(?="$))"$/, '$1'); //remove double quotes at the start and the end
        row[j] = row[j].replace(/""/g, /"/); //unescape escaped double quotes
      }
      retArray.push(row);                                                                          
    }
  }
  return retArray;
}

// Main function: retrieve the CSV and poppulate the data in-place

/**
Import CSV with basic auth
@param {1} foo Any value. Change it to force an update of the CSV
@param {"toggl.com/reports/api/v2/details.csv?rounding=Off&status=active&calculate=time"} csvUrl Location of the CSV
@param {"dXNlcm5hbWU6cGFzc3dvcmQ="} base64pw Pre-encoded base-64 username:password. Get it from https://www.base64encode.net/ or a similar website
@param {FALSE} ignoreHeaders If TRUE, doesn't return the first row of the CSV.
@param {FALSE} bustCache If TRUE, appends a random token to the CSV URL to traverse a cache on the host serving your CSV.
@customfunction*/
function populateSheetWithCSV(foo, csvUrl, base64pw, ignoreHeaders, bustCache) {
  //var base64pw = Utilities.base64Encode(base64pw); //uncomment this line if you want to supply the unencrypted 'user:pass' string 
  var url = cacheBust ? csvUrl .concat(cacheBust()) : csvUrl;
  var resp = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Basic '.concat(base64pw)
    }
  });
  var csvContent = parseCsvResponse(resp.getContentText(), ignoreHeaders);
  return csvContent;  
}

Hi.
The works works well for me, but is it possible to:

1. Add a sort by asc or DESC option?
2. Remove/Replace any linebreaks when importing the csv file?

Thanks