CODEWITHSUNDEEP
google-apps-scriptgoogle-drive-apigoogle-workspaceurlfetch
Ryan Capule
Ryan Capule

Reputation: 143

GmailApp.sendEmail not working with G Suite Account

I am trying to use GmailApp.sendEmail to send a PDF of a google sheet by email. The script works when the sender has a @gmail.com account, but for some reason a GSuite account (not @gmail.com) sends an HTML doc that does not contain the PDF of the sheet.

Here is a snippet of my code, so far I only use GmailApp.. in this portion.

function printForward() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[3];
  // if you change the number, change it also in the parameters below
  var shName = sh.getName()
  var ssname = ss.getName();
  var length = ssname.length;
  var date = ssname.substring(0,length-15);
  const body = "All, <br><br>Attached is the invoice for the week of "+ date+"."+"<br><br> Thank you, <br> Elizabeth Moscoso";
  var fnumber = 2;
  sendSpreadsheetToPdf(3, shName, ss.getRange('Constants!T5').getValue(),"Invoices "+date, body, date, fnumber);
}

function printMidwest(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sh = ss.getSheets()[5];
 var shName = sh.getName();
 var ssname = ss.getName();
 var length = ssname.length;
 var date = ssname.substring(0, length-15);
 const body = "All, <br><br>Attached is the invoice for the week of "+ date+"."+"<br><br> Thank you, <br> Elizabeth Moscoso";
 var mnumber = 1;
 sendSpreadsheetToPdf(5, shName, ss.getRange('Constants!T5').getValue(), "Invoices "+date, body, date, mnumber);
}


function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody, invcdate, foldNum) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()  
  var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');

  if(foldNum == 2){var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw = true' // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
      }
  if(foldNum == 1)
  {var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
      
      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw = true' // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
      }


  var options = {
    headers: {method:"GET",
      'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName+" Invoice "+ invcdate+ '.pdf');
 
  var mailOptions = {};
  var attachmentArray = [];
  
  if(blob){
  attachmentArray.push(blob);
  mailOptions.attachments = attachmentArray;
  }
 
  if(htmlbody){
  mailOptions.htmlBody = htmlbody;
  }
  
  if(email){
  GmailApp.sendEmail(email,subject,"",mailOptions);
  }
 
  //save PDF
  const nameFile = "Forward " + invcdate + ".pdf";
  if(foldNum == 2){
  var forwardFolder = DriveApp.getFolderById("1-n7O87J0zN7ZnOtErhcJR_JDbkrjSyj8");
  forwardFolder.createFile(blob);}
  if(foldNum == 1){
  var midwestFolder = DriveApp.getFolderById("17cQrFnAzy3B6jmwZB5-Osa6G7oC3YOLw")
  midwestFolder.createFile(blob);
  }

}

I am not sure why a GSuite account would not be able to send the email/pdf. Also, if I take out the sending email part completely and have the GSuite account only save the PDF, it gives the same HTML file.

Upvotes: 0

Views: 164

Answers (1)

Martin Zeitler
Martin Zeitler

Reputation: 76579

I've just tried by myself, an URL alike this should export PDF (assuming a proper oAuth2 scope). The PDF export would require scope https://www.googleapis.com/auth/spreadsheets.readonly.

var url = 'https://docs.google.com/document/d/' + id + '/export?format=pdf'

This obviosuly would export the whole document, but one still can narrow that down later on.

You could also try using class MailApp instead of class GMailApp, which wouldn't trigger re-authorization requests that often, when changes were made to the script (this may be a factor). Unless one needs to access a user's GMail mailbox, there is no given reason to use GMailApp.

The oAuth2 authorization scopes also vary; class GMailApp uses a rather wide scope:

https://mail.google.com/

While class MailApp uses a quite specific scope:

https://www.googleapis.com/auth/script.send_mail

And from the question it is also not really clear, if you have the "OAuth2 for Apps Script" library imported; it's ID would be: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF.

Upvotes: 1

Related Questions