Skip empty formmenu values

Looking for some assistance. I have a CSV file with 3 columns. I'm trying to convert the values in each column to be formmenu entries. Some of the columns have empty values so I don't want empty values populated in the formmenu. I have been able to write code to pull the CSV file and turn it into formmenus but it is full of empty values. Wondering if anyone would advise how to remove the empty values from the formmenu.

Thanks in advance.

{urlload: https://docs.google.com/spreadsheets/d/1VPmxvyq8kiRU12Hdk8fjB_UJ0y_2a-kLQVcvMk5LDqU//export?format=csv; done=(res) -> ["csv"=res]}

{rows=slice(map(split(catch(csv, ""), "\n"), row -> split(row, ",")), 2)}

{formmenu: values={=rows}; itemformatter=(row) -> row[1]; name=brush; trim=yes}
{formmenu: values={=rows}; itemformatter=(row) -> row[2]; name=razor; trim=yes}
{formmenu: values={=rows}; itemformatter=(row) -> row[3]; name=straight; trim=yes}

Hi, and welcome to the forum! :slight_smile:

You can use the filter() function to remove the empty entries. The following removes everything that is blank or only whitespace:

{data=["Red", "", " ", "Green", "", "Blue"]}

{formmenu: values={=filter(data, (item) -> trim(item) <> "")}}

Hi scott,

Thanks for the assistance. The problem is whole value isn't empty, just a portion of it. You can see it in the example. Having a little trouble seeing how to translate that back to the filter.

Jared

Your spreadsheet isn't public Jared, so I can't take a look.

Please post an example of what your data looks like directly in the forum.

Strange. it is set to public for anyone that has the URL. In any regards, I was able to figure it out. Here is my end resulting code ( I also added in some code to filter out escaped quotes as well:

{urlload: https://docs.google.com/spreadsheets/d/1VPmxvyq8kiRU12Hdk8fjB_UJ0y_2a-kLQVcvMk5LDqU//export?format=csv; done=(res) -> ["csv"=res]}

{rows=slice(map(split(catch(csv, ""), "\n"), row -> split(row, ",")), 2)}

{formmenu: values={=filter(rows, row -> testregex(row[1], "\S"))}; itemformatter=(row) -> replaceregex(replaceregex(row[1], "^"(.)"", "$1","g"), """", """); name=brush; trim=yes}
{formmenu: values={=filter(rows, row -> testregex(row[2], "\S"))}; itemformatter=(row) -> replaceregex(replaceregex(row[2], "^"(.
)"", "$1","g"), """", """); name=razor; trim=yes}
{formmenu: values={=filter(rows, row -> testregex(row[3], "\S"))}; itemformatter=(row) -> replaceregex(replaceregex(row[3], "^"(.*)"", "$1","g"), """", """); name=straight; trim=yes}

Content from file:
Brushes,Razors,Straights
Copper Hat 24mm 2 Band Fan Knot,DE89 Head with Windrose K2 Handle,"ALE 9/8"" 1/2 hollow"
Copper Hat 24mm Tuxedo Synthetic,Ever-Ready 1912,"George Wostenholm & Sons Celebrated 6/8"" Near Wedge"
Craving Shaving 26mm Gel Tipped Boar,Ever-Ready 1914,"John Clarke & Son Sheffield 3/4"""
CSB 24mm Plisson Synthetic,Fatip Piccolo Special Edition,"Soviet ZTV Extra 1960 5/8"""
DS Cosmetic Captain 24mm Synthetic,Gem Jr Bar,"Wade & Butcher 13/16"" Near Wedge"
Ever-Ready 100T 24mm 2 Band Badger,Gillette Fat Handle Tech,
Ever-Ready 18mm Boar,Gillette Fatboy on,
Omega 24mm Boar,Haward Safety Razor,
Oz Shaving 26mm Brown Synthetic,Karve CB on,
Paragon 28mm Plisson Synthetic,Ming Shi 2000,
Rockwell 20mm Synthetic,Paragon 'The Ace',
Rubberset 22mm Silvertip,Razorock Old Type,
Rubberset 26mm Cashmere Synthetic,Razorock SLOC 2.0,
Semogue Excelsior 820 22mm Boar,Rex Envoy,
Simms 21mm Best Badger,Rockwell 6s on,
Sloyd 20mm Tuxedo Synthetic,Schick Injector Type E,
Sloyd 22mm Silvertip,Tatara Masamune,
Sloyd 24mm Timberwolf Synthetic,Tatara Masamune Nodachi,
That Darn Rob 26mm Brown Synthetic,West Coast Shaving/Charcoal Goods Lithe Razor with Space Needle Handle,
Tournure de bois 26mm APShaveCo Gealousy,Yaqi Mellon,
Tournure de bois 26mm Cashmere Synthetic,Yaqi R1730 24mm Sagrada Familia Tuxedo,
Tournure de bois 26mm White Synthetic,,
Wild West Brushworks 26mm Brown Synthetic,,
Yaqi 24mm Barberpole Tuxedo Synthetic,,
Yaqi 24mm Cashmere Synthetic,,
Yaqi 24mm High Mountain Silvertip,,
Yaqi 24mm Moka Express Synthetic,,

Looks good!

One suggestion is you might be able to use the replace() function to strip quotes. If you don't need to balance quotes or do more complex matching, it will be simpler than replaceregex().

{=replace("\"Quoted text\"", "\"", "")}

Thanks, I originally was trying to use replace but it is a bit tricky in that I need "" replaced by " and I need " removed entirely, based on how quotes are escaped in CSV.