google-apps-scriptgoogle-sheetsurlfetchgoogle-data-api

URL fetch sometimes leads to "Uncaught ScriptError" resp. 404


My goal is to write an Add-on, which converts a Spreadsheet file into an XLSX-file and sends it to a given mail distribution list on a daily, weekly or monthly basis.

Everything works fine, except from the URL fetch. Please find my function below:

function startWizard () {
var addresses = SpreadsheetApp.getActive().getSheetByName("Einstellungen").getRange("A1").getValues();
var filename = SpreadsheetApp.getActiveSpreadsheet().getName();
var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + ssID + "&exportFormat=xlsx"; 
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer '+ token}});
Logger.log(url);
var contents = response.getContent();

MailApp.sendEmail(addresses,"subject" ,"body", {attachments:[{fileName:filename+".xlsx", content:contents, mimeType:"application//xlsx"}]});
}

Most of the time everything works fine and I receive an e-mail and the XLSX-file attached. Nevertheless, from time to time I get the following error message in my browser console (translated from German into English) and I do not receive any e-amil:

"Uncaught ScriptError: Error at the request for https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=1iBIny93-y1sSsGxwn78_c-_9EpjIPwMBGa1Ivn1DNKg&exportFormat=xlsx. The following code was responded: 404. Shortened server answer: "GOOGLE ANSWER IN HTML CODE" Use "muteHttpExceptions", to read the whole answer.

I have tried out other solutions but nothing worked so far. In addition I tried to search for an answer in this forum, but in vain.

It seems that the URL is correct, as I can download the file. My assumption is that there is something wrong with the oAuth.

Could somebody please give me a hint? It is driving me mad...

Thank you!


Solution

  • Thank you pointNclick for your help. Yes, I tried using muteHttpExceptions. The result is, that the XLSX-file gets exported and sent via mail, but the content of the file is the "GOOGLE ANSWER IN HTML CODE" = about google docs etc.

    In the meanwhile I have tried something else, which worked out for me and I hope I won't face new problems. I inserted "DriveApp.getRootFolder();" Here is the new code in case anyone has the same issue:

    function startWizard () {
      var addresses = SpreadsheetApp.getActive().getSheetByName("Einstellungen").getRange("A1").getValues();
      var filename = SpreadsheetApp.getActiveSpreadsheet().getName();
      var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
      var url ="https://docs.google.com/spreadsheets/d/"+ssID+"/export?format=xlsx&id="+ssID;
      DriveApp.getRootFolder();
      var token2 = ScriptApp.getOAuthToken();
      var response = UrlFetchApp.fetch(url, {
      headers: {'Authorization': 'Bearer ' +  token2}});
      var contents = response.getContent();
    
      MailApp.sendEmail(addresses,"subject" ,"body", {attachments:[{fileName:filename+".xlsx", content:contents, mimeType:"application//xlsx"}]});
    }
    

    As I spent a lot of time to figure this out and to get the OAuth up and running, I would like to mention, that I did not use any OAuth libraries and Drive APIs via Advanced Google Services / Google Developer Console. Everything which is needed is in the code. I hope this example will help some other developers.