CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-querynested-if
I. Я. Newb
I. Я. Newb

Reputation: 329

Nested IF statements with AND condition and multiple nested QUERY

In the "Process Validation" tab of my Google Sheet I have a Data Validation cell with multiple options to pick of. The option chosen triggers a QUERY in the "Master Extraction" tab.

In order to make it "fool-proof" I added a few columns in the "Process Validation" tab with checkboxes to mark the completion of each stage of the process (eg. Weekly Campaign CSV Export - a stage that requires the user to export the report in mention as CSV). If the prior stage has not been completed (boxes have not been checked), the user can not proceed to extracting the next file.

What I need is:

If the user has picked "Weekly Campaign" from the Data Validation and the source file and Weekly campaign fields have not been filled to be displayed the following message:

"Please, clarify if the 'Source File' and 'Weekly Campaign ID' fields in the 'Process Validation' tab have been filled correctly."

If the user chooses another report - eg. "Update lead address", without completing all 4 stages of the "Weekly Campaign" report (not all 4 checkboxes have been checked (= TRUE)) to be displayed the following message:

"Please, clarify if the 'Weekly Campaign CSV Export', 'Weekly Campaign CSV Export Save', 'Weekly Campaign SF Append' and 'Weekly Campaign Success Error TD Upload' processes have been completed."

In case that all stages of the previous process have been completed the required QUERY should be triggered.

What I have up until now is:

=IF(IF(AND(fileToExtract = "Weekly Campaign",
           sourceFile <> "",
           weeklyCampaignID <> ""), 
        QUERY(QUERY({sourceFile_dataRange}, 
                    "SELECT Col" & MATCH("Lead ID",sourceFile_labelRange,FALSE)), 
                    "SELECT Col1, '" & weeklyCampaignID & "', 'Sent' 
                     WHERE Col1 IS NOT NULL 
                     LABEL '" & weeklyCampaignID & "' 'Campaign ID', 
                           'Sent'                     'Status'",1),
        "Please, clarify if the 'Source File' and 'Weekly Campaign ID' fields in the 'Process Validation' tab have been filled correctly."),

 IF(IF(AND(fileToExtract = "Update lead address", 
           weeklyCampaign_CSV_Export = TRUE,
           weeklyCampaign_CSV_Export_Save = TRUE,
           weeklyCampaign_SF_Append = TRUE,
           weeklyCampaign_Success_Error_TD_Upload = TRUE),
       QUERY(QUERY({sourceFile_dataRange},
                   "SELECT Col" & MATCH("Lead ID",         sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Street",          sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("City",            sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("State/Province",  sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Zip/Postal Code", sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Country (RB)",        sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Country",         sourceFile_labelRange,FALSE),1),
                   "SELECT Col1,
                           Col2,
                           Col3,
                           Col4,
                           Col5,
                           Col6,
                           Col7
                     WHERE Col1 IS NOT NULL", 1), 
       "Please, clarify if the 'Weekly Campaign CSV Export', 'Weekly Campaign CSV Export Save', 'Weekly Campaign SF Append' and 'Weekly Campaign Success Error TD Upload' processes have been completed.")))

The formula above results in a #VALUE! error - Function IF parameter 1 expects boolean values. But 'Please, clarify if the 'Source File' and 'Weekly Campaign ID' fields in the 'Process Validation' tab have been filled correctly.' is a text and cannot be coerced to a boolean..

When I tried to run them separately like so:

=IF(IF(AND(fileToExtract = "Weekly Campaign",
        sourceFile <> "",
        weeklyCampaignID <> ""), 
        QUERY(QUERY({sourceFile_dataRange}, 
                    "SELECT Col" & MATCH("Lead ID",sourceFile_labelRange,FALSE)), 
                    "SELECT Col1, '" & weeklyCampaignID & "', 'Sent' 
                     WHERE Col1 IS NOT NULL 
                     LABEL '" & weeklyCampaignID & "' 'Campaign ID', 
                           'Sent'                     'Status'",1),
        "Please, clarify if the 'Source File' and 'Weekly Campaign ID' fields in the 'Process Validation' tab have been filled correctly."),"Dull")

Or like so:

=IF(IF(AND(fileToExtract = "Update lead address", 
       weeklyCampaign_CSV_Export = TRUE,
       weeklyCampaign_CSV_Export_Save = TRUE,
       weeklyCampaign_SF_Append = TRUE,
       weeklyCampaign_Success_Error_TD_Upload = TRUE),
       QUERY(QUERY({sourceFile_dataRange},
                   "SELECT Col" & MATCH("Lead ID",         sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Street",          sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("City",            sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("State/Province",  sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Zip/Postal Code", sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Country (RB)",          sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Country",         sourceFile_labelRange,FALSE),1),
                   "SELECT Col1,
                           Col2,
                           Col3,
                           Col4,
                           Col5,
                           Col6,
                           Col7
                     WHERE Col1 IS NOT NULL", 1), 
       "Please, clarify if the 'Weekly Campaign CSV Export', 'Weekly Campaign CSV Export Save', 'Weekly Campaign SF Append' and 'Weekly Campaign Success Error TD Upload' processes have been completed."),"EVEN DULLER")

I get respectively the following #VALUE! errors:

Error Function IF parameter 1 expects boolean values. But 'Please, clarify if the 'Source File' and 'Weekly Campaign ID' fields in the 'Process Validation' tab have been filled correctly.' is a text and cannot be coerced to a boolean.

And:

Error Function IF parameter 1 expects boolean values. But 'Please, clarify if the 'Weekly Campaign CSV Export', 'Weekly Campaign CSV Export Save', 'Weekly Campaign SF Append' and 'Weekly Campaign Success Error TD Upload' processes have been completed.' is a text and cannot be coerced to a boolean.

Practically the same error when combined.

How can I tackle this?

P.S.

Please, do let me know if you need some dummy data, but, please, have in mind that I have corporate security restrictions that disable me from sharing any sheet with anyone outside of the company.

Upvotes: 0

Views: 304

Answers (1)

I. Я. Newb
I. Я. Newb

Reputation: 329

The first IF had it's argument misplaced.

Instead of:

=IF(IF(AND(fileToExtract = "Weekly Campaign",
        sourceFile <> "",
        weeklyCampaignID <> ""), 
        QUERY(QUERY({sourceFile_dataRange}, ect...

And:

 IF(IF(AND(fileToExtract = "Update lead address", 
           weeklyCampaign_CSV_Export = TRUE,
           weeklyCampaign_CSV_Export_Save = TRUE,
           weeklyCampaign_SF_Append = TRUE,
           weeklyCampaign_Success_Error_TD_Upload = TRUE),
       QUERY(QUERY({sourceFile_dataRange}, ect...

It should have been:

=IF(fileToExtract = "Weekly Campaign",
    IF(AND(sourceFile <> "",
           weeklyCampaignID <> ""), 
        QUERY(QUERY({sourceFile_dataRange}, ect...

And:

 IF(fileToExtract = "Update lead address",
    IF(AND(weeklyCampaign_CSV_Export = TRUE,
           weeklyCampaign_CSV_Export_Save = TRUE,
           weeklyCampaign_SF_Append = TRUE,
           weeklyCampaign_Success_Error_TD_Upload = TRUE),
       QUERY(QUERY({sourceFile_dataRange}, ect...

The whole QUERY looks like this:

=IF(fileToExtract = "Weekly Campaign",
    IF(AND(sourceFile <> "",
           weeklyCampaignID <> ""), 
        QUERY(QUERY({sourceFile_dataRange}, 
                    "SELECT Col" & MATCH("Lead ID",sourceFile_labelRange,FALSE)), 
                    "SELECT Col1, '" & weeklyCampaignID & "', 'Sent' 
                     WHERE Col1 IS NOT NULL 
                     LABEL '" & weeklyCampaignID & "' 'Campaign ID', 
                           'Sent'                     'Status'",1),
        "Please, clarify if the 'Source File' and 'Weekly Campaign ID' fields in the 'Process Validation' tab have been filled correctly."),

 IF(fileToExtract = "Update lead address",
    IF(AND(weeklyCampaign_CSV_Export = TRUE,
           weeklyCampaign_CSV_Export_Save = TRUE,
           weeklyCampaign_SF_Append = TRUE,
           weeklyCampaign_Success_Error_TD_Upload = TRUE),
       QUERY(QUERY({sourceFile_dataRange},
                   "SELECT Col" & MATCH("Lead ID",         sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Street",          sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("City",            sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("State/Province",  sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Zip/Postal Code", sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Country (RB)",        sourceFile_labelRange,FALSE) & ",
                           Col" & MATCH("Country",         sourceFile_labelRange,FALSE),1),
                   "SELECT Col1,
                           Col2,
                           Col3,
                           Col4,
                           Col5,
                           Col6,
                           Col7
                     WHERE Col1 IS NOT NULL", 1), 
       "Please, clarify if the 'Weekly Campaign CSV Export', 'Weekly Campaign CSV Export Save', 'Weekly Campaign SF Append' and 'Weekly Campaign Success Error TD Upload' processes have been completed.")))

Upvotes: 1

Related Questions