0

I have this script almost done to automate generating a spreadsheet and emailing it. However ,it keeps emailing it as a pdf and not a spreadsheet format.

When I try getAs(MimeType.GOOGLE_SHEETS) or the microsoft and open office versions, I get an exception being thrown:

Exception: Blob object must have non-null data for this operation.
generateEmailToSupplier @ GenerateEmailToSuppliers.gs:18
(anonymous) @ Main.gs:61
Main    @ Main.gs:55

The spreadsheet I have generated is definitely populated with data and I am definitely accessing that in this particular module.

Here's my code:

/**
 * Generate and send email to supplier
 * 
 * params:
 * - _supplier : String[] ===> [name of supplier, contact name, contact email]
 * - orderSheetId : Spreadsheet ===> file id forspreadsheet containing items to order
 */
function generateEmailToSupplier(_supplier, _orderSheetId){

  const email = MailApp;

  const testEmailAddress = "fake@email.com";

  const _orderSheet = DriveApp.getFileById(_orderSheetId)

  Logger.log(`mimetype email : ${_orderSheet.getMimeType()}`)

  email.sendEmail({
    to: testEmailAddress,
    subject: `  Order Needed from ${_supplier[0]}`,
    htmlBody: 
      `<p>Hey ${_supplier[1]} at ${_supplier[2]} ,<br><br> ` +
      "Attached is an excel sheet showing what products we would like to get an order for. <br>"+
      "You can also log into the app for reference or make changes.<br><br>" +
      "This is an automated, weekly email.<br>"+
      "Feel free to reach out to us with any questions.<br><br>"+
      "Regards,<br><br>",
    attachments: [_orderSheet.getAs(MimeType.GOOGLE_SHEETS)]
  });
Cambo
  • 111
  • 1
  • 1
  • 5

1 Answers1

0

This is because Google Docs/Google Sheets can't be included to email as an attachment since they are web based files. Even if you try to manually attach the email it has to come from the Drive (Insert files using Drive). When opened by the recipient it will redirect to the Drive Link that will still require them to have access directly to the source file. This is basically similar to just sharing the link of the file.

enter image description here

So what you can do is convert the file to XLSX before sending in an email.

Try:

function generateEmailToSupplier(_supplier, _orderSheetId) {

  const email = MailApp;
  const testEmailAddress = "fake@email.com";

  const _orderSheet = DriveApp.getFileById(_orderSheetId)

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

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

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

  blob.setName(SpreadsheetApp.openById(_orderSheetId).getName() + ".xlsx");

  Logger.log(`mimetype email : ${_orderSheet.getMimeType()}`)

  email.sendEmail({
    to: testEmailAddress,
    subject: `  Order Needed from ${_supplier[0]}`,
    htmlBody:
      `<p>Hey ${_supplier[1]} at ${_supplier[2]} ,<br><br> ` +
      "Attached is an excel sheet showing what products we would like to get an order for. <br>" +
      "You can also log into the app for reference or make changes.<br><br>" +
      "This is an automated, weekly email.<br>" +
      "Feel free to reach out to us with any questions.<br><br>" +
      "Regards,<br><br>",
    attachments: [blob]
  });
}

References:

Logan
  • 1,691
  • 1
  • 4
  • 11