Can I pull out a single cell with query in google sheets?

Question

I'm trying to find a way to (I think) get a single cell out as part of a query that also returns columns, I don't know if it's even possible. Brief explanation - every quarter we get v. detailed audit reports in via google sheets in 6 spreadsheets of between 20-45 case files each, each with their own separate tab/sheet within that. I've managed to use query to pull out and collate the major issues from all of them in a new sheet for analysis using

=QUERY({IMPORTRANGE(A3,"Case File 1!F18:M");IMPORTRANGE(A3,"Case File 2!F18:M");IMPORTRANGE(A3,"Case File 3!F18:M")},"Select Col1,Col4,Col7 where Col4 contains 'concern'")

This gives me a neat list of all the concern point narratives against the specific criteria they come under, to do funky things with, but what I haven't managed to do is actually get anything that says 'these concern points are from Case File 1' or similar. There could be 0, 1, 5 concern points, so I never know how many rows it's going to return. Is there an easy way to go 'select this one cell (with the organisation name on) as well as the concern bits and stick the name next to all of them? I doubt it, but any ideas welcome, I don't want to have to run the queries separately and collate them later. Can't attach a copy of the sheets unfortunately, it's all confidential info. I've tried adding Col3 to the mix, which is the one with the name on (and some other extraneous info that looks messy, but ah well) but this only returns the name bit for the first sheet on the top - I haven't got the hang of headers so that might be why? Cheers, sorry for the length, first question and I'm not sure what you need to know. Meg

Aresvik Aresvik · Accepted Answer · 2021-07-19T11:10:55

Try:

=arrayformula(
query({
query({iferror(IMPORTRANGE(A3,"Case File 1!F18:F")/0,"Case File 1"),IMPORTRANGE(A3,"Case File 1!F18:M")},"where Col5 contains 'concern'",0);
query({iferror(IMPORTRANGE(A3,"Case File 1!F18:F")/0,"Case File 2"),IMPORTRANGE(A3,"Case File 2!F18:M")},"where Col5 contains 'concern'",0);
query({iferror(IMPORTRANGE(A3,"Case File 1!F18:F")/0,"Case File 3"),IMPORTRANGE(A3,"Case File 3!F18:M")},"where Col5 contains 'concern'",0)
},"select Col1,Col2,Col5,Col8",0)
)

This option places the text column at the end (and just one query):

=arrayformula(
    query({
    IMPORTRANGE(A3,"Case File 1!F18:M"),iferror(IMPORTRANGE(A3,"Case File 1!F18:F")/0,"Case File 1");
    IMPORTRANGE(A3,"Case File 2!F18:M"),iferror(IMPORTRANGE(A3,"Case File 2!F18:F")/0,"Case File 2");
    IMPORTRANGE(A3,"Case File 3!F18:M"),iferror(IMPORTRANGE(A3,"Case File 3!F18:F")/0,"Case File 3")
    },"select Col1,Col4,Col7,Col9 where Col4 contains 'concern' ",0))

Can I pull out a single cell with query in google sheets?

1 Answers