I am currently using Google Apps Script to fetch CSV data from a webpage using basic authentication and insert it into a spreadsheet. Below is the code snippet I am working with:
CSVImport.gs
function parseCSVtoSheet(sheetName, url)
{
// Credentials
var username = "myusername";
var password = "mypassword";
var header = "Basic " + Utilities.base64Encode(username + ":" + password);
// Setting the authorization header for basic HTTP authentication
var options = {
"headers": {
"Authorization": header
}
};
// Getting the ID of the sheet with the name passed as parameter
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
// Fetching the CSV data and inserting it into the spreadsheet
var csvContent = UrlFetchApp.fetch(url, options).getContentText();
var resource = {requests: [{pasteData: {data: csvContent, coordinate: {sheetId: sheetId}, delimiter: ","}}]};
Sheets.Spreadsheets.batchUpdate(resource, spreadsheet.getId());
}
Lately, I have been experiencing an issue where sporadically I receive this error on the line that contains UrlFetchApp.fetch:
Exception: Unexpected error: http://www.myurl.com/data/myfile.csv (line 21, file "CSVImport")I have attempted the following troubleshooting steps:
- Embedding the credentials directly in the URL instead of using an Authorization header (which resulted in a different error message stating "Login information disallowed").
- Encoding the credentials to base64 when passing them into the headers object (this approach did not resolve the issue).
- Removing authentication entirely (resulted in a 401 response from the HTTP page).
It is puzzling why this problem has suddenly emerged and I am unsure about what other techniques to try. Any guidance would be greatly appreciated.