[Help] How to create formula for single data but with multiple values?

Hello, I’m creating a scenario wherein I want to generate a document with dynamic content.

THE SCENARIO
Step 1: Google Sheet - Watch New Rows

This Google Sheet has columns that have values TRUE and FALSE

For example:

  • Column 1: TRUE
  • Column 2: FALSE
  • Column 3: TRUE
  • Column 4: TRUE
  • etc

Step 2: Google Sheet - Get Range Values

This Google Sheet is a different from Step 1 wherein it contains the responding values for columns that has TRUE

For example:

  • Column 1: Lorem ipsum dolor sit amet, consectetur adipiscing elit.
  • Column 2: Duis ut tortor in quam lobortis auctor.
  • Column 3: Etiam eleifend mi ac mauris tincidunt vestibulum ut eget diam.
  • Column 4: Suspendisse at finibus magna, eu dignissim quam.
  • etc

Step 3: Google Docs - Generate New Document From Template

This Google Sheet has only one variable named {{DYNAMIC_CONTENT}}

THE GOAL
Since on Step 1, the following columns is TRUE

  • COLUMN 1
  • COLUMN 2
  • COLUMN 4

The Google Docs should produce a document like this that are properly spaced even if there are gaps on the columns that have FALSE as value.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Etiam eleifend mi ac mauris tincidunt vestibulum ut eget diam. Suspendisse at finibus magna, eu dignissim quam.

PROBLEM

  • Is there a better way to properly generate the contents instead of using a series of IF formulas.

Formula example:

IF(STEP1_COLUMN1=TRUE;STEP2_COLUMN1;space)
IF(STEP1_COLUMN2=TRUE;STEP2_COLUMN2;space)

It might become tedious to write this formula if in case there’s 20 columns or more.
Additionally, there’s also a problem of introducing too many spaces in case that

  • Column 1 is TRUE
  • Column 2 till 24 is FALSE
  • Column 25 is TRUE

Which may result to 22 spaces

Lorem ipsum dolor sit amet, consectetur adipiscing elit. [______ this is the space I mentioned above ______] Praesent scelerisque metus eu arcu finibus blandit.

This will be interesting to do, Are the column name in both of the spreadsheets the same?

1 Like

Correct, it’s the same header name for both Step 1 and Step 2