api - Converting Google Sheet to Excel then sending as an email attachment works, but cannot access XLXS file (Unauthorized Error 401)

Question

The code below works to convert and send a google sheets as an excel file over email, but the file does not have the actual excel, just Unauthorized Error 401.

function getGoogleSpreadsheetAsExcel(){

try { var ss = SpreadsheetApp.getActive();

var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";

var params = {
  method      : "get",
  headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
  muteHttpExceptions: true
};

var blob = UrlFetchApp.fetch(url, params).getBlob();

blob.setName(ss.getName() + ".xlsx");

MailApp.sendEmail("[email protected]", "Google Sheet to Excel", "The XLSX file is  attached", {attachments: [blob]});}

catch (f) {
  Logger.log(f.toString()); }
}

I believe it has something to do with authorization to access the file, but I am very new to coding and API's, so any help would be appreciated.

Answer 1

Issue:

Your code works fine for me.

According to the error message you are getting, you must be having some authorization issues and based on this and this make sure to include in the Manifest file the following Spreadsheet & Document scopes:

"oauthScopes": ["https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/documents",
    "https://www.googleapis.com/auth/spreadsheets"]

and the appsscript.json should look like that:

{
  "timeZone": "Europe/Paris",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": ["https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/documents",
    "https://www.googleapis.com/auth/spreadsheets"]
}

Although I post the recommended approach of generating and sending an excel file.

Recommended Approach:

function myFunction() {
const ss = SpreadsheetApp.getActive();
const nameFile = ss.getName() + ".xlsx";
const requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};  
const url = "https://docs.google.com/spreadsheets/d/"+ ss.getId() + "/export?format=xlsx";
const result = UrlFetchApp.fetch(url , requestData);  
const contents = result.getContent();
    
MailApp.sendEmail("[email protected]", 
                  "Google Sheet to Excel",
                   "The XLSX file is  attached", 
                  {attachments:[{fileName:nameFile, content:contents, mimeType:"MICROSOFT_EXCEL"}]});     
}