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

Question

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){
  Logger.log('submit ran');

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  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 sB = sheet.getRange(lastRow, 2).getValue();
var se = sheet.getRange(lastRow, 5).getValue();
var sf = sheet.getRange(lastRow, 6).getValue();
 var sh = sheet.getRange(lastRow, 8).getValue();



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


  var email = sB;
  var subject = "راید ریپورت "+sa;
  var body = se;

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

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  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 sB = sheet.getRange(lastRow, 2).getValue();
var se = sheet.getRange(lastRow, 5).getValue();
var sf = sheet.getRange(lastRow, 6).getValue();
 var sh = sheet.getRange(lastRow, 8).getValue();



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


  var email = sB;
  var subject = "راید ریپورت "+sa;
  var body = se;

  var calculate = ss.getSheets()[2];
 var Responses = ss.getSheets()[0]; 
var report = ss.getSheets()[1];
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 report = ss.getSheets()[1];
var pdf = report.getAs('application/pdf');



      MailApp.sendEmail(email,subject ,body,  {attachments:[pdf]});
     sf.setvalue("EMAIL_SENT");

          SpreadsheetApp.flush();

  }

Do you want to send the users the whole response sheet as PDF, or just the responses that where saved until the moment when the user filled the form? If other users fill the form afterward, will every user get a second email?. And please post any code you have tried so far. — Aerials
each user should enter Code(user name) and password and receive email about his or her specific data and for next user data will change and email sent just to that user( not all of them) — Mostafa Karami

Aerials Aerials · Accepted Answer · 2020-05-22T09:32:17

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:

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

2 Answers