Identifying and removing invalid filename characters

AppSheet Tips & Tricks
1

I don’t find anywhere in the forum or AppSheet reference documentation a clear, comprehensive list of invalid characters in filenames. In case anyone finds it helpful, I did my best via some testing to figure out which characters get replaced by a hyphen when an AppSheet automation task creates a file on Google Drive. I needed this in the context of the technique explained in Creating an action to open a saved file, where it’s critical to be able to replicate the filename of a file created through an automation task.

Here’s what I came up with:

# * | \ : ' " / ? < >

There are various posts regarding techniques for sanitizing filenames. Most focus on nested SUBSTITUTE() functions (for a clear example see the screenshot in the “Adjust the initial value for each reference column” section of @MultiTech 's document on data importing), sometimes in combination with @Suvrutt_Gurjar 's nifty use of EXTRACTMENTIONS(). I ended up using @Steve 's idea of a spreadsheet formula. Here’s that formula from Google Sheets, where the string to be sanitized is in column A:

=REGEXREPLACE(A2, "[#*|\\:'""/?<>]", "-")

I didn’t test whether the create file automation task may trim leading or trailing spaces. Regardless, just to be safe, I did use the spreadsheet trim function in forming my filename:

=TRIM(REGEXREPLACE(A2, "[#*|\\:'""/?<>]", "-"))
7 Likes
2

I’m not aware of any documentation on this, so your post is an appreciated addition!

The list of unacceptable characters for file. table, and slice names has (if I recall correctly) expanded quite a bit within the last six months or so. Many of the characters have been problematic forever, but the app editor didn’t prevent their use or flag them until relatively recently.

Basically, avoid any character that has any meaning in expressions. Try to stick to letters, numbers, underscores, periods, and spaces (but avoid leading, trailing, and consecutive spaces).

4 Likes
3

Here’s a formula that I use to sanitize values so I can use them for IDs:

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(

TRIM([ENTER_YOUR_COLUMN_HERE]),

" , ", “"), ", ", "”), " “, “"), “-”, "”), left(”’ “, 1), “”), left('” ', 1), “”), “/”, “”), "", “”), “.”, “"), “,”, “”), “:”, “”), “;”, “”), “[”, “”), “]”, “”), “(”, “”), “)”, “”), “{”, “”), “}”, “”), “!”, “”), “@”, “”), “#”, “”), “$”, “”), “%”, “”), “^”, “”), “&”, “”), “*”, “”), “+”, “”), “?”, “”), “|”, “”), “<”, “”), “>”, “”), “`”, “”), “~”, “”), right(’ =', 1), “”), “_____”, "”), “", ""), "”, “"), “__”, "”)

  • Removes every special character on the keyboard
  • Certain special characters are exchanged for underscores
    • spaces, commas, that sort of thing
  • also removes an resulting “multiple underscores”
    • When replacing special characters, sometimes multiple underscores can be put one after another (__), so there’s a trailing bit at the end that removes any of these - reducing them to a single underscore.

While not as elegant as the RegExReplace in Gsheets, this formula accomplishes everything inside AppSheet - so you don’t have the google sheet formula slowing things down.

just thought I would share!

4 Likes
4

I found an old post back in 2018 :grinning_face:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Attachment-Name-In-the-workflow-rule-I-creat/m-p/372396

2 Likes