Google Sheets API appending extra apostrophe

user_1997852 0
Viewed 1924

I've been using this Google Sheets API code for a few months in Python to append data to a spreadsheet, but it recently broke. Can anyone see what is wrong with this?

body = {'values': [['2019-9-1', '41']]}
result = service.spreadsheets().values().append(spreadsheetId=SPREADSHEET_ID,valueInputOption='RAW', body=body, range='A:Z').execute()

It creates cells like this, with an apostrophe before each cell value:

+----+-----------+-----+
|    |     A     |  B  |
+----+-----------+-----+
| 33 | '2019-9-1 | '41 |
+----+-----------+-----+

This is the result:

{'spreadsheetId': 'xxxxxxxxx', 'tableRange': 'data!A1:U32', 'updates': {'spreadsheetId': 'xxxxxxxxxx', 'updatedRange': 'data!A33:B33', 'updatedRows': 1, 'updatedColumns': 2, 'updatedCells': 2}}
google-sheets-api python
1 Answers
  • You want to put the values of 2019-9-1 and 41 to the Spreadsheet as the date object and the number, respectively.
    • In the current situation, the result becomes like '2019-9-1 and '41.
  • You want to achieve it using google-api-python-client with Python.

If my understanding is correct, how about this modification?

Issue:

I think that the reason of your issue is due to valueInputOption='RAW'. When RAW is set to valueInputOption, the official document says as follows.

The values the user has entered will not be parsed and will be stored as-is.

By this, 2019-9-1 and 41 are put as the string type. So ' is added to the top of character.

Solution:

In order to put the values of 2019-9-1 and 41 as the date object and the number, please modify as follows.

From: 
result = service.spreadsheets().values().append(spreadsheetId=SPREADSHEET_ID,valueInputOption='RAW', body=body, range='A:Z').execute()
To: 
result = service.spreadsheets().values().append(spreadsheetId=SPREADSHEET_ID,valueInputOption='USER_ENTERED', body=body, range='A:Z').execute()

  • When USER_ENTERED is set to valueInputOption, the official document says as follows.

    The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

References:

If I misunderstood your question and this was not the result you want, I apologize.

user_71086530
Related