CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-apigspread
Rapid1898
Rapid1898

Reputation: 1220

GSpread - get also None-values when reading?

i want to read data from google sheets using gspread - but i also want the None values in the nested list -

I read the data with the following code and it works fine:

FN = "TestSheetGSPREAD"
wb1 = gc.open (FN)
ws1 = wb1.get_worksheet(0)
data = ws1.get('A3:H100').value

In the google sheet only the first column A is filled - the rest of the columns is empty.

Now i get this result:

[['AAPL'], ['FB'], ['MSFT'], ['AMZN'], ['CAT'], ['V'], ['BAYN.DE']]

But i want also the None / "" values in the result - like:

[['AAPL',None,None,None,None,None,None,None],...]

Is this somehow possible using Gspread?

Upvotes: 2

Views: 1315

Answers (1)

Tanaike
Tanaike

Reputation: 201553

Issue and workaround:

Unfortunately, in the current stage, when the methods of spreadsheets.values.get and spreadsheets.values.batchGet of Sheets API are used, the empty cells of the end of column cannot be retrieved as "". When the method of spreadsheets.get is used, the empty cells of the end of column like "" can be confirmed by checking userEnteredValue and effectiveValue. But in this case, the script is a bit complicated. So in this answer, as a workaround, I would like to propose the following flow.

  1. Export the Spreadsheet as CSV data. In this case, you can use the range.
  2. Parse the CSV data to a list.

By this flow, I thought that your goal might be able to be achieved. When this workaround is reflected to your script, it becomes as follows.

Modified script:

gc = gspread.authorize(credentials) # About "credentials", please use your script here.

FN = "TestSheetGSPREAD"
wb1 = gc.open(FN)
ws1 = wb1.get_worksheet(0)
access_token = credentials.get_access_token().access_token
rng = 'A3:H100'
url = 'https://docs.google.com/spreadsheets/d/' + wb1.id + '/gviz/tq?tqx=out:csv&gid=' + str(ws1.id) + '&range=' + quote(rng)
headers = {'Authorization': 'Bearer ' + access_token}
res = requests.get(url, headers=headers)
data = list(csv.reader(io.StringIO(res.text)))
print(data)

# If you want to convert "" to None, please use the following script.
result = [[c or None for c in r] for r in data]
print(result)

Reference:

Upvotes: 3

Related Questions