A weird behavior of Google Spreadsheets API (spreadsheets.values.append)

      Comments Off on A weird behavior of Google Spreadsheets API (spreadsheets.values.append)

Today I am using google spreadsheets API v4 to generate a spreadsheet from some data source. I believe one the most commonly used API is append. Say you get a tuple of data, you want  to add a row to the end of your table and fill in the data. values.append does exactly that work.

However, I found the API has a weird behavior that it does not locate the starting column as you specified. As stated in the API reference (here), it needs at least two parameters, spreadsheetID and range. The spreadsheetID is used to locate the spreadsheet document, range is used to specify which sheet and which place to append the row.

For example, your sheet1 is like this.

A B C D E F G H
1 xxx xxx
2 yyy yyy
3
4

You want to add (“”, “zzz”, “”, “zzz”) to the last row.  The first intuition is the table range is A to D. If I use range=sheet1!A1:D1 , I hope It will find the table within this range and append data to the last row. However, the following thing happens.

A B C D E F G H
1 xxx xxx
2 yyy yyy
3 zzz zzz
4

It turns out that the API will look for a table from right to left. It will expand row dimensions until it finds a empty row. Similaly it will expand column dimensions until it finds an empty column. So in last example, it found table D1:D2 and inserted a new row starting at D3.

Is using range=sheet1!A1:B1 OK? No, it will regard Col B as empty, and then Row 2 as empty and insert data to A2.

A B C D E F G H
1 xxx xxx
2 zzz zzz
3 yyy yyy
4

Even range=sheet1!A1:B2 does not work. B2 is regarded as the table and data will be inserted to B3.

The take-away is always make sure your data has no empty column  or empty row or disconnected subtable in the given range, so that the new row will be appended below last row, starting from the first column.

So for this existing data, there is no way to insert data to A3 automatically. To successfully use append , we generally need a header row which is a full row!

Here is the code I used for test.


import httplib2 from apiclient import discovery from oauth2client import client from oauth2client.file import Storage credentials = Storage('.\\.credentials\\myappcredential.json').get() http = credentials.authorize(httplib2.Http()) discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?version=v4') service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl) spreadsheetId = 'xxxxxxxxxxxxx_not_valid_xxxxxxxxxxxxx' #sheetId can be found in its url below_range = "Sheet1!A1:B1" #insert a row below this range value_input_option = 'RAW' insert_data_option = 'INSERT_ROWS' value_range_body = { "range" : "Sheet1!A1:B1", 'majorDimension': 'ROWS', "values": [["", "zzz", "", "zzz"]] } request = service.spreadsheets().values().append(spreadsheetId=spreadsheetId, range=below_range, valueInputOption=value_input_option, insertDataOption=insert_data_option, body=value_range_body, includeValuesInResponse=True) response = request.execute() print(response)

To try what I talked about in this post, follow this quickstart (python) to configure the credentials and this page to get example codes to use append.