- taxi88testAsked on September 10, 2017 at 04:38 AM
I'm looking to add a "city" field to a Google Sheet linked to my form. I'm using the Geocomplete widget to get a full address which I need - but I'd like a second field with just the city.
I've tried using the copy to another field if filled condition to no avail.
The Address autocomplete widget does let me choose just the city, but will not accept the copy condition, and sadly will not work for me because it does not give the full autocompleted string (only the filtered results) and therefore Points of Interest are left semi blank since they don't have a street name and number.
Using custom formulas on the Sheet itself works, but of course I can only do that on already filled rows and I need the information live.
Obviously I can't ask my users to enter the address twice and hope they will do it correctly and not get annoyed everytime.
Thanks for your help.
- JotForm SupportMike_GAnswered on September 10, 2017 at 07:17 AM
- taxi88testAnswered on September 10, 2017 at 08:26 AM
Thanks for the prompt response!
My form is going to get updated and edited, so I rather not mess around with deploying and writing JS code where the parameters might change.
I guess it's possible to use Google Script on the Sheet itself, so the city will be split only after the line will be appended - but I have to make sure it's efficient since there will be thousands of lines in a few months time.
A shame the sweet calculate field formula editor doesn't support string/text based actions.
- CharlieAnswered on September 10, 2017 at 10:49 AM
Personally speaking, string/text based actions is quite tricky. What specific functionality are you looking for? Even if we could capture a part of the string or text there are questions like how should we capture the text? Is it based on a delimiter? Or the position of the string character?
The Form Calculation widget is currently limited to math functions and date calculations. But if you want to fetch or pull a part of the string in the Geocomplete widget or Autocompleted Address widget, that is possible by using the Substring widget.
However, there are also other challenges, example, for Geocomplete widget, you cannot predict the user's input, so if they input an address that may include house or street number, then the position of the "City" would be moved. Compared to the Autcompleted Address widget, it labels part of the address like you can specify the "City" input. However, they are not divided or separated by an actual delimiter, instead, they are separated by a new line based on the Google spreadsheet's output.
With those restriction and limitations, I can agree with my colleague that the full proof way to have freedom to manipulate a test string is to use your own custom script, tailed for the requirements you have and insert or inject it with the form's full source code, that is if you want to display a specific text string in the live form.
I hope that helps.
- taxi88testAnswered on September 12, 2017 at 04:03 AM
Actually, this specific case is quite straight foreward.
Google returns addresses in a "Street Name House Number (optional), City, Country" format. POIs replace the Street Name House Number string.
A user can neglect to fill the house number and even the street name - leaving only "City, Country".
In any case, not only is the output always comma delimited, we always have the same ", Country" suffix.
Ergo, all that is needed is to
1. Truncate the suffix ", ישראל"
2. If exist, truncate anything before the first comma.
Unfortunately, Substring Widget doesn't even support comma seperation :-(
- CharlieAnswered on September 12, 2017 at 04:38 AM
Unfortunately, the complexity lies in the second condition you have, "truncate anything before the first comma". The widgets or a feature needs to identify delimiter of the string you want to manipulate.
If you have any suggestions, you can open a new thread and specify what widget or feature you want to improve. In your case, I believe the feature you are looking for may be added in the Substring Widget. Although I cannot guarantee that it will be implemented anytime soon. It's best that you open a new thread, add the details of the feature or behavior you wanted the widget to have, then we will forward it to our developers.
- taxi88testAnswered on September 12, 2017 at 05:44 AM
As per your suggestion, opened a new thread.
Thank you all for the prompt and detailed responses, it's refreshing to encounter actual customer service and not automated "responses".