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

It's best to show this in practice in code

SERVICE_ACCOUNT_FILE = "wydupa15.json"
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
SAMPLE_SPREADSHEET_ID = "1oEfhwe46ZPPpBgr_LYfVZAzJWV-enZHfW8Tp2RsYhvQ"

CRED = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES
)

service = discovery.build('sheets', 'v4', credentials=credentials)

# The A1 notation of the values to update.
RANGE = 'Test1!A5:C5'  # TODO: Update placeholder value.

result = service.spreadsheets().values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=RANGE).execute()

print("Current values in a given area:",result)

# ---inserting DATA - GET function ----------------------------------------------

values = result.get('values', [])

AAA = [['A', 'b', 5220]]

request = service.spreadsheets().values().update(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                                 range=RANGE,
                                                 valueInputOption='USER_ENTERED',
                                                 body={'values':AAA})
response = request.execute()

# ---------------------------------result------
result = service.spreadsheets().values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=RANGE).execute()

print("Area value after changes:",result)