In my quest to de-array-formula-ify my GSheets for use in Glide, I had ran across an interesting issue trying to deal with CHAR(10) splitting a column with artifacts from a JotForm submission.
The form allows uploads of up to 10 images, this gets posted to a GSheet, which is then available in Glide. The data is in one column, but is separated by a line break. The best I’ve come up with is 10 additional columns with an INDEX(SPLIT()) pulling each line out. Glide makes this great because it collapses each of those 10 columns into one containing all the images (which I had before the split).
The difficult part is that JotForm seems to write data badly – more than just a line break there appear to sometimes be spaces after the line break (sometimes up to 9), and it doesn’t fix spaces in the name of the original uploaded image either, which further causes confusion with trying to consistently split the data.
So the question: Is there a better way to handle this in Glide – because it seems to not recognize CHAR(10) in any of the available actions – and, am I taking the “get rid of array formulas in Sheets” to an extreme? (because it seems to sometimes cause issues in the app when synching with Google)
Essentially I just need each of these URLs, but am stuck on the best way to do all of this Glide native. Glide doesn’t seem to recognize the URLs when in one column with a link break and goofy extra spaces, but split them into their own column and it works great.
I have also tried Experimental → Code → Excel Formula… This doesn’t seem to do anything with CHAR(10) either. Nor does it like very complex regexreplace(), substitute(), or other nested attempts to defuse the goofy extra spaces and line breaks.
The best I can come up with is maybe just doing the INDEX(SPLIT()) operations in Glide Code vs in GSheets. Would still require the same number of columns, but at least it would run on Glide’s side.
So it does accept [enter] as a line break in the split column type, vs CHAR(10), which seems to do nothing. It seems to have gotten rid of the extra whitespaces, did /not/ compromise the spaces in the URL/file itself, AND converted everything to CSV. HUZZAH!
However… now there seems to be a limitation with how Glide → Images works. I had tried other GSheets based routes to CSVify the line break mess, but it only ever shows one image. “Image” column is a basic column, and the only compatible with GSheets. “Multiple Images” column is Glide only, and doesn’t work in GSheets.
Glide is auto-collapsing the 10-columns to 1-multi-images column, but when fed images that are CSV it does not recognize them, and the “image” basic column only shows the first.
So, back to the original question I guess: Am I taking the “must get rid of array formulas” too far? And by extension, is using an Experimental → Code → Excel formula in Glide better than doing it in Sheets? (assuming it even works as was the case with some more complex formulas)
https://www.jotform.com/uploads/Du231/Cracked Driveway.jpg
https://www.jotform.com/uploads/Du231/Driveway Damage Pic _1.jpg
https://www.jotform.com/uploads/Du231/Driveway Damage Pic _3.jpg
https://www.jotform.com/uploads/Du231/Left Side Tree - Drainpipe.jpg
https://www.jotform.com/uploads/Du231/Two Trees - Right Side Pic _1.jpg
Links will not work, but goofy ass whitespace et al is preserved.
The “customerPhoto” column is auto-generated by Glide collapsing “customerPhoto1,” “customerPhoto2,” etc which were all generated by GSheets arrayformulas to split “customerUploads” by CHAR(10). As you can see, it’s a Multi-Images Glide column.
The “customerPhotoCSV” is a GSheets column where I properly reformatted the janky data from JotForm into proper CSV. Glide only shows the first image.
So, really, this is two fold. Getting rid of GSheets based arrayformulas, and doing the work in Glide with as few columns as necessary.
Yes, the original “customerUploads” data from JotForm is posted above with line breaks and extra whitespace included. The Split Text data with the [enter] as the delimiter is:
https://www.jotform.com/uploads/Du231/Cracked Driveway.jpg,https://www.jotform.com/uploads/Du231/Driveway Damage Pic _1.jpg,https://www.jotform.com/uploads/Du231/Driveway Damage Pic _3.jpg,https://www.jotform.com/uploads/Du231/Left Side Tree - Drainpipe.jpg,https://www.jotform.com/uploads/Du231/Two Trees - Right Side Pic _1.jpg
I’m lost here. The Split Text column takes a delimited list and converts it into an array. An array works very well in an image component. If your original delimited list uses a newline character as the delimiter, then pressing Enter in the delimiter field of the Split Text column specifies that the enter key is the delimiter. I’m not understanding how your are getting a CSV (comma delimited result) from a Split Text column, when it should be an array. This should be a pretty simple process in glide that doesn’t require any work in the google sheet. It should be the the original newline delimited column, and the Split Text column. That should be it.
I was expecting the view to look different on the data side, due to how the image and multi-image columns look – i.e. it shows the images in the column. So because it wasn’t doing this my assumption was it wasn’t reading the data correctly.
Had I mapped that field to an image/carousel it would have just worked.
Re: Comma delimited results – that’s what it’s outputting.
This:
With this: (line break delimiter mixed with random spaces)
