I'm coding a new python script that need to extract data from google sheets, but there are many cells which are merged/combined, and only the top-left cell from this merge has the value. It's important to have that value on all the merged cells.
How can I do that?
Python 3.8.5 + gspread 3.6.0
Note: every comment "trying to get...", the code right below it should return the same value as the previous code.
Spreadsheet test: https://docs.google.com/spreadsheets/d/17Dyxufu1y1ouBCPkf5Y7Vt1UW70WroK0Moy_DD7bZKc/edit?usp=sharing
Code for reproducing the problem:
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os
import pprint
here = os.path.dirname(os.path.abspath(__file__))
secret = os.path.join(here, 'credentials.json')
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name(secret, scope)
client = gspread.authorize(creds)
sheet = client.open_by_key('17Dyxufu1y1ouBCPkf5Y7Vt1UW70WroK0Moy_DD7bZKc')
ws = sheet.sheet1
pp = pprint.PrettyPrinter()
#getting the FIRST text
result = ws.acell('A1')
pp.pprint('A1: '+result.value)
#trying to get the SAME text on the cell col+1
result = ws.acell('A2')
pp.pprint('A2: '+result.value)
#getting the 'simple_cell'
result = ws.acell('C2')
pp.pprint('C2: '+result.value)
#getting the 'row_merged'
result = ws.acell('D2')
pp.pprint('D2: '+result.value)
#trying to get 'row_merged' on row+1
result = ws.acell('E2')
pp.pprint('E2: '+result.value)
#getting the 'col_merged'
result = ws.acell('D6')
pp.pprint('D6: '+result.value)
#trying to get 'col_merged' on col+1
result = ws.acell('D7')
pp.pprint('D7: '+result.value)
The output is like this:
('A1: just to confirm, the value "row_merged" has been put to cell D2 originally. Value "col_merged" is in D6 initially. Since it's merged, the expected result should be found on all merged cells')
'A2: '
'C2: simple cell'
'D2: row_merged'
'E2: '
'D6: col_merged'
'D7: '
PS C:UsersjoaovDesktop>
The point is: A2 must be equals to A1... E2=D2, D7==D6... But it seems there's no way of handling merged cells with gspread.
See Question&Answers more detail:os