Export data excel to google sheets with Python

Question

Going through an itertuples, I need to export two data, located in two rows of an excel column ('G7', 'G8') to two columns of google sheets. How can I do this?

import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
from oauth2client.service_account import ServiceAccountCredentials
import pyperclip
import pyautogui as p
import rpa as r
import pandas as pd
import tabula
import openpyxl

r.init()
r.url('https://www.meudetran.ms.gov.br/veiculo.php#')
p.sleep(2)
janela = p.getActiveWindow()
janela.maximize()
p.sleep(2)

scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key('1AGYhinoPiE9xUABnrNEfVGjLf5s_bAJGjpz9hatfIQU')
worksheet = wks.get_worksheet(0)
dados = get_as_dataframe(worksheet)
df = pd.DataFrame.from_records(dados, columns=["Placa", "Renavam"])
set_with_dataframe(worksheet, df)
df2 = get_as_dataframe(worksheet)

for row in df2.itertuples():
    df = tabula.read_pdf(text, pages=1)[1]
    df.to_excel('dados.xlsx')
    wb = openpyxl.load_workbook('dados.xlsx')
    sheet = wb.active
    venc = sheet['G8'].value
    valor = sheet['G7'].value
    worksheet.update(row[3], venc)

This last line does not update column 3 of the google sheet

Answer 1

I believe your goal and your current situation as follows.

• You want to retrieve the values from the cells "G7" and "G8" from 1st tab in the XLSX data converted from a PDF data.
  • You have already achieved this.
• You want to append the values to the columns "C" and "D" in the Spreadsheet every run of script.
  • For example, at 1st run, you want to put the retrieved values of "G7" and "G8" to the cells "C2" and "D2" of Spreadsheet. And, at 2nd run, you want to put the retrieved values of "G7" and "G8" to the cells "C3" and "D3" of Spreadsheet. You want to do this cycle.
• You have already been able to get and put values for Google Spreadsheet using Sheets API.

Modification points:

• In your script, the values retrieved from Spreadsheet are converted to the dataframe. I thought that in your situation, this might not be required.
• In this modification, I would like to propose the following flow.
  1. Retrieve the values from "G7" and "G8" from the XLSX data converted from PDF data.
  2. Retrieve the values from the columns "C" and "D" and retrieve the last row of the columns "C" and "D".
  3. Append the retrieved values to the columns "C" and "D" in Google Spreadsheet.

When above points are reflected to your script, it becomes as follows.

Modified script:

In this modified script, I modified below gc = gspread.authorize(credentials) in your script.

gc = gspread.authorize(credentials)
wks = gc.open_by_key('###') # Please set your Spreadsheet ID.
worksheet = wks.get_worksheet(0)

# 1. Retrieve the values from "G7" and "G8" from the XLSX data converted from PDF data.
df = tabula.read_pdf(text, pages=1)[1]
df.to_excel('dados.xlsx')
wb = openpyxl.load_workbook('dados.xlsx')
sheet = wb.active
venc = sheet['G8'].value
valor = sheet['G7'].value

# 2. Retrieve the values from the column "C" and retrieve the last row of the columns "C" and "D".
lastRow = max([len(worksheet.col_values(3)), len(worksheet.col_values(4))])

# 3. Append the retrieved values to the columns "C" and "D" in Google Spreadsheet.
worksheet.update('C' + str(lastRow + 1), [[valor, venc]])

• In this modified script, it supposes that df = tabula.read_pdf(text, pages=1)[1] works fine. Please be careful this.
• By above modification, the retrieved values valor, venc are appended to the columns "C" and "D" every run.

References:

• Getting All Values From a Row or a Column
• Updating Cells