14

To update a range of cells, you use the following command.

## Select a range
cell_list = worksheet.range('A1:A7')

for cell in cell_list:
    cell.value = 'O_o'

## Update in batch
worksheet.update_cells(cell_list)

For my application, I would like it to update an entire range, but I am trying to set a different value for each individual cell. The problem with this example is that every cell ends up with the same value. Updating each cell individually is inefficient and takes way too long. How can I do this efficiently?

msturdy
  • 10,479
  • 11
  • 41
  • 52
ecollis6
  • 5,357
  • 4
  • 17
  • 12

5 Answers5

28

You can use enumerate on a separate list containing the different values you want in the cells and use the index part of the tuple to match to the appropriate cells in cell_list. 

cell_list = worksheet.range('A1:A7')
cell_values = [1,2,3,4,5,6,7]

for i, val in enumerate(cell_values):  #gives us a tuple of an index and value
    cell_list[i].value = val    #use the index on cell_list and the val from cell_values

worksheet.update_cells(cell_list)
ecline6
  • 896
  • 8
  • 15
  • It returns '1' for the first column, and 'none' for the remaining – ecollis6 May 21 '13 at 17:51
  • 2
    Your example gives a range of A1:A7. Only cells in the first column would be changed. I tested and it works on my machine. It should make A1 = 1, A2 = 2, A3 = 3, etc. If you want columns, then you need range('A1:G1') – ecline6 May 21 '13 at 18:01
  • My mistake, I modified it for the data to be entered in columns instead of rows. Do I need to make any other adjustments? – ecollis6 May 21 '13 at 18:05
  • The only tricky bit is you'll need to make sure that the length of your cell_list is the same length as the cell_values list. If they're not the same length, you'll get an 'index out of range' error. Otherwise this method should work as expected. – ecline6 May 21 '13 at 18:07
  • I am still having issues arranging the data in columns. Every time I run the script, it adds an additional value, but pressing 'submit' more than once is a problem. – ecollis6 May 21 '13 at 18:11
  • 1
    Without seeing the code that your talking about now, I can't imagine what is happening. – ecline6 May 21 '13 at 18:13
  • I made the mistake of indenting the update_cells line D: it's working now, thank you very much! – ecollis6 May 21 '13 at 18:22
13
  1. Import modules
import gspread
from gspread.cell import Cell
from oauth2client.service_account import ServiceAccountCredentials
import string as string
import random
  1. Create cell array with values
cells = []
cells.append(Cell(row=1, col=1, value='Row-1 -- Col-1'))
cells.append(Cell(row=1, col=2, value='Row-1 -- Col-2'))
cells.append(Cell(row=9, col=20, value='Row-9 -- Col-20'))
  1. Find the sheet
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('Sheet-Update-Secret.json', scope)
client = gspread.authorize(creds)
  1. Update the cells
sheet.update_cells(cells)

You could refer to my blog post for more details.

Seanny123
  • 8,776
  • 13
  • 68
  • 124
Prince Francis
  • 2,995
  • 1
  • 14
  • 22
4

Assuming a table with a header row, as follows:

Name  | Weight
------+-------
Apple | 56
Pear  | 23
Leaf  | 88

Then, the following should be self explanatory

cell_list = []

# get the headers from row #1
headers = worksheet.row_values(1)
# find the column "Weight", we will remember this column #
colToUpdate = headers.index('Weight')

# task 1 of 2
cellLookup = worksheet.find('Leaf')
# get the cell to be updated
cellToUpdate = worksheet.cell(cellLookup.row, colToUpdate)
# update the cell's value
cellToUpdate.value = 77
# put it in the queue
cell_list.append(cellToUpdate)

# task 2 of 2
cellLookup = worksheet.find('Pear')
# get the cell to be updated
cellToUpdate = worksheet.cell(cellLookup.row, colToUpdate)
# update the cell's value
cellToUpdate.value = 28
# put it in the queue
cell_list.append(cellToUpdate)

# now, do it
worksheet.update_cells(cell_list)
ivan98
  • 69
  • 4
3

You can use batch_update() or update(). https://github.com/burnash/gspread

worksheet.batch_update([
            {
                'range': 'A1:J1', # head
                'values': [['a', 'b', 'c']],
            },
            {
                'range': 'A2', # values
                'values': df_array 
            }
        ])
user1976546
  • 151
  • 1
  • 8
1

Here's my solution if you want to export a pandas data frame to a google sheet with gspread:

  • We can't access and replace elements in cell_list with values in the data frame intuitively, with [row, col] notation.
  • However, the elements are stored 'cell_list' are stored in a 'row-wise' order. The relative ordering depends on how many columns in your dataframe. Element (0,0) => 0, element (3,2) in a 5x5 dataframe is 17.
    • We can construct a function that maps a [row, col] value from a data frame to its position in the list:
def getListIndex(nrow, ncol,row_pos, col_pos):
    list_pos = row_pos*ncol + col_pos
    return(list_pos)

We can use this function to update the correct element in the list, cell_list, with the respective value in the dataframe, df. 

count_row = df.shape[0]
count_col = df.shape[1]

# note this outputs data from the 1st row
cell_list = worksheet.range(1,1,count_row,count_col)

for row in range(0,count_row):
    for col in range(0,count_col):
        list_index = getListIndex(count_row, count_col, row, col)
        cell_list[list_index].value = df.iloc[row,col]

We can output the results of the list, cell_list, to our worksheet. 

worksheet.update_cells(cell_list)
Tim496
  • 162
  • 3
  • 19