CODEWITHSUNDEEP
python-3.xgoogle-sheets-api
stardamore
stardamore

Reputation: 150

Posting data to new row in Google Sheets (Python)

So here's the problem that I'm facing. I've created a python script which takes the data from the first column of my Google Sheet, passes that into another API, gets the response, and then adds some fields from that response in the next columns of that row. The problem is that every time it updates the fields, it overwrites everything in the first row only and doesn't actually move to the second row.

For example, if column A1 has the value x and that gets decoded into b,c,d. Then it writes b c d in columns B1, C1, and D1 respectively. That's all well and good. But when it moves onto A2, it writes the decoded values for that in B1, C1, and D1 as well instead of moving to B2, C2, D2. So it overwrites the last added values.

    # I've called the Sheets API here
SPREADSHEET_ID = 'ID HERE'
RANGE_NAME = 'A2:A'
result = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID,
                                            range=RANGE_NAME).execute()
values = result.get('values', [])
i=1

if not values:
    print('No data found.')
else:
#values 
    for row in values:
        try:
        # Print column A, which corresponds to indice 0 in the VIN Sheet.
            print('%s' % (row[0]))
            URL = "http://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/"
            VIN=row[0]
            PARAMS={VIN:'format=json'}
            r=requests.get(url=URL + VIN, params={'format': 'json'})
            result=r.json()
            #print (result)
            print ("Make: ", result['Results'][0]['Make'])
            print ("Model: ", result['Results'][0]['Model'])
            print ("Year: ", result['Results'][0]['ModelYear'])
            print ("Engine Manufacturer: ", result['Results'][0]['EngineManufacturer'])
            i=i+1
            text=r.text
            values = [
                [ result['Results'][0]['Make'],result['Results'][0]['Model'],result['Results'][0]['ModelYear'],result['Results'][0]['EngineManufacturer']
                ]
                ]
            body = {
                'values' : values
                }
            result1 = service.spreadsheets().values().update(spreadsheetId=SPREADSHEET_ID, range='B:E',valueInputOption='RAW', body=body).execute()
        except IndexError, e:
            print("Row has no index")

Can someone please tell me what I'm doing wrong? I've tried using the append() function instead of the update() but that obviously starts adding the results at the bottom of the table.

Upvotes: 1

Views: 1715

Answers (1)

glhr
glhr

Reputation: 4537

The problem is in the range parameter of your update() function call. In your code, the range is set to B:E in every iteration of the for loop, so it's always writing to the same 4 cells: B1:E1.

Instead, you should increment the range by one row every time update() is called. Assuming you want to start writing in row 2: B2:E2, B3:E3, etc.

Here's a working proof of concept based on your code, that reads the values from column A (range A2:A) and writes each value to columns B through E in the corresponding row:

for i, row in enumerate(values, start=2):
    # Print column A, which corresponds to indice 0 in the VIN Sheet.
    print('Input value: %s' % (row[0]))
    values = [[row[0], row[0], row[0], row[0]]]
    body = {
        'values': values
        }

    # range of cells to be updated (eg. B2:E2 on first iteration)
    destrange = 'B' + str(i) + ':E' + str(i)
    print("Writing to " + destrange)

    result1 = service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID,
        range=destrange,
        valueInputOption='RAW',
        body=body).execute()

Note that I'm using the loop index i starting at 2 to define the range.

Upvotes: 1

Related Questions