1

In my application I have a bunch of cells not in range. Currently I am updating them one by one but it takes a lot of time. I would like to update them in batch by making just one call.

I looked at a few other SO threads such as this, but in my case the cells are not in range.

To simplify here is an example of what I am trying to achieve:

worksheet.update_acell("A1", "test1")
worksheet.update_acell("C5", "test2")

Is it possible to update cells not in range in one call?

Community
  • 1
  • 1
apadana
  • 13,456
  • 15
  • 82
  • 98
  • I believe it can be faster to load all the table, manipulate your data as you wish and then update all the table again. – Ilya V. Schurov Dec 04 '16 at 10:58
  • For example, you can try this approach: http://stackoverflow.com/questions/34400635/how-to-write-a-table-list-of-lists-to-google-spreadsheet-using-gspread. It can be efficient if your table is not too large. – Ilya V. Schurov Dec 04 '16 at 11:00

2 Answers2

2

Yes, it's possible. You can use Worksheet.update_cells method for this.

The argument of the method is a list of Cell objects and it doesn't matter where this list comes from. You can get it from range method or create the list yourself:

a1 = worksheet.acell('A1')
c5 = worksheet.acell('C5')
a1.value = 'Hello'
c5.value = 'World'
wk.update_cells([a1, c5])

This updates multiple cells in one call.

Burnash
  • 3,181
  • 2
  • 31
  • 35
  • Hi Burnash, thanks for your answer. The problem is that reading cells via acell still makes individual calls to the API which is time consuming. If there was another way to create a cell object without reading it first then this would have been the perfect answer. – apadana Dec 05 '16 at 15:53
  • This is true. Right now to get a `Cell` instance you need to make a request. Under the hood it fetches a piece of xml needed to construct another xml to make an update request. Although your mileage may vary, generally read requests are faster than write request. So you still get an improvement when using one `update_cells()` versus multiple update requests. – Burnash Dec 07 '16 at 15:59
  • Looks like a similar problem has been raised https://github.com/burnash/gspread/issues/376 – Burnash Dec 07 '16 at 18:17
1

Burnhash is correct in that there is no way to get a Cell without requesting it. However, I was able to acheive desired behaviour with a dummy class:

    class Cell:
        def __init__(self, c, r, v):
            self.col = c
            self.row = r
            self.value = v

    cell1 = Cell(1, 1, 'value1')  # A1
    cell2 = Cell(2, 1, 'value2')  # B1
    wk.update_cells([cell1, cell2])
Nick Smith
  • 11
  • 2