Save Excel file from external URL in gmail message using Apps script

Question

I am new to Apps Script coding.

I receive an external link in my gmail messages from a particular website every week, that allows me to download an excel data extract (xlsx file). Initially when i click the link, it redirects to an external website login page, where i have to enter my username and password. Once done, it automatically downloads the file in Chrome.

What i want to do: Loop through the email messages and save the Excel data extract from URL into my Google Drive folder (or better Team Drive folder) by creating a folder by same name as the label. It would be preferable to save it in Google Spreadsheet format (If not, Excel xlsx format is ok with me).

I have created a Google Spreadsheet to document this Link, email Subject and Date received and preferably have a Status column as "Processed".

The link i receive in email is of this format:

https://XXXXX-XXX.XXXXXXX.com/XXXXXXXWeb/ReportDownloadServlet?outputInd=P&executionId=1234567

Initially when i click the link, it redirects to an external website login page where i enter username and password and click 'Remember Me':

https://abcdefg.hijklmn.com/gateway/logon.htm?TYPE=33619969&REALMOID=06-0008fcbc-145a-1be3-b1b7-05e50a27c0f4&GUID=&SMAUTHREASON=0&METHOD=GET&SMAGENTNAME=-SM-UmRXxeTjx0wukOHolEZ%2fuji7%2f9Iznin6POLHraH%2b4Pc30o%2fZFVkAWVfTNIh2ZF8V&TARGET=-SM-HTTPS%3a%2f%2fXXXXX--XXX%2XXXXXXX%2ecom%2fXXXXXXXWeb%2fReportDownloadServlet%3foutputInd%3dP%26executionId%3d1234567

In the Google Spreadsheet container Script editor, i have written the following code:

function myFunction() {
  var start = 0;
  var inc = 100;
  var k = 0;
  var ss = SpreadsheetApp.openById("*****-*********_****************************");
  var sheet = ss.getSheetByName("Sheet1");
  var label = GmailApp.getUserLabelByName("SOME_LABEL");
  var arr=[];
  
  do {    
//  var now = new Date();
  var threads = label.getThreads(start,inc);

  for (var i = 0; i<threads.length; i++)
  {
    var messages = threads[i].getMessages();

    for (var j = 0; j < messages.length; j++)
    {
      var msg = messages[j].getPlainBody().toString(); 
      
      var re1='.*?'; // Non-greedy match on filler
      var re2='((?:http|https)(?::\\/{2}[\\w]+)(?:[\\/|\\.]?)(?:[^\\s"]*))'; // HTTP URL 1
      var pattern = new RegExp(re1+re2,["i"]);
      var matches = pattern.exec(msg);
      var url = matches[1].replace(">","");
      
      var user = "USERNAME";
      var password = "PASSWORD";

      //var headers = {
      //              "Accept": "application/xml",
      //              "Content-Type": "application/xml",
      //              "Authorization": "Basic "+ Utilities.base64Encode(user+":"+password)
      //      };
      
      //defining method to download file
      //var options = {
      //  "method" : "get",
      //  "headers" : headers,
      //  "muteHttpExceptions" : true
      // };
      
      // var response = UrlFetchApp.fetch(url,options);
      var response = UrlFetchApp.fetch(url);
      var blob = response.getBlob();
      
      var folders = DriveApp.getFoldersByName('Team Drives');
      while (folders.hasNext()) {
        var folder = folders.next();
      }
      var subfolder = folder.createFolder("SOME_LABEL");
      var file = subfolder.createFile(response);

      var file = folder.createFile(response);

      
      var dat = messages[j].getDate();
 //      sheet.appendRow([k, msg, sub, dat]);
      k++;
//      arr[k-1] = [[k,msg,sub,dat]];
    }
//      threads[i].removeLabel(label);
  
  }
  start += inc;
  
}  while (threads.length == inc);
  sheet.insertRowsAfter(1, k);
}

I am able to loop through the emails in my Gmail folder (label) and extract the link properly. Now when i try to use the UrlFetchApp, i get the response as:

ReportDownloadServlet.html

which when opened is basically the login page, and not an Excel file.

I am having trouble identifying the Options and Headers (parameters) to pass to UrlFetchApp() method from Chrome Developer Extension (F12).

Can somebody help me in identifying the correct parameters?

Also, something i noticed from Chrome developer extension (Console log):

Resource interpreted as Document but transferred with MIME type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet: "https://XXXXX-XXX.XXXXXXX.com/XXXXXXXWeb/ReportDownloadServlet?outputInd=P&executionId=1234567".

and this i copied from Network > Selected the URL link > Clicked Headers tab.

Request URL:https://XXXXX-XXX.XXXXXXX.com/XXXXXXXWeb/ReportDownloadServlet?outputInd=P&executionId=1234567
Request Method:GET
Status Code:200 OK
Remote Address:123.123.123.123:443
Referrer Policy:no-referrer-when-downgrade


Response Headers
Connection:Keep-Alive
Content-Disposition:attachment; filename="SOME_FILENAME.xlsx";
Content-Encoding:gzip
Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Date:Wed, 21 Jun 2017 18:25:48 GMT
Keep-Alive:timeout=5, max=100
Server:Apache/2.2.21 (Unix)
SESSIONLOGID:56097422
Set-Cookie:XXXXX-XXX_cookie=R951123141; path=/; expires=Thu, 22-Jun-2017 07:20:48 GMT
Set-Cookie:SMSESSION=ZKpKqhPOkqIDQqlp1pbIamKby5gmAGnhwOgmHlckXTd3GWGrqlSG4ZHKyJW1JpRrg2TOR6WB9Q+8q8D4Fd/PwsNyCL6ItmxDJfMEF1BewbbL8mPouRLt8jgPLFMjt3De9J5WwIHhxXMI67ua3t+KSKvnqPk4G6LMjOOy3JcDKWz/J6l2nMXCrTq7g6aAxH5NkJWcliLLHLIrpBPz+v8XT729gnuf4yXCpe3frz+wLNcY+t0YDKcIduCtgEo0QuZ2cYqub6fenZxSbvg+Zhla1OCduU9+/Tb6CyM6R/hl+fJ1CT7pGcWM8MGlapS+xV2UCOe0q0I8syojlH3lTwn0jWHJ0aOF6jnUPkrIsvunFD9g2JdGasCg21O18Gn94pUbgpQX4YeAdIUmXo7HQ7K6mnbXOMhsmGhEGDv8c6YGU57mD0owxncKpm1XgZFW70Fo1vztxxQorP23ui8kpdyz75VhDeebYftyTv340Y/2APiVOG7ojXVq+2Tjcky9TgPdk1EVMWCeIiqjroWBG5IiCvbLol7Wl1JSKg2i/q+zJmzlyn4vo9db2wffBG/JnuY2qnd9urngzI9XGNPEugymRVfyi7y6W73Qdvedry3/Qd8IDh9LHZ1r4U5KfNW6vir5iewaVcVRfHJw6MYp2iLpvhIVvqvxZCDt9/7juhB6VmRVxjuyhEbGKXwz7DLkt5rzr3R/eY5L/ztJis9ZNhtU8wzQHg2S6F0AU0AyzIHKSRKVBy+MtOSbPgb8B9nRQYt04BCI/Kb3ehIzISbg9Tm26Q4v5J1EWo5rBsbNefOfpFuqM4Y6uN0X1Hq2BHlAQGtyB6n3A3Bekb8mhcjmDeotWj9MPpSV4qNhGgtLOeuxdkrXlLuqrUsVNVLue1VyvwuY4bWn4PKjYAlahd+THyjT+GkXN9pnxLDGTo9ZfsueXZOqXK16xtClAub0Ff/hjrrOD9g3u4wLVHg4NARi3IayFAgW4hcgSl1EzyhJ059vJKIiiBukSn9xi0tL2SY6cqIBskDv64hLl+86/82vxndW5LSG3zwQp4ZWbzjHrAdgIgnY/7BHthX/hRseynPxhXtIey7+z8FYGBvY93Gg8RoqEHmM6yHB4JXs52FpJj+R/GfSX/4I6ArjsguQLU9hU1Z7Lq0EB9IdXwUqdQ8/2NAG4rrJ88BuAYeAFd3blg3vN423dQMePOv9iTbLZvGkR5mnYzRadETn6qidxxCkj7MMkLhxhMddatWSLBENyC+1SKfSHWK7Jno+/zNRa+qOWyRR9cGdYlDgBhEBqFRNxTFAaq+1PSEAoTDs7/alPeLHFd6JynTqHSCjVLcYcBEVLUxjKfvSCknwjdOjjK8D97cJqi72whvRSlqSMWNwvcp2GvHt27IVuJ5aCJRFNDa1yzPZsGq3wQxOTmyPilDBDbuk+VgwF83OVjjBlXRMAo4+8BeINcX7vSaM7jgjd3wSlBHEduRDU/LyutIbRi8PuyEzAX7xBj0JqXOlm/6Cm5l2+krtY4w9ZljlSkUyFi3V6O4T; path=/; domain=.XXXXXXX.com
Transfer-Encoding:chunked
Vary:Accept-Encoding,User-Agent
X-Powered-By:Servlet/2.5 JSP/2.1


Request Headers
Accept:text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Encoding:gzip, deflate, sdch, br
Accept-Language:en-IN,en;q=0.8
Cache-Control:max-age=0
Connection:keep-alive
Cookie:_ga=GA1.2.1347973701.1497504891; XXXXX-XXX_cookie=R951123141; SMSESSION=+ebdOREr4ZKBRN+XWlS2UQ7BC9YZ4uwIyRnAUJxguYrI6GFAjdWscW3XawnLjJrCsYIWqteptYW7/ILHHp6bxR389CSoDbn/kSjT5DJAlZO4/8tBfn/zDwjDv1s+z8q0meJcoUVaV7j/m0sSVUoJzHUZgG3OIpcKgJKg/7hqxqlcnY7eb7kzfVtyEDTlwjw6zemdunSoaHd8vpf87YvHSBH+62e+x1eXWc0E48gS/xgpgtgPozInpxwlSCq/8E7fbyhEN9EWgAQSWJ3Jl/vSfXCyssWrdumQWCgl/2Cp3ODHziOdO7TowQfI8IsP8Z+P+Wr+L5gGY6HiUj9sw/qbWFbxN/DRU3P9NyTiVsQ5+T0H3GskK3h/AHnn0FvCZn+w7O1ku7ebowEaVf2EWzn0Vw8QVDzGcjV8jjSnS3mZxJT/jN+YkTYNdhdgxsxti2Gyp6Y+hv+yKcYTFIper5vyj1/4oj+P6w+9e/r+FH2dG976gQRXB/6v740ytgGzUfDpG04BmpWMcU2FMqEtECf0Ofcb7NbmCpxrH6X9eTmiThqEHwH5dAfFu7kksY20Lam8y+izbBl5wwXmnYgUrOQSXYI0/YlYMheybaFb+09PEqGqfygw6jOrgI8/SI4hTd+cUZvTpYRaGfeIT+UZ5RtpAScxvHV6A35yWJSpjpCrEe/RbaZ2nL9DFazFj8qlOvg2ZHNnxdFmRPb6HtkDDXr6JxaQ5coB2DCB0HqjWrR/FGND5mqbUhOBKCPthSBB5oRaZ2qcgy8Y+th8Ecy1C4rSZoPzMZS/5IRU3GhvBFsTykKqIEGKmOVwqEadpSXYcbhSnpctYI2oTRNoFIDwaSPmqasERmfmgHT8jk8p0xxdz6JophGb2RZCuCmv0wfTPOHYQgi9IMNoK0qGK+MNUg5yZ4lLYNAxafK1fpn5JvS9kVdSI6E6N8fD9N52s6buGnXZslxV9V2nKqAeBdnbBHP9Jzrn9Ta7a4eqUpTKOevveV6PFjMYHZOMTMPcGHZXLBn4S7JIvOWPUBBwDyqekfu6S6jV70dPPd5JNnzWGIobwoy8qNFXhpq9D/0424oC5kgwabYfgRP4kQ8KAo2ZuRg25KkhIh6vTYeCXDiVP1itXeJ+WtUM14OTUFzj217DD0tcKhUAkdO3Xsan5G0j2VBeAhCwkIKFtedcZmIRfqVFqN1ghbkvco3TUwWJv/SZUh/97prNv5a0vu+g1w8ZOa5SP7bWNLmxBrhx4J7go9dbfwZVZpfjpqPvYvPYcb8fnB6S/oBOumE5ppJUh3wRVanHZA+JVZJI9qYNybu+MC9Nno4jdfho/8u1ClYy5+QhPQdSmKUHi+a+UlPNkwwQGnLindNKcGj8kasDUHe77OkZr84PDeh0c7j5ePVYJ6Ye8BrENzTPQst9MnxeNYQC0R9racVo3xI/w0pEoFAKD14tTI2IcH6IFsw8s7Z8fVWm8BnJeqjDO/GZqjSPNVEK9pd4/HMYxuWZigi/L4uS2kRbmB3OznrtPHsEu88ICyxqofuk; Portal-Organisation-Cookie=8a5bbfef1999ec8643314bd79ce71218
Host:XXXXX-XXX.XXXXXXX.com
Referer:https://abcdefg.hijklmn.com/gateway/logon.htm?TYPE=33619969&REALMOID=06-0008fcbc-145a-1be3-b1b7-05e50a27c0f4&GUID=&SMAUTHREASON=0&METHOD=GET&SMAGENTNAME=-SM-UmRXxeTjx0wukOHolEZ%2fuji7%2f9Iznin6POLHraH%2b4Pc30o%2fZFVkAWVfTNIh2ZF8V&TARGET=-SM-HTTPS%3a%2f%2fXXXXX--XXX%2XXXXXXX%2ecom%2fXXXXXXXWeb%2fReportDownloadServlet%3foutputInd%3dP%26executionId%3d1234567
Upgrade-Insecure-Requests:1
User-Agent:Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36

Query String Parameters
outputInd:P
executionId:1234567

Does this help?