Search code examples
javascriptgoogle-apps-scriptgoogle-sheets

Exception: Request failed for https://docs.google.com returned code 401 - Unauthorized

I have exactly the same problem highlighted in this link but none of the suggestions given seems to help and the code I'm testing is pretty much the same as the one described in this link.

I'm not a GAS expert and this is the code I developed inspired by similar codes on the internet, including a test function written for debugging purposes.

const ss = SpreadsheetApp.getActiveSpreadsheet();

function testExportGoogleSheet() {
  const ssId = ss.getId();
  const sh = ss.getSheets()[1];
  const fileName = sh.getName();
  const file = exportGoogleSheet(fileName, ssId, sh);

  MailApp.sendEmail('myEmailAddress', 'testSubject', 'testBody',{attachments : file});
}

function exportGoogleSheet(fileName, ssId, sh) {
  let url, urlExt, gid;

  // Base URL
  url = 'https://docs.google.com/spreadsheets/d/' + ssId + '/export';
    
  urlExt = '?format=pdf' +
  // The below parameters are optional...
        '&size=7' +
        '&scale=2' + 
        '&fitw=true' +
        '&fzr=true' +
        '&portrait=false' +
        '&sheetnames=false' +
        '&printtitle=false' +
        '&pagenumbers=false' +
        '&gridlines=true' +
        '&horizontal_alignment=CENTER' +
        '&vertical_alignment=TOP' +
        '&attachment=true';

  gid = '&gid=' + sh.getSheetId();
  
  url += urlExt + gid;

  const token = ScriptApp.getOAuthToken();
  const params = { method: 'GET', headers: { 'authorization': 'Bearer ' + token } };

  //'muteHttpExceptions': true,
  //'oAuthServiceName': 'spreadsheets',
  //'oAuthUseToken': 'always',

  const blob = UrlFetchApp.fetch(url, params).getBlob().setName(fileName + '.pdf');
  
  return blob;
}

// script error returned at UrlFetchApp.fetch() function call
Exception: Request failed for https://docs.google.com returned code 401. Truncated server response:
<HTML>
<HEAD>
  <TITLE>Unauthorized</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
  <H1>Unauthorized</H1>
  <H2>Error 401</H2>
</BODY>
</HTML>

// appsscript.json manifest file
{
  "timeZone": "myTimeZone",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
          "https://www.googleapis.com/auth/spreadsheets.currentonly",
          "https://www.googleapis.com/auth/script.external_request",
          "https://www.googleapis.com/auth/userinfo.email",
          "https://www.googleapis.com/auth/script.send_mail",
          "https://www.googleapis.com/auth/forms.currentonly",
          "https://www.googleapis.com/auth/script.container.ui"
        ]
}

The problem seems to be at the level of the UrlFetchApp.fetch() function call. The code worked for a while, but then I must have changed something into the code and now I can't figure out where the problem is!

I've been googling for days but I can't solve the problem and I'm at a standstill.

Thanks in advance to anyone who wants to help me to solve the problem.

Solution

  • From your showing manifest file (appsscript.json), I guessed that in your situation, the scope might not be enough for using your script. So, please modify appsscript.json as follows, and test it again.

    {
  "timeZone": "myTimeZone",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/userinfo.email",
    "https://www.googleapis.com/auth/script.send_mail",
    "https://www.googleapis.com/auth/forms.currentonly",
    "https://www.googleapis.com/auth/script.container.ui",
    "https://www.googleapis.com/auth/drive.readonly" // <--- Added
}
    • In this case, I think that the scope of https://www.googleapis.com/auth/drive can be also used.