What is wrong with this google sheets formula?

0
votes

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.

2
google-sheetsgoogle-sheets-formula
I’m voting to close this question because it isn't a programming problem. It might be suitable for webapps.stackexchange.com – Quentin
@Quentin there are 8013 questions asked regarding google sheets formula. I don't think it should be closed for this reason. – soMario
There is nothing wrong with it. I can create two sheets, plop in some data, cut and paste your formula into a third sheet and get success. If I play with the data on the first two sheets and get rid of some columns or whatnot to make it so that it's not pointing to real data, then I do get some errors. But never a parse error. – pwilcox
@pwilcox thanks for the reply but i figured what actually gave me the parse error, it's the ' , "select... ' the , in it after I changed it to ; it worked than my question is can I change that query to give 3 result from each tab ? because if I use Limit 3 ... same thing as on sortn. i really wonder if it's possible to limit the results in a way to get only N no. from each tab – bjbooks

2 Answers

1
votes

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)
0
votes

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.