CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
bjbooks
bjbooks

Reputation: 1

What is wrong with this google sheets formula?

I want to use this formula in google sheets:

=QUERY({sheet1!A2:D;sheet2!A2:D},"select * where Col4>=65 order by Col4 desc")

but I get "Formula parse error" basically I want to get data from multiple sheets tabs and display it into one sheet but I want the data to be sorted and only get results where Col4 is >= to 65 or even better it would be to only display the top 3 results from each tab

I tried that by using

=sortn({sheet1!A2:D;sheet2!A2:D};5;0;4;0)

but this display top 5 results from both tabs overall but I would like if I could get top 3 results from each tab.

Upvotes: 0

Views: 101

Answers (2)

bjbooks
bjbooks

Reputation: 1

Well hello, after much trial and error I managed to get a formula that does exactly what I want there is just one simple problem it's so long .... but maybe it can help others too or someone can come up with a way to simplify it.

=SORT(IFERROR({iferror(query({sheet1!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3";0);A2:D2/0); iferror(query({sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3";0);A2:D2/0)});4;0)

This formula will display 3 results from each tab like I wanted and will sort it overall all the data displayed but like I said it's very long already and if I want to add more tabs it will get even longer. But it's the only solution I could come up with that does everything I needed automatically. I hope it can help people and if there is someone who can simplify it that would be an even greater help.

Upvotes: 0

JPV
JPV

Reputation: 27292

You may want to try a custom function ?

function filterDataBiggerThen(rangeA1, column, value, limit) {
  const ss = SpreadsheetApp.getActive();
  const activeSheet = ss.getActiveSheet();
  let output = [];
  ss.getSheets().filter(sh => sh.getName() !== activeSheet.getName())
    .map(sh => sh.getRange(rangeA1).getValues().sort((a, b) => b[column - 1] - a[column - 1])
      .forEach((row, i) => {
        if (row[column - 1] >= value && i < limit) output.push(row)
      })
    )
  return output && output.length ? output : "No data found";
}

In your spreadsheet you can use this function as a formula

=filterDataBiggerThen("A2:D"; 4; 65; 3)

Upvotes: 1

Related Questions