How do I store a text value beginning with "+" character in appsheet?

[gaixixon]

Hello. I notice that, in appsheet field with "Text", or "LongText" type, I am unable to store value like:

+This is some text value that I want to store, it BEGINS with a "+" mark.
In Googlesheet databased, it is stored as #NAME?.
Unless I manually enter apostrophe ' mark in Google Spreadsheet or in Appsheet form.

 Is there anyway that Appsheet automatically add ' mark when in Appsheet form, I enter "+Some text start with +"

I understand that I can either manually enter ' mark; or using automation script.. but this is way too complicated for a simple job.

[WillowMobileSys]

You should NEVER need to explicitly add the apostrophe in Google sheets.  I would first set the format of every sheet column to general text - this will help with other known issues as well.   At the very least set the format of THIS text column.  Don't worry, AppSheet can handle the conversion. 

Next make certain the text you are trying to save is properly quoted.  Otherwise AppSheet will think it is an arithmetic expression.

[gaixixon]

Thank you for your reply. I did set the colum in Googlesheet as Text type. However, at appsheet form (Long Text/or Text type), if I enter something starting with + character, then in Googlesheet, it understand as a formular!

I don't know what I have done wrong.

[WillowMobileSys · Accepted solution]

Well, I didn't take my previous testing far enough.  When you first save text with the "+" sign and immediately review the entry, everything looks like it should.

However, after the background sync runs, as you have described, the text is NOT reloaded because on the Google sheet side is attempting to treat it as a formula - even if the sheet column is defined as plain text - and because it is not in a proper formula format, an error occurs on the sheet side and prevents retrieval of the text.  As far as I can tell there is no way to prevent Google sheets from treating any supplied text in this manner.

So, here are a few suggestions to resolve:

1)  If at all possible, avoid using these symbols at the start of your text.

2)  Insert validation to check if the entered value starts with =, +, etc, and then provide a validation message asking the user to insert the apostrophe.

3)  Assign the apostrophe, automatically.  The way I would do this is to attach an action on Form Save such that when the entered text starts with =,+, etc, it performs the SUBSTITUTE() function replacing the entered symbol with the apostrophe & the entered symbol.  I will assume that you know how to do this.  If not, please ask.

I hope this helps!

[gaixixon]

Thank you @WillowMobileSys  Even this solution is not 100% solved my problem, but it is the ONLY option for now. So I marked it as resolved, and waiting for Appsheet team to automatically add apostrophe mark before every Text/Long text entry.

[Suvrutt_Gurjar]

Another option to try could be use an emoji of + sign (➕) as an initial value of the long text column. Whenever a + sign is required in the beginning,  the user can start typing the text after the emoji(➕) and whenever the user does not need + sign in the beginning, the user can simply overwrite it. However once the user overwrites, the emoji will not be available if the user is in the form as it is an initial value.

An example of emoji used as initial value in a column product description.

Filled in details as shown below:

Backend G sheet: 

You will need to test, if emoji is displayed in all the devices that your app users will use. However the + sign emoji was released in 2010 ( 12 years ago) . So , practically it should display on all devices.

➕ Heavy Plus Sign Emoji

[WillowMobileSys]

---

@gaixixon wrote:

Even this solution is not 100% solved my problem

---

Why not?  What approach did you use and what other issues are you seeing?

---

@gaixixon wrote:

waiting for Appsheet team to automatically add apostrophe mark before every Text/Long text entry

---

Unless you have heard otherwise, I wouldn't expect AppSheet to do this anytime soon...if at all ever.   There are only a handful of use cases where this is a problem and is specifically an issue when using Google Sheets (possibly also Excel). 

It is probably in your best interest to resolve it through a custom implementation.

[dbaum]

---

@WillowMobileSys wrote:

3)  Assign the apostrophe, automatically.  The way I would do this is to attach an action on Form Save

---

You might be able to get away with even the super simple action of always inserting an apostrophe--i.e., set the value of the column to "'" & [column]. Sheets suppresses the apostrophe in the cell's displayed value and, thus, in the value sent to AppSheet.