Valid If formula on form field for file name restricting special characters

[gtreichel]

Context: I have an app with a form for grant applications.  Later in the process, I use the Title provided by the user in the file name where I generate a PDF to represent a final report related to the grant.  This allows the original data to be archived.

What I found was that users were at times including special characters in the title which would cause issues with the file name on Google Drive.  With lots of searches and disappointment there is not yet a RegEx function.  With experimentation, I came up with the following formula for Valid If.  The idea is to block most special characters, but allow those that I've found acceptable when generating files.

Allowed symbols include ( ) - _ . !

EncodeURL replaces symbols with % and their char number, and captures most of them.  I then change the %20 for space back to a space, replace the % with #, and for the three symbols that EncodeURL does not cover ~'*, I replace them with #0.  Also, had to put a space after the single quote otherwise Appsheet wouldn't accept it.  Now because all of the encoded values start with a #, I can use Extracthashtags to see if there are any, and in fact how many.

Hope this helps others with a similar issue.  Also open to feedback if there might be a better approach.

COUNT(
  EXTRACTHASHTAGS(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              ENCODEURL([_THIS]),
              "%20",
              " "
            ),
            "%",
            "#"
          ),
          "~",
          "#0"
        ),
        TRIM("' "),
        "#0"
      ),
      "*",
      "#0"
    )
  )
) = 0