CODEWITHSUNDEEP
excelgoogle-apps-scriptgoogle-sheetsgmailxlsx
Valip
Valip

Reputation: 4620

Blob conversion: Get file as xlsx

I am trying to send an email that has a xlsx file attached but the problem I'm running into is that the file is opened as PDF when I download it

This is my code:

function sendMail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['test value']);

  var ss   = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId();
  var file = DriveApp.getFileById(ssID); 
  var blob = file.getBlob();

  blob.setName("test.xlsx");

  GmailApp.sendEmail(
    '[email protected]',
    'Test',
    'test.',
    {
      attachments: blob
    }
  );
}

If I try to open the file directly from the email it says:
Google Docs encountered an error. Please try reloading this page, or coming back to it in a few minutes.
To learn more about the Google Docs editors, please visit our help center.

Upvotes: 0

Views: 4169

Answers (1)

Tanaike
Tanaike

Reputation: 201408

How about the following modifications?

Modification points :

  • When Spreadsheet is converted to blob using DriveApp.getFileById(ssID).getBlob(), the Spreadsheet is automatically converted to PDF.
    • It is required to convert from Spreadsheet to xlsx file.
  • When it attaches blob to e-mail, please use attachments: [blob].

The modified script which reflected above is as follows.

Modified script :

function sendMail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['test value']);

  var ss   = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId();

  // Added
  var url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=xlsx";
  var params = {
    method: "get",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()}
  };
  var blob = UrlFetchApp.fetch(url, params).getBlob().setName("test.xlsx");
  // Added

  GmailApp.sendEmail(
    '[email protected]',
    'Test',
    'test.',
    {
      attachments: [blob] // Modified
    }
  );
}

Note :

References :

If I misunderstand your question, I'm sorry.

Upvotes: 3

Related Questions