Fetch a Remote CSV in Google Sheets with Basic Auth
This is a Google Apps Script custom function that facilitates importing a remote CSV file into your Google Sheets document. It provides for basic HTTP auth traversal. This is based upon an older solution described on the Redfin engineering blog, which no longer works (probably due to changes over time in Google Sheets functionality).
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; | |
| } |