0

I've been looking for a method that can import a csv into a google sheet with basic authentication. So far all the solutions online are outdated.

I followed the method found here: https://gist.github.com/jmodjeska/b0af2372c75c903700aeca4afb1fd56f

Which basically does this:

    // 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) }, muteHttpExceptions:true});
  var csvContent = parseCsvResponse(resp.getContentText(), ignoreHeaders);
  return csvContent;  
}

I then call the method by dropping this in cell A1:

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

The error I get in the spreadsheet when pasting the formula is: The server does not support the functionality needed to fulfill this request.</u></p><hr class="line"><h3>Apache Tomcat/8.5.11

Update2: Per a user comment I am attempting to hard code the username, password and URL in the actual populateSheetWithCSV method (first portion of the code stays unchanged):

    // Main function: retrieve the CSV and poppulate the data in-place
function populateSheetWithCSV(foo, ignoreHeaders) {
  var USERNAME = PropertiesService.getScriptProperties().getProperty('test');
  var PASSWORD = PropertiesService.getScriptProperties().getProperty('test');
 var url = PropertiesService.getScriptProperties().getProperty('https://csv-url-redacted');///////
  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };

  var params = {
    "method":"GET",
    "headers":headers
  };
  var resp = UrlFetchApp.fetch(url, params);
  var csvContent = parseCsvResponse(resp.getContentText(), ignoreHeaders);
  return csvContent;  

}

I'm getting the error: Attribute provided with no value: url (line 33). using this second method. Line 33 is the " var resp = ..."

Anton Cardan
  • 3
  • 3
  • 1
    Fetch only takes two parameters and you are passing it three. can you move your mute clause into the the same object with headers? – J. G. Sep 23 '19 at 16:46
  • @J.G. Ah, I see that mistake. Updated. Still cannot get the csv. The error is now "the server does not support the functionality needed to fulfill this request" – Anton Cardan Sep 23 '19 at 19:25
  • 1
    What's `base64pw`? – TheMaster Sep 23 '19 at 20:45
  • 1
    Have you tried the code for basic authentication as suggested here: https://stackoverflow.com/questions/23546255/how-to-use-urlfetchapp-with-credentials-google-scripts – Jack Brown Sep 23 '19 at 21:04
  • @JackBrown Thanks for the suggestion. You mean hard code the values in the script? Please check the Update2 because I'm still having issues with that, although I believe I'm missing something – Anton Cardan Sep 23 '19 at 22:39

1 Answers1

0

Try the following code:

function populateSheetWithCSV(foo, ignoreHeaders) {
  var USERNAME = 'YOURUSERNAME';
  var PASSWORD = 'YOURPASSWORD';
 var url = 'https://csv-url-redacted';
  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };

  var params = {
    "method":"GET",
    "headers":headers
  };
  var resp = UrlFetchApp.fetch(url, params);
  var csvContent = parseCsvResponse(resp.getContentText(), ignoreHeaders);
  return csvContent;  

}

Annotation: If you desire to use Script properties as here, keep in mind that script properties are key value pairs and you have to set a value before you can retrieve it.

Sample:

PropertiesService.getScriptProperties().setProperty('URL', 'https://csv-url-redacted');
...
var url = PropertiesService.getScriptProperties().getProperty('URL');
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Thank you, this worked. What's the correct way to split the csv using Pipe | instead of comma , ? Changing the comma here into a pipe doesn't do anything. retArray.push(line.split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/)); – Anton Cardan Sep 24 '19 at 13:50
  • Glad it helped. If you use Apps Script method `parseCsv(csv, delimiter)`, you can specify the delimiter: https://developers.google.com/apps-script/reference/utilities/utilities#parsecsvcsv,-delimiter – ziganotschka Sep 24 '19 at 14:19