Skip to main content
Best answer

Formatter Split Text issue causing repeated values in Google Sheets


Forum|alt.badge.img
  • Beginner
  • 3 replies

I’m newer to zapier, so this is probably user error, but I can’t figure out why using Formatter Split Text as Separate Fields is duplicating the value over and over and over (and then this gets piped over to the google sheets row that gets created). Here are the steps I’ve setup so far with screenshots, steps with ✅ indicate they are working as expected and steps with❓are where I’m having issues:

  1. ✅ Get data from CVENT when a new attendee registers, including individual questions and answered include in the registration. 
     
  2. ✅ Choose input for Formatter by Zapier - Split Text on Comma. I want to split the field Questions Value which has concatenated all the answers from the above questions into one field separated by commas.

     

  3. ❓Configure Formatter with comma as Separator and All (as Separate Fields) as Segment Index and show test output (second screenshot in this step).  
    test output

     

  4. ❓Configure formatter outputs to google sheets fields. I can see that values are repeated multiple times here so that’s what’s getting pushed to the google sheets row. In the screenshot I am mapping Output Item 1 from the Formatter step to a google sheets field called Jurisdiction name, and you can see the value “test jurisdiction name” is repeated 6 times with a comma in between each. Just to be clear I only want the value “test jurisdiction name” to appear once, not 6x. This is happening with all the fields that were split in the Formatter step - the values contained in them are repeated 6x. 

     

  5. ✅ Final output in google sheets, repeated values showing as expected (but not desired).

     

Best answer by erlp

Well I gave up on using the formatter for now and wrote some python that splits the text how I want it (shout out to zapier AI + google for getting me started). Pasting that here in case it’s helpful for other folks.

input configuration:

 

Code:

#get input from CVENT field Questions Value
questions_value = input_data.get('questions_value', '')

# Check if questions_value is not empty before splitting
if questions_value:
    items = questions_value.split(',')
    jurisdiction_name = items[0] if len(items) > 0 else ''
    VANID = items[1] if len(items) > 1 else ''
    category = items[2] if len(items) > 0 else ''
    discount_code = items[3] if len(items) > 0 else ''
    jurisdiction_type = items[4] if len(items) > 0 else ''
    elected_title = items[5] if len(items) > 0 else ''
else:
    jurisdiction_name = ''
    VANID = ''
    category = ''
    discount_code=''
    jurisdiction_type = ''
    elected_title = ''

# Prepare the output as a dictionary
output = {'jurisdiction_name':jurisdiction_name, 'VANID':VANID, 'category':category,'discount_code':discount_code, 'jurisdiction_type':jurisdiction_type, 'elected_title':elected_title}

 

 

View original
Did this topic help you find an answer to your question?

5 replies

Forum|alt.badge.img
  • Author
  • Beginner
  • 3 replies
  • February 3, 2025

I can’t figure out how to edit this post, but it seems like the number of repeats is the same as the number of questions I’m trying to extract (so for example if I use test data with only 3 of these questions answered it repeats only 3x). 


ken.a
Forum|alt.badge.img+6
  • Zapier Staff
  • 6301 replies
  • February 5, 2025

Hi there ​@erlp,

Before we dig deeper into this, could you please try using the Line-item to Text function instead? The Line-item to Text function should be under the Utilities event.

Kindly give it a try and let me know how it goes? I'll keep an eye out for your response!


Forum|alt.badge.img
  • Author
  • Beginner
  • 3 replies
  • February 5, 2025

Sure thing ​@ken.a, I tried Line-item to Text and output is even more wonky. 

 

 


Forum|alt.badge.img
  • Author
  • Beginner
  • 3 replies
  • Answer
  • February 5, 2025

Well I gave up on using the formatter for now and wrote some python that splits the text how I want it (shout out to zapier AI + google for getting me started). Pasting that here in case it’s helpful for other folks.

input configuration:

 

Code:

#get input from CVENT field Questions Value
questions_value = input_data.get('questions_value', '')

# Check if questions_value is not empty before splitting
if questions_value:
    items = questions_value.split(',')
    jurisdiction_name = items[0] if len(items) > 0 else ''
    VANID = items[1] if len(items) > 1 else ''
    category = items[2] if len(items) > 0 else ''
    discount_code = items[3] if len(items) > 0 else ''
    jurisdiction_type = items[4] if len(items) > 0 else ''
    elected_title = items[5] if len(items) > 0 else ''
else:
    jurisdiction_name = ''
    VANID = ''
    category = ''
    discount_code=''
    jurisdiction_type = ''
    elected_title = ''

# Prepare the output as a dictionary
output = {'jurisdiction_name':jurisdiction_name, 'VANID':VANID, 'category':category,'discount_code':discount_code, 'jurisdiction_type':jurisdiction_type, 'elected_title':elected_title}

 

 


ken.a
Forum|alt.badge.img+6
  • Zapier Staff
  • 6301 replies
  • February 6, 2025

That’s awesome ​@erlp! Thank you for providing the workaround here. This will he helpful to our Community members who might encounter the same issue.

If you have any other questions, please don’t hesitate to reach out to the Community. We’re always happy to help! 🤗