CODEWITHSUNDEEP
javascriptemailgoogle-sheetsspreadsheetemail-attachments
Mostafa Karami
Mostafa Karami

Reputation: 23

How to send email from google sheet with the sheet as a PDF attachment

I have a google sheet that has two sheets, Form Responses and report: see here

when a form submits report sheet change with the last form or last row of the responses sheet. I want to send an email to the person who submits the form and attach the report sheet as PDF after sending email write sent email in column e: see here

email to: Form Responses column b subject: some text and Form Responses column a cc: [email protected] body: some text and Form Responses column f attach: report sheet as PDF

i used this code but it doesn't work

 function onSubmit(e){
  Logger.log('submit ran');

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var lastRow = sheet.getLastRow();
  var sa = sheet.getRange(lastRow, 1).getValue();
  var sB = sheet.getRange(lastRow, 2).getValue();
var sf = sheet.getRange(lastRow, 6).getValue();


  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetgId = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetId();
  var email =  Session.getUser().getEmail();
  var subject = SB;
  var body = Sf;


  var url = "https://docs.google.com/spreadsheets/d/e/..............................................................=0&single=true&output=pdf";


  var result = UrlFetchApp.fetch(url)

  var contents = result.getContent();

 if (emailSent !== "EMAIL_SENT") { 

      MailApp.sendEmail(email,subject ,body, {attachments:[{fileName:SB+".pdf", content:contents, mimeType:"application//pdf"}]});
       sheet.getRange().setValue("EMAIL_SENT");

          SpreadsheetApp.flush();

        }
  }

I get the url from spreadsheet>file>publish to web>publish report tab as pdf

i rewrite the code but get error "TypeError: report.getAs is not a function (line 36, file "Code")"

   function onSubmit(e){


 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getAet();
 
  var calculate = ss.getSheets()[2];
 var Responses = ss.getSheets()[0]; 
var report = ss.getSheets()[1];


  


 var sh = sheet.getRange(lastRow, 8).getValue();
 


   var cell = calculate.getRange("b2");
  cell.setFormula(sh); 
SpreadsheetApp.flush();
 

  var email = sB;
  var subject = 
  var body = se;
  
  var calculate = ss.getSheets()[2];
 vafunction onSubmit(e){
  Logger.log('submit ran');


  var sheet = ss.getActiveSheet();
 
  var calculate = ss.getSheets()[2];
 var Responses = ss.getSheets()[0]; 
var report = ss.getSheets()[1];


  


  var lastRow = Responses.getLastRow();
  var sa = sheet.getRange(lastRow, 1).getValue();
var se = sheet.getRange(lastRow, 5).getValue();
v
  cell.setFormula(sh); 
SpreadsheetApp.flush();
 

  var email = sB;

  var body = se;
  
  var calculate = ss.getSheets()[2];
 var Responses = ss.getSheets()[0]; 

var pdf = report.getAs('application/pdf');

 
     
      MailApp.sendEmail(email,subject ,body,  {attachments:[pdf]});
     sf.setvalue("EMAIL_SENT");
      
          SpreadsheetApp.flush();
  
  }
r Responses = ss.getSheets()[0]; 

var pdf = report.getAs('application/pdf');

 
     
      MailApp.sendEmail(t ,body,  {attachments:[pdf]});
     sf.setvalue("EMAIL_SENT");
      
          SpreadsheetApp.flush();
  
  }

Upvotes: 1

Views: 904

Answers (2)

Derek S.
Derek S.

Reputation: 358

I dug out an old example and was using getAs() to send a document so if you only want one sheet you would just create a new spreadsheet with the info you do want to send.

var newSheet = SpreadsheetApp.create(Title,y,x);

Set whatever values you want in it and then 

var pdf = newSheet.getAs('application/pdf');
MailApp.sendEmail(email, subject, body, {attachments:[pdf]} );

I suggest creating a new spreadsheet because you cannot convert just one tab to PDF.

Upvotes: 0

Aerials
Aerials

Reputation: 4419

I would recommend you to use Apps Script for this.

You as the owner of the form and response sheet, can create a bound script and use the Gmail and Forms services to create your desired pdf, and sent it to your users.

You can also create triggers to check when a new response is submitted.

References:

Upvotes: 1

Related Questions