Google Spreadsheetdagi filtrlarning natijalarini bilish mumkinmi?

Google App skriptida filtr ning satr natijasi ni bilmoqchi edim, lekin ularni qo'lga kirita olmayman, har doim men tanlov yoki varaqning barcha satrlarini olaman. Men tekshirib chiqdim va bu API bilan amalga oshishi mumkin emas. Bu to'g'ri yoki yo'qligini bilasizmi yoki bu satrlarni olish uchun boshqa yo'l bormi ?. Rahmat

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
Done @CedricDruck, thx
qo'shib qo'ydi muallif Richi, manba
Done @CedricDruck, thx
qo'shib qo'ydi muallif Richi, manba
parchani iltimos qiling.
qo'shib qo'ydi muallif Cedric Druck, manba
parchani iltimos qiling.
qo'shib qo'ydi muallif Cedric Druck, manba

6 javoblar

Hech qanday bo'lishi mumkin emas Spreadsheet filter ilovalar skriptida olinishi mumkin bo'lmagan maxsus "view". Shunga qaramay, uning parametrlarini bilsangiz, filtr ko'rinishini qo'lda qayta tiklash mumkin. Barcha ma'lumotlarni olish va keyin filter funktsiyasi .

0
qo'shib qo'ydi

Hech qanday bo'lishi mumkin emas Spreadsheet filter ilovalar skriptida olinishi mumkin bo'lmagan maxsus "view". Shunga qaramay, uning parametrlarini bilsangiz, filtr ko'rinishini qo'lda qayta tiklash mumkin. Barcha ma'lumotlarni olish va keyin filter funktsiyasi .

0
qo'shib qo'ydi

Google'da Fikr reytingi va Google tomonidan tasdiqlangan :

You can use the new Google Sheets advanced service to get the filtered rows, clear all filters,... See this blog post for more info: https://gsuite-developers.googleblog.com/2017/04/using-google-sheets-filters-in-add-ons.html

for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].properties.sheetId == sheetId) {
      var data = sheets[i].data;
      var rows = data[0].rowMetadata;
      for (var j = 0; j < rows.length; j++) {
        if (rows[j].hiddenByFilter) hiddenRows.push(j);
      }
    }
  }
  return hiddenRows;
}

0
qo'shib qo'ydi

Google'da Fikr reytingi va Google tomonidan tasdiqlangan :

You can use the new Google Sheets advanced service to get the filtered rows, clear all filters,... See this blog post for more info: https://gsuite-developers.googleblog.com/2017/04/using-google-sheets-filters-in-add-ons.html

for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].properties.sheetId == sheetId) {
      var data = sheets[i].data;
      var rows = data[0].rowMetadata;
      for (var j = 0; j < rows.length; j++) {
        if (rows[j].hiddenByFilter) hiddenRows.push(j);
      }
    }
  }
  return hiddenRows;
}

0
qo'shib qo'ydi

Nihoyat, men o'z maqsadimga erishishim mumkin, lekin odatiy yechim emas, lekin u ishlaydi. Jarayondan oldin jadvalni barcha satrlarni tanladim va fon rangi qo'ydim, Google Spreadsheet faqat fon rangini filtrlangan qatorlarga o'rnatdi. So'ngra barcha varaqlarni ishlasa-da, men uning fonini so'rashim uchun filtrlangan satrlarni bilaman. Umid qilamanki, bu yechim sizga yoqadi va sizga foydali bo'ladi. Rahmat.

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
qo'shib qo'ydi

Nihoyat, men o'z maqsadimga erishishim mumkin, lekin odatiy yechim emas, lekin u ishlaydi. Jarayondan oldin jadvalni barcha satrlarni tanladim va fon rangi qo'ydim, Google Spreadsheet faqat fon rangini filtrlangan qatorlarga o'rnatdi. So'ngra barcha varaqlarni ishlasa-da, men uning fonini so'rashim uchun filtrlangan satrlarni bilaman. Umid qilamanki, bu yechim sizga yoqadi va sizga foydali bo'ladi. Rahmat.

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
qo'shib qo'ydi