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: "[email protected]",
    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;
}

CodePudding user response：

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: "[email protected]",
    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: "[email protected]",

this way:

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