CODEWITHSUNDEEP
pythonlistcsvgoogle-apps-script
Simon Breton
Simon Breton

Reputation: 2876

how to select specific row after comparison value between two list of lists

Not sure if my title is clear. Here is what I want to do. On one side, I have a csv file with 4 rows :

01/01/2017,value,value,value
02/01/2017,value,value,value
03/01/2017,value,value,value
04/01/2017,value,value,value

On the other side, I have a Google Spreadsheet with only 2 rows :

01/01/2017,value,value,value
02/01/2017,value,value,value

I want to update the google spreadsheet with the two missing rows as compared to the csv file : the rows starting with 03/01/2017 and 04/01/2017

I have the following code :

def add_todo():
    csvselection = []
    with open('data.csv', 'rb') as f:
        reader = csv.reader(f)
        for row in reader:
                csvselection.append(row)
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    spreadsheetId = 'XXXXIDXXXX'
    rangeName = 'Class Data!A2:E'
    result = service.spreadsheets().values().get(
        spreadsheetId=spreadsheetId, range=rangeName).execute()
    gsselection = result.get('values', [])

csvselection is a list of lists(?) from the csv file and gsselection is a list of lists(?) from the spreadsheet.

I don't really know what could be the most effective solution to compare my two list and select only the missing rows I want to pass into my Google Sheet.

I wanted to use something like this :

for row in gsselection:
    if row in csvselection:
        print(row)

if I'm correct this should return me the rows I don't want to copy in my spreadsheet, so I need to exclude them. However this part of code doesn't work and I need to compare only the first elements of my list of lists. I thought about this :

for row[n][0] in gsselection:
    if row[n][0] in csvselection:
        print(row)

But again I don't really now how I should write this operation properly.

For illustration and facilitate answers, here is the part of the code I'm using to actually past the data I want into my google sheet :

values = {'values': test}
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheetId, range='A:C',
    valueInputOption='RAW',
    body=values).execute()

Any idea ?

Thanks !

Upvotes: 0

Views: 219

Answers (1)

Mohammad Yusuf
Mohammad Yusuf

Reputation: 17064

Try with pandas like so:

import pandas as pd

df = pd.read_csv('csvfile', header=None)
df2 = pd.read_excel('googlesheet', header=None)
df3 = pd.concat([df,df2]).drop_duplicates([0])
df3.to_excel('final_file')

Upvotes: 1

Related Questions