'Workaround Google Sheets API does not accept range request without specifying desired final line

My spreadsheet has values in this model:

enter image description here

And I need to create a list to use in Python, including the empty fields that exist between values:

    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 = sheet_id
    get_page_id = 'Winning_Margin'
    range_score = 'O1:O10000'

    spreadsheets_match_score = []
    range_names2 = get_page_id + '!' + range_score
    result2 = service.spreadsheets().values().get(
                spreadsheetId=spreadsheet_id, range=range_names2, valueRenderOption='UNFORMATTED_VALUE').execute()
    sheet_output_data2 = result2["values"]
    for i, eventao2 in enumerate(sheet_output_data2):
        try:
            spreadsheets_match_score.append(sheet_output_data2[i][0])
        except:
            spreadsheets_match_score.append('')

In this case, this list (spreadsheets_match_score = []) would result in:

["0-0","0-0","4-0","0-1","6-0","","","","0-3","2-2","","","","","0-1","","","3-0","1-1","3-1","","","",""]

My spreadsheet currently has 24 rows, but it will grow without a fixed ending value.

So, I tried to use the range without putting the value of the last line (range_score = 'O1:O'), but it doesn't accept, the range needs to specify the final line (range_score = 'O1:O10000').

I put 10000 exactly so I don't have to change, but this is very wrong to do, because it does a search for a non-existent range, I'm very afraid that in the future it will generate an error.

Is there any method so that I can not need to specify the last row of the worksheet?
To be something like:

range_score = 'O1:O'
google-sheets-api


Solution 1:[1]

The problem is not in the range specification method for data collection, can use either range_score = 'O1:O' or range_score = 'O1:O100000000000' if looking for all the column rows.

In the case of the question, the problem was when line 1 of the desired column has no values, being null, the request failed but because of the empty ["values"] return.

In short, I was looking for the error in the wrong place.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Digital Farmer

Related Questions