Skip to content
[Help] How to creat...
 
Notifications
Clear all

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

3 Posts
2 Users
0 Reactions
6 Views
MAKE_USER_2
(@make_user_2)
Posts: 2
New Member
Topic starter
 

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?

 
Posted : 24/04/2023 3:02 am
Runcorn
(@runcorn)
Posts: 16
Active Member
 

This should be interesting to try. Are the column names identical in both spreadsheets?

 
Posted : 25/04/2023 11:26 am
MAKE_USER_2
(@make_user_2)
Posts: 2
New Member
Topic starter
 

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

 
Posted : 25/04/2023 11:42 am
Share: