Do you know how to access the rows resulting from applying a filter in a spreadsheet using Google App Script?

1

Good, I want to access the rows resulting from applying a filter on a sheet spreadsheet but it is impossible, I can not do it in any way, it always takes me all. I thought it read in some pages that at the moment there was no way (long entries) but I do not know if now it can be done in some way, I at least have not found the way. Thank you very much.

    
asked by Richi 12.02.2017 в 23:18
source

2 answers

0

In the end I have achieved it, although it will not be the most usual way it has served me for what I wanted. What I do is before processing the sheet I select it whole and apply a specific background color to all the rows, luckily Google Spreadsheet only applies it to the rows resulting from applying the filter. Therefore when processing the sheet the only thing I do is ask for the background color of each row and so I know what are the result of the filter. At the end of the process I return to leave all the rows with the original background color. I already tell you that it is probably not the most usual way but it has served me and while Google does not offer functions to access the filters ...

function docReport() {
  try {
    splashScreen("Generando informe...");    
    var activeSheet = SpreadsheetApp.getActiveSheet();    
    var numberOfColumns = activeSheet.getLastColumn();
    var numberOfRows = activeSheet.getLastRow(); 
    var activeRow, rowRange;
    var titleRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues();

    //Set los fondos a otro color para identificar las líneas a las líneas que tienen filtro
    activeSheet.setActiveSelection("2:" + activeSheet.getLastRow()).setBackground("#fffff9"); 

    var docReport =  DocumentApp.create(REPORT_FILE_NAME);
    var bodyReport = docReport.getBody();
    bodyReport.setAttributes(stylePage);

    for (var row = 2; row <= numberOfRows; row++) { //Empieza en 2 ya que no incluyo la fila de los títulos
      rowRange = activeSheet.getRange(row, 1, 1, numberOfColumns);
      if (rowRange.getBackground() == "#fffff9") { //If it has #fffff9 background, it means that it is a filtered row
        activeSheet.setActiveSelection(row + ":" + row).setBackground("#ffffff"); //Volvemos a dejar los fondos en blanco
        activeRow = rowRange.getValues();
        if (!isEmpty(activeRow[0], numberOfColumns)) {
          var image = UrlFetchApp.fetch(IMG);
          bodyReport.appendParagraph("").addPositionedImage(image).setHeight(100).setWidth(98).setLayout(DocumentApp.PositionedLayout.WRAP_TEXT);
          bodyReport.appendParagraph("          S.J.").setAttributes(styleTextBlue); 
          bodyReport.appendParagraph(DATE_REPORT + "\r\n\r\n").setAttributes(styleDate); 

          for (var col = 1; col < numberOfColumns; col++) { //Ignoramos la columna 1 que es el contador
            if (activeRow[0][col] != "") { //Si el valor es vacio no lo imprimimos
              if (col == COLUMN_NORMA)
                bodyReport.appendParagraph(String(activeRow[0][col]).trim()).setAttributes(styleTextNorma); //Incluimos contenido
              else { 
                bodyReport.appendParagraph(titleRow[0][col]).setAttributes(styleTitle); //Incluimos título
                bodyReport.appendParagraph(String(activeRow[0][col]).trim()).setAttributes(styleText); //Incluimos contenido
              }
              bodyReport.appendParagraph("");
            }
          }
          bodyReport.appendPageBreak(); 
        }
      }
    } 
    activeSheet.setActiveSelection("2:" + activeSheet.getLastRow()).setBackground("#ffffff"); //Volvemos a dejar los fondos en blanco menos primera fila
    bodyReport.appendPageBreak();     
    docReport.saveAndClose();
    MailApp.sendEmail(Session.getActiveUser().getEmail(), "Informe " + REPORT_FILE_NAME + " generado", 
                      'Se ha creado un nuevo informe "' + REPORT_FILE_NAME + '" en su unidad Google Drive: \n\r' + docReport.getUrl());   
    SpreadsheetApp.flush();
    SpreadsheetApp.getUi().alert('Se ha creado un nuevo informe "' + REPORT_FILE_NAME + '" en su unidad Google Drive \r\n');
  } catch(e) {
    Logger.log("ERROR in function createPdf \r\nMessage: " + e.message + "\r\nFile gs: " + e.fileName + "\r\nLine: " + e.lineNumber)
    Logger.log("\r\nUser: " + Session.getActiveUser().getEmail() + ", Date: " + Utilities.formatDate(new Date(), "GMT+1", "yyyyMMdd'_'HH:mm:ss"));
    MailApp.sendEmail(EMAIL_DEV, SUBJECT_MAIL, Logger.getLog());   
  } 
}
    
answered by 06.03.2017 / 00:35
source
2

Short answer

As I understand it is not yet possible. The alternative is to emulate the filter criteria using JavaScript.

Explanation

The Spreadsheet service in Google Apps Script does not yet have classes for filters or methods to get the results of them. It is worth mentioning that Sheets API 4 if you have methods for both basic filters and filter views, but these are for modifying the filters themselves, not to get the results they return in the user interface.

From Updating spreadsheets , Spanish translation of titles is mine,

  

Object (Object)

     

Filters ( filter   views & the basic   filter )

     

Add duplicate

     

AddFilterViewRequest
DuplicateFilterViewRequest

     

Update / Set (Update / set)

     

UpdateFilterViewRequest
SetBasicFilterRequest

     

Delete (Clear)

     

ClearBasicFilterRequest

    
answered by 13.02.2017 в 00:05