Google-Spreadsheet Script: Send outlook Email with attached XLS of Spreadsheet

Question

I want to send out only one tab of a specific sheet by Outlook mail using Google App script in Google Spreadsheet.

I searched and wrote the script, but it is too old to work or an error occurs.

What i want Among the multiple tabs of the List sheet of the spreadsheet, the data from sheet 33 to column A1:M are saved as "values" in .xls and attached to Outlook mail and sent

And how do I add a day to today's date in new Date()?

If there is an expert who can help me, it would be a great help.

Thanks.

function sendEmail(){

        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet=ss.getSheetByName("Sheet33");
        var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getSheetName() + "&exportFormat=xlsx";

        var d = Utilities.formatDate(new Date(), "GMT+9", "yyyyMMdd");
             

    var token         = ScriptApp.getOAuthToken();
    
    var response      = UrlFetchApp.fetch(url, {
      headers: {
        'Authorization': 'Bearer ' +  token
    
    },
          muteHttpExceptions: true
      
    });

  var blobs   = [response.getBlob().setName(ss.getSheetName() + ".xlsx")];

  var receipient = "khan@abc.com"
  var subject = "Date_"+ d + ss.getSheetName() +" List share."
  var emailbody = "Date_"+ d + ss.getSheetName() +" List share.\nThanks."

  MailApp.sendEmail(receipient, subject, emailbody, {attachments: blobs});

  Browser.msgBox("Date_"+ d + ss.getSheetName() + " Send Mail Complete.", Browser.Buttons.OK);
  }```

Answer 1

Issue:

You are using an old export URL (https://docs.google.com/feeds/...), I don't think this can be used anymore. Also, you are providing the spreadsheet name instead of the id.

Solution:

The export URL should be something like this instead:

var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx&gid=" + sheetId;

Code snippet:

function sendEmail(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = ss.getId();
  var sheet = ss.getSheetByName("Sheet33");
  var range = sheet.getRange("A1:M");
  var tempSheet = ss.insertSheet("My temporary Sheet");
  range.copyTo(tempSheet.getRange("A1:M"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var tempSheetId = tempSheet.getSheetId();
  var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx&gid=" + tempSheetId;
  var tomorrow = new Date();
  tomorrow.setDate(new Date().getDate() + 1);
  var d = Utilities.formatDate(tomorrow, "GMT+9", "yyyyMMdd");             
  var token = ScriptApp.getOAuthToken();    
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token  
    },
    muteHttpExceptions: true   
  });
  var blobs = [response.getBlob()];
  var receipient = "khan@abc.com";
  var spreadsheetName = ss.getName();
  var subject = "Date_"+ d + spreadsheetName +" List share."
  var emailbody = "Date_"+ d + spreadsheetName +" List share.\nThanks."
  MailApp.sendEmail(receipient, subject, emailbody, {attachments: blobs});
  ss.deleteSheet(tempSheet);
  Browser.msgBox("Date_"+ d + spreadsheetName + " Send Mail Complete.", Browser.Buttons.OK);
}

Notes:

• In order to add a day to your date, you can do something like this. That's what I did in the sample above.
• In this sample, the entire Sheet33 is exported. If you want to export a specific range instead, you should (1) copy the desired range to a new temporary sheet, (2) export that sheet, (3) delete the sheet. See, for example, this answer.