Geek asks and answers

Is it possible to find out the result of filter lines in a Google spreadsheet?

I want to know the result of filter linesin the Google Script application, but I don’t get them, I always get all the selection lines or the List. I have researched and it seems that this is not possible using this API. Do you know if this is true or there is another way to get these lines? thank

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

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

    for (var row = 1; row < numberOfRows; row++) {
      if (!isEmpty(activeRow[row], numberOfColumns)) {
        var image = UrlFetchApp.fetch(IMG_BBVA);
        /*var paragraph = bodyReport.appendParagraph("");
        paragraph.addPositionedImage(image).setHeight(100).setWidth(98);
        paragraph.appendText("\r\n");
        paragraph.setAttributes(styleTitle);*/

        //bodyReport.appendImage(image).setHeight(100).setWidth(98); //Incluimos la imagen de cabecera   

        bodyReport.appendParagraph("").addPositionedImage(image).setHeight(100).setWidth(98).setLayout(DocumentApp.PositionedLayout.WRAP_TEXT);
        bodyReport.appendParagraph("          SERVICIOS JURÍDICOS").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[row][col] != "") { //Si el valor es vacio no lo imprimimos
            if (activeRow[0][col] == "NORMA")
              bodyReport.appendParagraph(String(activeRow[row][col]).trim()).setAttributes(styleTextNorma); //Incluimos contenido
            else { 
              bodyReport.appendParagraph(activeRow[0][col] + ":").setAttributes(styleTitle); //Incluimos título
              bodyReport.appendParagraph(String(activeRow[row][col]).trim()).setAttributes(styleText); //Incluimos contenido
            }
            bodyReport.appendParagraph("");
          }
        }
        bodyReport.appendPageBreak(); 
      }
    } 
    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());   
  } 
}
0
source share
2 answers

, , , . , Google Spreadsheet . , , , . , . .

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_BBVA);
          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());   
  } 
}
0

, . - "", script. , , . getDataRange(). GetValues ​​(), , .

0

More articles:


All Articles