-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** 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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi.
The works works well for me, but is it possible to:
Thanks