Data added in the google sheet has ' added in front of it by default

[rishi29]

Hi All,

I created a form from google sheets. When I am adding data and saving it, the data is added as '1234567890722. Note the special character in front of the number. Although I do not add any special character in the form.

The field is text both in the form and the back end google sheet. Please refer to the screenshots below for the better explaination.

Any suggestions what might be happening? Because of this I am not able to add data validations.

[Suvrutt_Gurjar]

I think Google sheet adds that apostrophe to indicate that the column is text type, even though the data string contents are all numeric. Your backend setting for that column type appears to be “Plain text” or “Automatic”

rishi29:

Because of this I am not able to add data validations.

Could you please elaborate?

[rishi29]

Hi Suvrutt,

Thank you for the details. My bad it was one of those scenarios where you put equals instead of not equals. My requirement is that each column value should only have 3 unique level column values. So in the following example below:

GTIN LEVEL

123 1
123 1
123 2
123 3

User should not be able to add the second row. I am using the following logic in data validation:

AND(Not(In([_THIS], SELECT(Data Capture[GTIN 1], [GTIN 1] = [_ThisRow].[GTIN 1]))) , Not(In([_THIS], SELECT(Data Capture[Level / Niveau], [Level / Niveau] = [_ThisRow].[Level / Niveau]))))

This use case is not allowing me to add the records for the same GTIN value but different level. It is taking GTIN column as unique as well

[Suvrutt_Gurjar]

Please create a VC called say [GTINLevel] with an expression something like CONCATENATE([GTIN 1],"-", [Level])

In the valid if of either [GTIN `] or [Level] columns, please have an expression something like

ISBLANK(  FILTER(    "Data Capture",    ([_THISROW].[GTINLevel] = [GTINLevel])  )  - LIST([_THISROW]))

Please refer below article for valid_if for avoiding duplicates

help.appsheet.com

List Expressions

List expressions use and/or produce lists of values.

[rishi29]

Survrutt,

That worked like a charm! Just another follow up question if I may, can I prompt the user to save the record multiple times based on the user input? For example, user selects 2 as an option for number of levels they want to add. Then they can enter the data twice for the same record number.