Search code examples
google-apps-scripturlfetch

Why is UrlFetchApp unable to fetch content in this case?

I am hoping to save some files to my Google Drive. But UrlFetchApp.fetch wouldn't get any response and would time out. How do I know what is wrong here?

var url = 'https://www.cmegroup.com/CmeWS/exp/voiProductsViewExport.ctl?media=xls&tradeDate=20210702&assetClassId=2&reportType=F&excluded=CEE,CEU,KCB';
var response = UrlFetchApp.fetch(url);

Logger.log(response.getResponseCode())

var folder = DriveApp.getFolderById(folderID); // omitting folderID in this snippet
var file = folder.createFile(response.getBlob());
file.setName('file.xls');
Solution

  • It sounds like CME may have blocked Apps Script.

    I have seen a few questions like this today.

    There is not much you can do about the CME blockage (if that is the case), but there is a way to workaround this, even though it requires visiting a browser. I noticed that if I used the JavaScript fetch API, it would return the XLS blob correctly, so here is a workaround using an Apps Script Web App.

    Index.html

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    
  </body>

  <script>
    const r = fetch('https://www.cmegroup.com/CmeWS/exp/voiProductsViewExport.ctl?media=xls&tradeDate=20210702&assetClassId=2&reportType=F&excluded=CEE,CEU,KCB')
    .then(r => r.blob())
    .then(b => readFile(b))
    .then(result => saveToDrive(result))

    function saveToDrive(base64) {
      google.script.run
        .withSuccessHandler(() => console.log("success"))
        .saveAsXLS(base64)
    }

    function readFile(blob){
      return new Promise((resolve, reject) => {
        var fr = new FileReader();  
        fr.onload = () => {
          resolve(fr.result)
        };
        fr.onerror = reject;
        fr.readAsDataURL(blob);
      });
    }

  </script>

</html>

    Code.gs

    function doGet(){
  return HtmlService.createHtmlOutputFromFile("index")
}

function saveAsXLS(dataURL){
  const parts = dataURL.split(",")
  const type = (parts[0]).replace('data:','');
  const blob = Utilities.newBlob(Utilities.base64Decode(parts[1], Utilities.Charset.UTF_8), type, "sheet.xls")

  DriveApp.createFile(blob)
}

    Basically, instead of using UrlFetchApp, it uses a browser instance to use the JavaScript fetch API to get the blob, and then encode it before sending it to the Apps Script side. The Apps Script side then decodes it and saves it to Drive.

    Caveat

    Though you might just as well be using curl or Python at this point since you have to actially visit the Web App to execute it.

    Reference