Google Sheet: Save PDF with named range function and then send an email via button

Question

Can someone help me to combine these two codes? I want to have a function to save and email the pdf from the spreadsheet. These two code works, but I can't even combine them.

Here's the code for SAVE as PDF:

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Export all sheets', 'exportAsPDF')
    .addItem('Export selected area', 'exportPartAsPDF')
    .addItem('Export predefined area', 'exportNamedRangesAsPDF')
    .addToUi()
}

function exportAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var blob = _getAsBlob(spreadsheet.getUrl())
  _exportBlob(blob, spreadsheet.getName())
}

var timeZone = Session.getScriptTimeZone();
var date = Utilities.formatDate(new Date(), timeZone, "yyyy");

function getFilename() {

    var ss = SpreadsheetApp.getActive();
  
    var sheet = ss.getSheetByName('Manifest');
    var filename = "FORM137 - " + ss.getRange("REPORT CARD!D12").getValue() + " - ARCS" + date;

    return filename;
}

function _exportBlob(blob, fileName) {
  
  //TimeStamp
  var timeZone = Session.getScriptTimeZone();
  var date = Utilities.formatDate(new Date(), timeZone, "yyyy");
  
  // get the active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // get a value from the B2 cell on sheet "Sheet" as new filename
  var fileName = "FORM137 - " + ss.getRange("REPORT CARD!D12").getValue() + " - ARCS" + date;
  
  // get the spreadsheet ID
  var ssID = ss.getId();
  
  // get the spreadsheet file by ID
  var ssFile = DriveApp.getFileById(ssID);
 
  blob = blob.setName(fileName)
  
  var parentFolder = DriveApp.getFileById(ss.getId()).getParents().next();
  var subFolder = parentFolder.getFoldersByName("CARDS").next();
 
  var files = subFolder.getFilesByName(getFilename());
  if (files.hasNext()) {
    files.next().setTrashed(true);
  }
  var pdfFile = subFolder.createFile(blob);
  
  
  if (pdfFile) {
    const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + fileName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
  }
}


function _getAsBlob(url, sheet, range) {
  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
   var exportUrl = url.replace(/\/edit.*$/, '')      + '/export?exportFormat=pdf&format=pdf'
      + '&size=folio'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=0.5'              
      + '&bottom_margin=0.5'          
      + '&left_margin=0.5'             
      + '&right_margin=0.5'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=false'
      + '&gridlines=true'
      + '&fzr=FALSE'      
      + sheetParam
      + rangeParam

      
  Logger.log('exportUrl=' + exportUrl)
  var response = UrlFetchApp.fetch(exportUrl, {
    headers: { 
      Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
    },
  })
  
  return response.getBlob()
}


function exportPartAsPDF(predefinedRanges) {
  var ui = SpreadsheetApp.getUi()
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  
  var selectedRanges
  var fileSuffix
  if (predefinedRanges) {
    selectedRanges = predefinedRanges
    fileSuffix = '-predefined'
  } else {
    var activeRangeList = spreadsheet.getActiveRangeList()
    if (!activeRangeList) {
      ui.alert('Please select at least one range to export')
      return
    }
    selectedRanges = activeRangeList.getRanges()
    fileSuffix = '-selected'
  }
  
  if (selectedRanges.length === 1) {
    // special export with formatting
    var currentSheet = selectedRanges[0].getSheet()
    var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet, selectedRanges[0])
    
    var fileName = spreadsheet.getName() + fileSuffix
    _exportBlob(blob, fileName)
    return
  }
  
  var tempSpreadsheet = SpreadsheetApp.create(spreadsheet.getName() + fileSuffix)
  var tempSheets = tempSpreadsheet.getSheets()
  var sheet1 = tempSheets.length > 0 ? tempSheets[0] : undefined
  SpreadsheetApp.setActiveSpreadsheet(tempSpreadsheet)
  tempSpreadsheet.setSpreadsheetTimeZone(spreadsheet.getSpreadsheetTimeZone())
  tempSpreadsheet.setSpreadsheetLocale(spreadsheet.getSpreadsheetLocale())
  
  for (var i = 0; i < selectedRanges.length; i++) {
    var selectedRange = selectedRanges[i]
    var originalSheet = selectedRange.getSheet()
    var originalSheetName = originalSheet.getName()
    
    var destSheet = tempSpreadsheet.getSheetByName(originalSheetName)
    if (!destSheet) {
      destSheet = tempSpreadsheet.insertSheet(originalSheetName)
    }
    
    Logger.log('a1notation=' + selectedRange.getA1Notation())
    var destRange = destSheet.getRange(selectedRange.getA1Notation())
    destRange.setValues(selectedRange.getValues())
    destRange.setTextStyles(selectedRange.getTextStyles())
    destRange.setBackgrounds(selectedRange.getBackgrounds())
    destRange.setFontColors(selectedRange.getFontColors())
    destRange.setFontFamilies(selectedRange.getFontFamilies())
    destRange.setFontLines(selectedRange.getFontLines())
    destRange.setFontStyles(selectedRange.getFontStyles())
    destRange.setFontWeights(selectedRange.getFontWeights())
    destRange.setHorizontalAlignments(selectedRange.getHorizontalAlignments())
    destRange.setNumberFormats(selectedRange.getNumberFormats())
    destRange.setTextDirections(selectedRange.getTextDirections())
    destRange.setTextRotations(selectedRange.getTextRotations())
    destRange.setVerticalAlignments(selectedRange.getVerticalAlignments())
    destRange.setWrapStrategies(selectedRange.getWrapStrategies())
  }
  
  // remove empty Sheet1
  if (sheet1) {
    Logger.log('lastcol = ' + sheet1.getLastColumn() + ',lastrow=' + sheet1.getLastRow())
    if (sheet1 && sheet1.getLastColumn() === 0 && sheet1.getLastRow() === 0) {
      tempSpreadsheet.deleteSheet(sheet1)
    }
  }
  
  exportAsPDF()
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet)
  DriveApp.getFileById(tempSpreadsheet.getId()).setTrashed(true)
}

function exportNamedRangesAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var allNamedRanges = spreadsheet.getNamedRanges()
  var toPrintNamedRanges = []
  for (var i = 0; i < allNamedRanges.length; i++) {
    var namedRange = allNamedRanges[i]
    if (/^print_area_.*$/.test(namedRange.getName())) {
      Logger.log('found named range ' + namedRange.getName())
      toPrintNamedRanges.push(namedRange.getRange())
    }
  }
  if (toPrintNamedRanges.length === 0) {
    SpreadsheetApp.getUi().alert('No print areas found. Please add at least one \'print_area_1\' named range in the menu Data > Named ranges.')
    return
  } else {
    toPrintNamedRanges.sort(function (a, b) {
      return a.getSheet().getIndex() - b.getSheet().getIndex()
    })
    exportPartAsPDF(toPrintNamedRanges)
  }
}

And here's the code for the send email function:

function emailSpreadsheetAsPDF(url, sheet, range) {

    var email = ""; // Enter the required email address here

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getActiveSheet(); // Enter the name of the sheet here

    var subject = "PDF generated from spreadsheet " + ss.getName();

    var body = "\n Attached is a PDF copy of the sheet " + sheet.getName() + " in the " + ss.getName() + " spreadsheet.";

    // Base URL
    var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

  
  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
  
    var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
         + '&size=folio'
         + '&portrait=true'
         + '&fitw=true'       
         + '&top_margin=0.5'              
         + '&bottom_margin=0.5'          
         + '&left_margin=0.5'             
         + '&right_margin=0.5'           
         + '&sheetnames=false&printtitle=false'
         + '&pagenum=false'
         + '&gridlines=true'
         + '&fzr=FALSE'      
         + '&gid='; // the sheet's Id
         + sheetParam
         + rangeParam

    var token = ScriptApp.getOAuthToken();

    var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
            headers : {
                'Authorization' : 'Bearer ' + token
            }
        }).getBlob().setName(sheet.getName() + ".pdf");

    // Uncomment the line below to save the PDF to the root of your drive. 
    //  var newFile = DriveApp.createFile(response).setName(sheet.getName() + ".pdf")

    if (MailApp.getRemainingDailyQuota() > 0)
        GmailApp.sendEmail(email, subject, body, {
            htmlBody : body,
            attachments : [response]
        });
}

Here's the sample image on what I'm trying to achieve.

I'm using the exportNamedRangesAsPDF function to save the PDF file. I need to make the PDF file on sending email looks like the Sample 2 layout.

Can someone help me with this? Thank you in advance!

Answer 1

The following code can be added as separate function or inline with your existing code to send email. While I am not completely clear what you try to accomplish but I think my understanding might help you a little.

Also, remove the code that moves the file to Trash as you need the file in folder to get it from there and export or run the function before Trashing the pdf.

function generatePdf() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  // Get folder containing spreadsheet and PDF
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }

  var pdfName = filename + '.pdf';
  var email = //get from data in spreashseet or any other data source;
    // Subject of email message
    const subject = `SUBJECT: ${pdfName}`;
  // Email Body can  be HTML too with your logo image
  const body = "BODY";
  var files = folder.getFilesByName(pdfName);
  if (files.hasNext()) {
    if (MailApp.getRemainingDailyQuota() > 0)
      GmailApp.sendEmail(email, subject, body, {
        htmlBody: body,
        attachments: [files.next().getAs(MimeType.PDF)]
      });
  };
};

sourceSpreadsheet.toast("Reports Emailed ", "Success");

}