Retrieving UI filtered data values.

[Diana Ramos Fusther Correa]

Hello again! 

I'm working in automating a report draft from google sheets to google docs.

For this workflow my users go to the 'Evaluation table' and use the sheet filters to select the title and user of the report they want to create. 

Thing is when I use my code to copy for the report it always gets the first line of data of all the table instead of the filtered view. I just need to retrieve this one line of filterred data.

Is there a way to retrieve the rows that are shown on the UI? That the users manually filtered?

[Juan Ozcariz]

Hi, Diana,

These two links may be useful to you:

https://tanaikech.github.io/2019/07/28/retrieving-values-from-filtered-sheet-in-spreadsheet-using-google-apps-script/

https://www.labnol.org/code/20611-get-hidden-and-filtered-rows-in-google-sheets

Good luck!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f50c90b4-7b3f-4f4e-8b33-fcbc9ed2c168n%40googlegroups.com.

[Halicate]

More simple...

Here is a very silly example. You just have to make a copy in your Drive to see the code.

To test the operation it is necessary to check/uncheck the box below to force the function to be executed

Code:

function FilterEx() {

  

  var sheet = SpreadsheetApp.getActiveSheet();

  var data = sheet.getDataRange().getValues();

  for (var d = 1; d < data.length+1; d++) {

    

    if (!sheet.isRowHiddenByFilter(d + 1)) {

      break

    }    

  }

  return d+1

}

[Diana Ramos Fusther Correa]

Ok so in the end this is what I ended up doing.

Since I used the .showHiddenValues to get the hidden criteria (aka what users didn't select). I made this piece of code that compares the original list array to the hidden criteria array and takes out coincidences until only the original selections are left. Inelegant probs, but it works.

for (let i=0;i<User_hiddenCriteria.length;i++){

 let prov_value = User_hiddenCriteria[i];

 for (let j=0; j<UserList.length; j++){

   let prov_uvalue = UserList[j][0];

   if (prov_value === prov_uvalue){

     index_found = j;

     UserList.splice(j,1);