Map a Trello field to a Google Form field using butler automation

[Hannah Humbert]

Each of my Trello cards represents a job for a client. Each job needs a form filled out once it reaches a certain stage. We are using Google Forms, and I am using automation to add this form to a card's checklist. 

I would like to pre-fill certain fields in my form based on the information within a card to avoid duplicated efforts/potential errors due to manual input. I know I can change the form URL to achieve this by finding the entry IDs for each field. E.g.

docs.google.com/forms/d/e/FORM_ID/viewform?usp=pp_url&entry.123456789=Answer1&entry.987654321=Answer2

"Answer1" and "Answer2" would be custom field values - let's say "Full Name" and "Email".

These values will be different for each card, so I would like to dynamically update the URL using Trello variables. 

For example, my rule could be something like:

When a card is moved to list "Ready", add item "docs.google.com/forms/d/e/FORM_ID/viewform?usp=pp_url&entry.123456789={{%Full Name}}&entry.987654321={{%Email}}" to checklist "Forms To Complete"

So the output would look something like: docs.google.com/forms/d/e/FORM_ID/viewform?usp=pp_url&entry.123456789=Bobby Bob&entry.987654321=Bob@bob.com

Obviously, email works great here as there are no spaces, but by using the full name field, it breaks the link due to the space. 

If there was a way to find and replace the text so it transformed it to a url-friendly version (e.g. Bobby+Bob), that would be awesome, but I don't know if that's possible using Trello...or maybe some alternative solution? Ideally not using 3rd party tools, but could be open to it.

Thanks in advance!!

[Mike - Dreamsuite Consulting · Accepted solution]

Hi @Hannah Humbert 

You can use regex for this...

When a card with custom field "Full Name" set to "regex:/^(\w+)\s(\w+)(?:\s(\w+))?$/", add item "docs.google.com/forms/d/e/FORM_ID/viewform?usp=pp_url&entry.123456789={wildcard1}%20{wildcard2}&entry.987654321={{%Email}}" to checklist "Forms To Complete"

[Hannah Humbert]

Thank you so much!! That works perfectly for the name field if there are 2-3 words. Would this work for an address field that could have 10 or more words, along with special characters like - , # / etc.?

[Mike - Dreamsuite Consulting]

Hi @Hannah Humbert 

You can by just adding more of the same combination:

The regex below would capture up to 30 words, including special characters (the \S = any non-whitespace character)

regex:/^(\S+)\s(\S+)(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?(?:\s(\S+))?$/