Count unique items (AppSheet vs. Google Sheets)

[Kirk_Masden]

Like many people who use AppSheet, I store my data in Google spreadsheets.  Sometimes, I access the data in the sheets to monitor activity indirectly.  I've found, though, that AppSheet and Google Sheets have different criteria for determining what is the same and what is different.  This makes it hard to replicate the way AppSheet counts in a spreadsheet.  Here are some examples:

Counted as different (just like Google Sheets):

count(unique({"red ball","redball"}))

Counted as the same (unlike Google Sheets):

count(unique({"red ball ","red ball"}))
count(unique({" red ball","red ball"}))
count(unique({"ball","Ball"}))

In other words, Google Sheets pays attention to the presence of spaces at the ends and beginnings of words while AppSheet ignores such differences.  Similarly, Google Sheets treats upper case as different from lower case while AppSheet sees them as the same.  

I asked ChatGPT what to do and was told that I could use TRIM() and LOWER() in Google Sheets to replicate AppSheets counting.  OK.  I guess that will work but I have a couple of questions.

1. Why is this not explained here?
UNIQUE()
https://support.google.com/appsheet/answer/10107368?hl=en

2. Are there other differences (besides those I explained above) between what the two platforms view as different or unique?

[Steve]

Attn @lizlynch 

[Kirk_Masden · Accepted solution]

Thanks Steve!  I found out that AppSheet also ignores Japanese spaces ("　") at the beginnings and ends of strings.  So, to replicate AppSheet "unique" counts I needed to use the following sort of formula in Google Sheets:

=REGEXREPLACE(B1, "^　|　$", "")

The actual formula I needed to use was more complicated but the above formula shows the basic technique for removing Japanese spaces from the beginnings and ends of words.

P.S I don't usually make a habit of awarding solutions to myself but, in this case, I think I'll do so.  By the way, all of the actual formulas I'm using to solve this problem in Google Sheets are ones that I had ChatGPT prepare for me.  Very useful!

[Steve]

=REGEXREPLACE(B1, "^\s+|\s+$", "") might be a better catch-all, but I don't know if it'll catch "Japanese spaces". \s+ matches one or more whitespace characters, which (at least) includes (normal) spaces and tabs.

[Kirk_Masden]

Thanks!  I tested it and it works, though, to be honest, I don't understand it well.