Skip to content

Instantly share code, notes, and snippets.

@jmodjeska
Forked from roblg/gist:2400902
Last active July 25, 2023 10:03
Show Gist options
  • Save jmodjeska/b0af2372c75c903700aeca4afb1fd56f to your computer and use it in GitHub Desktop.
Save jmodjeska/b0af2372c75c903700aeca4afb1fd56f to your computer and use it in GitHub Desktop.
retrieving a basic auth-protected CSV with Google Spreadsheets and Google App Scripting
/** 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;
}
@TMac1112
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment