App script - send spreadsheet in xlsx attachment

Question

I'm using following script at the moment, but with this script the whole spreadsheet is sent as attachment and I would prefer only 1 sheet "Planning Waak_Zomervakantie" is sent. Is it possible? And what do I need to change in the script?

var MASTERSHEET_ID = '###';

function MailPlanning() {
  try {
    var ss = SpreadsheetApp.getActive();
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
    var sheet = ss.getSheetByName('Planning Waak_Zomervakantie');
    var range = sheet.getRange("D1:D1");
    var cell = range.getValue();
    var rangename = sheet.getRange("B1:B1");
    var name = rangename.getValue();
    var recipients = cell + "," + "[email protected]";
    var params = {
      method: "get",
      headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true
    };
    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(ss.getName() + ".xlsx");
    GmailApp.sendEmail(recipients, "Bevestiging vakantiejob WAAK komende zomervakantie 2021", "Beste " + name + "," + "\n\nGoed nieuws! Je werd geselecteerd voor een vakantiejob bij WAAK." + "\nIn bijlage vind je alvast jouw planning." + "\n\nGelieve ons zo snel mogelijk te bevestigen of je deze periode kan werken via [email protected] of 056/52 15 50." + "\n\nAlle informatie over eventuele infosessie en afdeling zullen later doorgegeven worden." + "\nVeel succes!" + "\n\nVriendelijke groeten,\n\nTempo-Team Waak" + "\nDavina en Lisa" + "\nTel: 056/52 15 50" + "\[email protected]", { noReply: true, attachments: [blob] });
  } catch (f) {
    console.log(f.toString());
  }
}

Tanaike Tanaike · Accepted Answer · 2021-03-02T07:50:46

I believe your goal as follows.

You want to export the specific sheet in a Google Spreadsheet as XLSX format using Google Apps Script.

In this case, I would like to propose to add the information of sheet ID to the query parameter. But, I thought that your endpoint of https://docs.google.com/feeds/download/spreadsheets/Export?key=###&exportFormat=xlsx might be a bit older. Although I think that this can be used now, I'm not sure when this is deprecated. But I'm not sure about the detail of this situation. So, in the current stage, I would like to propose to use the endpoint of https://docs.google.com/spreadsheets/export?id=###&exportFormat=xlsx. This can be retrieved by Drive API v3. When this is reflected to your script, it becomes as follows.

Modified script:

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

var sheet = ss.getSheetByName('Planning Waak_Zomervakantie');
// var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx&gid=" + sheet.getSheetId();
var url = "https://docs.google.com/spreadsheets/export?id=" + ss.getId() + "&exportFormat=xlsx&gid=" + sheet.getSheetId();

App script - send spreadsheet in xlsx attachment

1 Answers

Modified script:

References: