Google Apps Script that loops through a filter and sends an e-mail with a PDF?

Question

I have data from a questionnaire (20K rows) that I need to share with the store managers (report) of our shops (400 shops). I managed to write a script that sends a pdf of my sheet to a list of e-mail addresses. But I'm stuck on writing the loop for the filter, since I can't get the setVisibleValues(values) function to work for FilterCriteriaBuilder. The setHiddenValues(values) function works, but I can't figure out how to combine that with the loop.

Sample of my Google Sheet

See below for my current code:

/**
 * Filtersheet by location
 */
function FilterSheet() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('Data')
  spreadsheet.getRange('F1').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['Amsterdam, Rotterdam'])
  .build();
  spreadsheet.getFilter().setColumnFilterCriteria(6, criteria);
};

/**

 * Send pdf of currentspreadsheet
 */
function SendPdf() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var spreadsheet = SpreadsheetApp.getActive().getSheetByName('Adres');
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  blob.setName(ss.getName() + ".pdf");

  var startRow = 2; // First row of data to process
  var numRows = 2; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = spreadsheet.getRange(startRow, 1, numRows, 2);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = 'I hearby send you the overview of your data'
    var subject = 'Overview of data';
    MailApp.sendEmail(emailAddress, subject, message,{
      attachments:[blob]});
  }
 }

Answer 1

getValues() returns the values of all range's cells no matter if they are shown or hidden.

Use a loop and isRowHiddenByFilter(rowPosition) to reap out all the filtered values. You could use Array.prototype.push to add the values to a new array or use Array.prototype.splice to modify the array holdin the values returned by getValues()

Related

• How to use in Google Sheets setValue only for range of filtered rows (getRange for not hidden cells)?

Answer 2

I managemed to solve the problem.

This script takes a google spreadsheet with 2 sheets,one with Data and one with a combination EmailAdresses. It sends a filtered list (filter column F) of sheet Data to the corresponding salon (location) in sheet Emailadresses (var mode email). Additionally, it has the option to "store" the pdf's in your google drive (var mode store)

*/

function construct() {
  // settings:
  //var mode = "store";
  var mode = "email";

  // get list of all salons and email
  var salonList = SpreadsheetApp.getActive().getSheetByName('EmailAdressen');
  // set endvar for loop
  var endRow = salonList.getLastRow();

  // loop trough the rows to get the Salon name and the corresponding email
  for(i=1;i<=endRow;i++){
    var salonName = salonList.getRange(i,2).getValue();
    var email = salonList.getRange(i,1).getValue();    

    // create an array with all salons that should be hidden (we cant pick which one to show, so we have to go the other way around...)
    var filterArray = [];
    // create array with all salons to hide
    for(c=1;c<=endRow;c++){
      // get value from email list, check if it is not the current selected one and if so add it to the list to filter out
      salonFilterName = salonList.getRange(c,2).getValue();
      if(salonFilterName != salonName) {
        filterArray.push(salonFilterName);
      }
    } // end for c

    // filter the list with the array we just created
    var spreadsheet = filterList(filterArray);

    if(mode == "email"){
      // export to PDF
      var pdf = exportToPdf(spreadsheet);
      // email to email address belonging to this salon
      emailToAddress(email, pdf);

    } // end if
    if(mode == "store"){
      StorePdf(spreadsheet, salonName);
    }

  } // end for i
  return;
}

function filterList(salonNameArray) {

  // select data sheet
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('Data');

  // first remove all existing filters to make sure we are on a clean sheet
  if(spreadsheet.getFilter()){
    spreadsheet.getFilter().remove(); 
  }

  // create the filter
  spreadsheet.getRange('F:F').createFilter();

  // set criteria for filter with array passed from construct
  var criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(salonNameArray).build();

  // apply filter
  spreadsheet.getFilter().setColumnFilterCriteria(6, criteria);
  return spreadsheet;
}

function exportToPdf(ss) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('Data');
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  blob.setName(ss.getName() + ".pdf");

  return blob;
 }

function StorePdf(ss, salonName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('Data');
  var blob = DriveApp.getFileById(ss.getId()).getBlob();
  blob.setName(salonName + "_" + Utilities.formatDate(new Date(), "GMT+1", "ddMMyyyy")+".pdf");

  DriveApp.createFile(blob);
  return;
 }

function emailToAddress(email, pdf) {
  MailApp.sendEmail(email, 'Type here the subject', 'Type here the body',{
      attachments:[pdf]});
  return;
}