0

I have values from column A to column L, to collect these values I am using the following procedure:

from Google import Create_Service

#GOOGLESHEETS
CLIENT_SECRET_FILE = 'client_secrets.json'
API_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)
spreadsheet_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

range_names ="ProfitOptions!A2:L100"
result = service.spreadsheets().values().get(
               spreadsheetId=spreadsheet_id, range=range_names, valueRenderOption='UNFORMATTED_VALUE').execute()
sheet_output_data = result["values"]

spreadsheets_options = list(zip(*sheet_output_data))

print(spreadsheets_options)

But it's only returning the A,B,C,D columns:

[
('0-2', '0-0', '2-3', '0-1', '1-1', '1-0', '3-3', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''), 
('Italy - Serie A', 'England - EFL Cup', 'South Africa - DStv Premiership', 'England - League One', 'England - Premier League', 'Spain - Copa del Rey', 'Germany - Bundesliga', 'Portugal - Primeira Liga', 'Africa - Africa Cup of Nations', 'France - Ligue 2', 'Italy - Serie B', 'Spain - Supercopa de España', 'Netherlands - KNVB beker', 'Portugal - Taça da Liga', '', '', '', '', '', '', '', '', '', '', ''), 
(20, 24, 29, 30, 31, 34, 36, 38, 40, 42, 44, 71, 73, 74, 77, 79, 80, 83, 85, 86, '', '', '', '', ''), 
(4, 6, 7, 9, 10, 13, 17, 20, 24, 27, 29, 30, 31, 33, 35, 36, 37, 38, 45, 48, 51, 58, 61, 71, 79)
]

enter image description here

Digital Farmer
  • 1,705
  • 5
  • 17
  • 67

1 Answers1

1

In your situation, how about the following modification? In this modification, the length of each row is set as the same length.

From:

sheet_output_data = result["values"]

spreadsheets_options = list(zip(*sheet_output_data))

print(spreadsheets_options)

To:

sheet_output_data = result["values"]
col = max([len(r) for r in sheet_output_data])
for r in sheet_output_data:
    if len(r) < col:
        r = r.extend([""] * (col - len(r)))
spreadsheets_options = list(zip(*sheet_output_data))
print(spreadsheets_options)
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Hello friend, at the moment I'm not at home to be able to do the tests, but as soon as I can, I'll get back to call you! – Digital Farmer Jan 29 '22 at 02:28
  • 1
    @Brondby IF Thank you for replying. If I misunderstood your question, I apologize. – Tanaike Jan 29 '22 at 02:45
  • 1
    @Brondby By the way, in your situation, if you are not required to use `service.spreadsheets().values().get()`, I thought that as another workaround, the method for converting to CSV data like https://stackoverflow.com/a/70915111/7108653 might be able to be also used. – Tanaike Jan 31 '22 at 01:55