Google Sheets API v4 and valueInputOption

Question

I have three columns in my spreadsheet. The first one is date, the second and the third are simple strings.

When I do batch upload of my data valueInputOption = "RAW" I get a wrong result for my Date column. All dates have an invisible apostrophe before them. String columns are OK.

When I use valueInputOption = "USER_ENTERED" all dates are good, but other values are interpreted not as actual values but as Google parsed values. For example a string "2-3-4" will be represented as 02/04/2004 or something like that.

I want data column to be represented as USER_ENTERED and string columns as RAW.

Is there any way to set valueInputOption for a cell (column)?

Answer 1

I have found the answer.

In order to make all needeed data to be represented as string, you should:

• set valueInputOption = USER_ENTERED;
• prepend it with apostrophe sign just like this one '.

In this case other columns type will be determined automatically based on Google's own algorithms and columns with cells prepended with ' sign will be shown exactly as you enteren them

It works when all spreadsheet columns have Automatic type set. Not sure what will happen for colums with other format types.