CODEWITHSUNDEEP
google-sheetsgoogle-sheets-query
I. Я. Newb
I. Я. Newb

Reputation: 329

Auto-filling empty cells with Google Sheets' query

I have a table with data that I want to run a QUERY from.

In the output tab I need just one column from the data tab, but also I have 3 empty columns in the output tab, that are not in the data tab, that I need to be filled automatically, based on conditions, preferably with the QUERY.

I am using a simple QUERY formula to load the data that I have in the source tab to the output tab.

=QUERY('Source'!$A$1:$X, "SELECT A WHERE F IS NOT NULL", 1)

The issue is that I can not have any other formulas in the output sheet, rather than the QUERY itself, as some issues arise when exporting Google Sheet that contains formulas to .CSV.

Regardless if the above is true or not, these are the rules...

This is the output that I need to have:

+---------+------------+-------------------+-----------------+
| Country | Researched | Status            | Reason          |
+---------+------------+-------------------+-----------------+
| UK      | TRUE       | In Progress       |                 |
+---------+------------+-------------------+-----------------+
| US      | TRUE       | Unable to Proceed | Not a UK member |
+---------+------------+-------------------+-----------------+

Column 1 is what the QUERY extracts from the source.

Columns 2 to 4 are the ones that I need to create with the QUERY.

The value of each cell in those columns depends on column 1, except for column 2 that needs to have the value "TRUE" for each record.

Is it possible to implement multiple conditions in the QUERY itself, that will fill the empty columns in the output tab, based on conditions?

Upvotes: 0

Views: 1808

Answers (1)

player0
player0

Reputation: 1

=QUERY({QUERY(Source!$A$1:$X, 
        "select A, 'TRUE' 
         where F is not null
         label 'TRUE' 'Researched'", 1),
        QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(
        QUERY(Source!$A$2:$X, 
        "select A 
         where F is not null", 0), 
        {"UK", "In Progress", ""}, {2, 3}, 0), 
        {"Unable to Proceed", "Not a UK member"})), 
        "select * 
         label Col1 'Status', Col2 'Reason'", 0)}, , 0)

Upvotes: 1

Related Questions