Send email of specific cells as PDF

Question

I've successfully learnt through this site how to send a sheet as a PDF attached via google sheets. What I'd now like to do is slightly expand that a bit.

I want to select a constant specific range that will export, along with the last added row within a column range.

J1:S1 to export every time. then the newest row from J2:S2 onwards.

I like to have these two ranges combined, with the constant range (J1:S1) always being on top.

I'm not entirely sure if this is possible as a PDF or if it may need to be a HTML table. I can work with either atm.

I really not sure where to start here, so would appreciate suggestions.

Thank you!

In the example above, the titles remain on every email / PDF. But each newest edition to the rows below is added to the PDF. So in the example above the row with the 2's would be added.

var ss = SpreadsheetApp.getActiveSpreadsheet();

function sendReport() {
  var sheetTabNameToGet = "Form response master";
  var range = "J1:S1";
  var pdfBlob = exportRangeToPDf(range, sheetTabNameToGet);
  var message = {
    to: "example@example.com",
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [pdfBlob.setName("Monthly sales report")]
  }
  MailApp.sendEmail(message);
}


function exportRangeToPDf(range, sheetTabNameToGet) {
  var blob,exportUrl,options,pdfFile,response,sheetTabId,ssID,url_base;
  ssID = ss.getId();
  sh = ss.getSheetByName(sheetTabNameToGet);
  sheetTabId = sh.getSheetId();
  url_base = ss.getUrl().replace(/edit$/,'');
  exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +
    
    '&gid=' + sheetTabId + '&id=' + ssID +
      '&range=' + range + 
        '&size=A4' +     // paper size
          '&portrait=false' +   // orientation, false for landscape
            '&fitw=true' +       // fit to width, false for actual size
              '&sheetnames=true&printtitle=false&pagenumbers=true' + //hide optional headers and footers
                '&gridlines=false' + // hide gridlines
                  '&fzr=false';       // do not repeat row headers (frozen rows) on each page
  
  options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }
  options.muteHttpExceptions = true;//Make sure this is always set
  response = UrlFetchApp.fetch(exportUrl, options);
  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;
    
  }  
  blob = response.getBlob();
  return blob;
}

Answer 1

If the last response is always a last row, you can remove all the rows between the header and the last row, export the sheet in PDF, send it, and restore the sheet back:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Form response master");

  var formula = sh.getRange('a1').getFormula();    // take the formula from A1
  var data = sh.getDataRange().getDisplayValues(); // get data from the original table
  var table = [data.shift(), data.pop()];          // make the new table from first and last row

  sh.clearContents()                               // clear the sheet
    .getRange(1,1,table.length,table[0].length)
    .setValues(table);                             // put the new table on the sheet

  sendReport(get_blob_PDF(ss, sh));                // make PDF and send it
  sh.clearContents().getRange('a1')
   .setFormula(formula);                           // restore the sheet
}

function get_blob_PDF(ss, sh) {
  SpreadsheetApp.flush(); // update the Sheet
  var ssID = ss.getId();
  var shID = sh.getSheetId();
  var url_base = ss.getUrl().replace(/edit$/,'');
  var exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +
    '&gid=' + shID + '&id=' + ssID +
    '&size=A4' +         // paper size
    '&portrait=false' +  // orientation, false for landscape
    '&fitw=true' +       // fit to width, false for actual size
    '&sheetnames=true&printtitle=false&pagenumbers=true' + //hide optional headers and footers
    '&gridlines=false' + // hide gridlines
    '&fzr=false';        // do not repeat row headers (frozen rows) on each page
  
  var options = {headers: {'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}}
  options.muteHttpExceptions = true; // Make sure this is always set

  var response = UrlFetchApp.fetch(exportUrl, options);
  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;  
  }

  return response.getBlob();
}

function sendReport(pdfBlob) {
  var message = {
    to: "exmaple@mail.com",
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [pdfBlob.setName("Monthly sales report")]
  }
  MailApp.sendEmail(message);
}

In your particular case the problem was that your sheet 'Form response master' is made via the array formula in cell 'A1'. It makes the code a little bit weird.

It does the job but, yeah, it's likely a script that takes a response object from the trigger onFormSubmit(e) would be more efficient.

Update

If there is email address in cell 'C2' you can take it if you replace the line:

sendReport(get_blob_PDF(ss, sh));

with:

var address = table[1][2]; // get the address from 'C2'
sendReport(get_blob_PDF(ss, sh), address);

And use the address in the function sendReport() if you change these lines:

function sendReport(pdfBlob) {
  var message = {
    to: "exmaple@mail.com",

this way:

function sendReport(pdfBlob, address) {
  var message = {
    to: address,

Update 2

If the cell 'C2' contains a name and you have the exact matching between every name and email, you can get the email address by the name via object: {name1: address1, name2: address2, ...etc}. To make this happen you can change the first lines of the function sendReport() this way:

function sendReport(pdfBlob, person) {
  var address = {
    'James Smith': 'james.smith@gmail.com',
    'Mark Williams': 'mark.willaims@gmail.com',
    'Vladimir Putin': 'goto@thehell.now',
  }
  var message = {
    to: address[person],

Technically it would be enough, but just in case I'd advice to change those two lines in the main() function as well:

var person = table[1][2]; // get the persons name from 'C2'
sendReport(get_blob_PDF(ss, sh), person);

Update 3 (XLSX and CSV)

To send it as XLSX file you can use this function:

function get_blob_XLSX(ss, sh) {
  SpreadsheetApp.flush(); // reload the Sheeet
  var ssID = ss.getId();
  var shID = sh.getSheetId();
  var url = 'https://docs.google.com/spreadsheets/d/' + ssID + '/export?format=xlsx&gid=' + shID;
  
  var options = {headers: {'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}}
  options.muteHttpExceptions = true; // Make sure this is always set

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to XLS!  Response Code: " + response.getResponseCode());
    return;  
  }

  return response.getBlob();
}

To send it as a CSV file here you go:

function get_blob_CSV(ss, sh) {
  SpreadsheetApp.flush(); // reload the Sheeet
  var ssID = ss.getId();
  var shID = sh.getSheetId();
  var url = 'https://docs.google.com/spreadsheets/d/' + ssID + '/export?format=csv&gid=' + shID;
  
  var options = {headers: {'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}}
  options.muteHttpExceptions = true; // Make sure this is always set

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to XLS!  Response Code: " + response.getResponseCode());
    return;  
  }

  return response.getBlob();
}