8

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}}
Elliott B
  • 980
  • 9
  • 32
  • that apostrophe is how google sheets (and spreadsheets in general) represent a text cell; The behavior is correct. Try passing a date or a integer number instead of text – nosklo Sep 30 '19 at 18:55

1 Answers1

14
  • 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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    Great answer, thanks. I didn't know apostrophe means string. In my code it was easier to change the array values from string to int and leave the valueInputOption=RAW. – Elliott B Sep 30 '19 at 22:34
  • @Elliott B Thank you for replying. I'm glad your issue was resolved. spreadsheets.values.append method can easily put the values. So the options for putting values are simple. If you want to put the values with the detail settings, I think that you can also use the AppendCellsRequest request of the method of spreadsheets.batchUpdate. [Ref](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest) – Tanaike Sep 30 '19 at 22:40