Home / Questions / 92320
Accepted
Jorge Ponti
Asked: 2020-08-03 08:21:13 +0800 CST 2020-08-03 08:21:13 +0800 CST

Filter data imported by gspread Python 3.x

  • 772

I'm importing data from a Google Sheets spreadsheet, but I need to filter some rows and not load all of them. Does anyone run this library, and know if there is an argument to filter rows.

I am doing it as follows so far:

    import gspread
    from oauth2client.service_account import ServiceAccountCredentials

    scope=['https://spreadsheets.google.com/feeds']
    creds=ServiceAccountCredentials.from_json_keyfile_name('Python-                        cbe875b7fd9f.json', scope)
    client=gspread.authorize(creds)
    sheet=client.open('Prueba Python').sheet1
    preventivos=sheet.get_all_records()
    print(preventivos)

I do this because there is a column that has several options for example "NOGO", "MOC" and "AHM" option. I want to import only those rows that have the "AHM" option. In this way the load will be faster since now it takes several seconds and there is a lot of data that I am not interested in importing.

Example file:

https://docs.google.com/spreadsheets/d/1PWKujfqoMpdecMWNxGRxPQoA4RtmZeMVBemH6rXfVF8/edit#gid=0

python

1 Answers

  1. Best Answer

    Jorge, the documentation of this library indicates for the routine get_all_records():

    Returns a list of dictionaries, all of them having: the contents of the spreadsheet's with the head row as keys, And each of these dictionaries holding - the contents of subsequent rows of cells as values.

    That is, a list of dictionaries where each key is the column header. Something like the following, based on your example:

    lista = [
          {"B/C": "T00APSUL",   "NOMBRE": "PREVENTIVO", "FECHA": "2016-10-07 00:07:55", "AVION": "CC-BAS", "FLOTA": "A320C", "ATA": "550" , "DESCRIPCION": "NO GO VIBRATION REPORTED BY PIREP", "H1": "COMPLETE", "FUENTE": "NOGO"},
          {"B/C": "T00AQ4HM",   "NOMBRE": "PREVENTIVO", "FECHA": "2016-10-08 10:40:26", "AVION": "CC-BGG", "FLOTA": "B787", "ATA": "45" , 
           "DESCRIPCION": "DSO REQUEST R/R ENGINE DATA ACMF REPORT DOES NOT DOWNLINK", "H1": "COMPLETE", "FUENTE": "AHM"}
        ]

    I did not transcribe all the records, you must also see the behavior against columns without headers, for example the one that would be the column Hof your spreadsheet. With these data the filter is something like this:

    print([e for e in lista if e["FUENTE"] == "AHM"])

    That is, we return the rows where in the "SOURCE" column we find the text "AHM".

    • 1