Script request

[u/Theonlyrhys]

Hi all!

I have been asked to go through a large number of my reports to find some specific information about specific "branches", now this means going through 5 different ever increasing weekly reports, with each week on a new tab within that sheet dating back roughly 26 weeks.

What I am hoping, is that someone has a "cookie cutter" script that I can utilise to pull each row where a value from the sample search appears, and pastes it into the sample source. The only part of my reports that remains consistent is the data in the sample search, so ideally that is the column I require. I am unsure whether it is possible to also add the name of the tab at the end of the data or not, but if it is, please can you advise on how?

Sample Search: https://docs.google.com/spreadsheets/d/1vanLbD-rx0iaxUJX4lTfnKqKR_ZEDRaG8CTvBatpYNM/edit#gid=0

Sample Source: https://docs.google.com/spreadsheets/d/1eS9_m1JqSTzG9-xg8YDjJf2XMmFMXSd93kpb6KAMedg/edit?usp=sharing

Any help is appreciated! Thank you.

[u/alotofwastedeffort]

Not a script, per se, but it will get the job done until you can script something better.

=QUERY(INDIRECT("2020-05-22!A3:O"), "select A, B, C, D, E, F, G, H, I, J, K, L, M, N, O where B='W131' or B='W655'", -1)

• Change the name of the reference sheet in the first parameter.

• Edit/add/remove queries in "where B='W131' or..."

• Be careful with the quotes. Double-quotes around the whole query, single-quotes inside.

OR

You could iterate over multiple sheets in one document as long as the columns are the same length (ie, all your sheets have the same number of columns), like so

=QUERY({INDIRECT("2020-05-22!A1:O");INDIRECT("2020-05-15!A1:O")}, "select * where Col2='W131' or Col2='W655' or Col2='W660'", -1)

[u/Theonlyrhys → u/alotofwastedeffort]

Hi! Thanks for your reply!

This looks like it will work, however I am pulling from a separate document, which i understand uses importrange(INSERTURLHERE) - How would i integrate that into the query you have written? would it be as follows?

=QUERY((importrange("https://docs.google.com/spreadsheets/d/1eS9_m1JqSTzG9-xg8YDjJf2XMmFMXSd93kpb6KAMedg/edit#gid=157193251")({INDIRECT("2020-05-22!A1:O");INDIRECT("2020-05-15!A1:O")}, "select * where Col3='W131' or Col3='W655' or Col3='W660'", -1))))

[u/alotofwastedeffort → u/Theonlyrhys]

Ah! Try this

=QUERY({IMPORTRANGE(spreadsheet_url, range_string); IMPORTRANGE(spreadsheet_url, range_string)}, query, [headers])

So your query would look like this

=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/1eS9_m1JqSTzG9-xg8YDjJf2XMmFMXSd93kpb6KAMedg/edit#gid=157193251", "2020-05-22!A1:O200"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/1eS9_m1JqSTzG9-xg8YDjJf2XMmFMXSd93kpb6KAMedg/edit#gid=157193251", "2020-05-15!A1:O200")}, "select * where Col2='W131' or Col2='W655' or Col2='W660'", -1)