Hello, I'm setting up a scenario to create a document with dynamic content.
THE SCENARIO
Step 1: Google Sheet - Watch New Rows
This Google Sheet contains columns with TRUE and FALSE values.
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 different from Step 1 and holds the corresponding values for columns marked as 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 a single variable named {{DYNAMIC_CONTENT}}
.
THE GOAL
Given that the following columns in Step 1 are TRUE:
- COLUMN 1
- COLUMN 2
- COLUMN 4
The Google Docs output should resemble this, with proper spacing even when there are gaps for columns with FALSE values:
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 more efficient method to generate the content than using a series of IF formulas?
Formula example:
IF(STEP1COLUMN1 = TRUE;STEP2COLUMN1;space)
IF(STEP1COLUMN2 = TRUE;STEP2COLUMN2;space)
This approach can become cumbersome with 20 or more columns. Additionally, there's an issue with generating excessive spaces if, for instance:
- Column 1 is TRUE
- Columns 2 through 24 are FALSE
- Column 25 is TRUE
This could result in 22 spaces.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. [_ this is the space I mentioned above _] Praesent scelerisque metus eu arcu finibus blandit.
Could you suggest a better way to handle this content generation?
This should be interesting to try. Are the column names identical in both spreadsheets?
Correct, it's the same header name for both Step 1 and Step 2