title: Pull csv to Google Sheets with basic authentication
body: <p>I need to pull a csv from a URL that requires basic auth.</p>
  
  <p>I have found this code that works, apart from parsing the csv, clearing and setting the cells. I think there is a few bugs as it is old code, such as <code>clear.contents()</code> should be <code>clear.content()</code>.</p>
  
  <p>And even with hard coding the data in where sheets is, im still struggling to get it to work, has anyone else found a solution?:</p>
  
  <pre><code>// this function assumes the CSV has no fields with commas,
  // and strips out all the double quotes
  function parseCsvResponse(csvString) {
      var retArray = [];
  
      var strLines = csvString.split(/\n/g);
      var strLineLen = strLines.length;
      for (var i = 0; i &amp;lt; strLineLen; i++) {
          var line = strLines[i];
          if (line != '') {
              retArray.push(line.replace(/"/g, "").split(/,/));
          }
      }
  
      return retArray;
  }
  
  function populateSheetWithCSV(sheet, csvUrl, user, pw) {
  
      // request the CSV!
      var resp = UrlFetchApp.fetch(csvUrl, {
          headers: {
              // use basic auth
              'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)
          }
      });
  
      // parse the response as a CSV
      var csvContent = parseCsvResponse(resp.getContentText());
  
      // clear everything in the sheet
      sheet.clearContents().clearFormats();
  
      // set the values in the sheet (as efficiently as we know how)
      sheet.getRange(1, 1, csvContent.length /* rows */, csvContent[0].length /* columns */).setValues(csvContent);
  
  }
  </code></pre>