gspread list of list of a selection of columns

Question

I have a google spreadsheet like this

A    B    C    D
0    1    2    3
4    5         7
8    9    8    7
6    5         3

I can get all these values as list of lists and save them in a DataFrame, like this:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd


scope=['my_scope']

credentials = ServiceAccountCredentials.from_json_keyfile_name('my_credentials', scope)

gc = gspread.authorize(credentials)

GsheetName = 'here_the_name_of_my_spreadsheet'
workSheetName = 'here_the_wsheet_name'

sht = gc.open(GsheetName)
wks = sht.worksheet(workSheetName)

get_values = wks.get_all_values()

df= pd.DataFrame(get_values)

so df is

   0    1    2    3
0  A    B    C    D
1  0    1    2    3
2  4    5    nan  7
3  8    9    8    7
4  6    5    nan  3

I would like to do the same, but only selecting columns B and D with NAN values where the cell is empty, like this

   0    1    
0  B    C    
1  1    2    
2  5    nan     
3  9    8    
4  5    nan     

How can I do it without manipulating df? I want to create it directly from the values of the spreadsheet.

get_all_values() method get the values of all the sheet, but I need only some columns and rows and I did not find how to define a range with this method or which other method of the library gspread allows this.

Answer 1

• You want to directly retrieve the values from "B:C" using gspread like below.

  Input values: Values on Spreadsheet
  A    B    C    D
  0    1    2    3
  4    5         7
  8    9    8    7
  6    5         3
  
  Output values: Values you want to retrieve
     0     1
  0  1     2
  1  5  None
  2  9     8
  3  5  None
  

• In your question, you said only selecting columns B and D with NAN values where the cell is empty. But it seems that your output values are the values from the column "B" and "C". So I modified to retrieve the values from the column "B" and "C".

If my understanding is correct, how about this modification?

From:

get_values = wks.get_all_values()

To:

get_values = sht.values_get(range=workSheetName + '!B:C')['values']

Note:

• If you don't use wks in your script, you can remove wks = sht.worksheet(workSheetName).

Reference:

• values_get(range, params=None)

If I misunderstood your question and this was not the result you want, I apologize.