1

Basically the title.

I've been researching for this problem online because I thought I was doing something wrong, but, apparently, my code should be working fine. I will tell exactly what I'm trying to do and what is going wrong.

My code downloads an Excel sheet from Google Drive, gets some info from some columns, changes some things and then uploads it back to the drive. It all works fine, but when the code tries to get the info it is supposed to, it does not; the dataframe made from the columns are field with "nan" when the cell has a formula on it. The cells can have the most simple formula, pandas does not read it.

My suspicion is that it has to do with the fact that I download it from the drive, because when I open the sheet downloaded through code and save it manually, everything works as it is supposed to, but I couldn't find any workaround that. If anyone could point out exactly what is happening, or if there is anything that I could do to solve it, it would be very good.

Here's the code to download the sheet:

def criar(self):
    
    SCOPES='https://www.googleapis.com/auth/drive'
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        'H:\Meu Drive\DriveAPI\key.json', SCOPES)

    # Builda o objeto service
    service = build('drive', 'v3', credentials=credentials)

    #Essa parte vai definir os parametros do request à api do drive
    file_metadata = {'name': 'testes.xlsm','parents':[parentid]}
    file_name='testes.xlsm'
    mime_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    media = MediaFileUpload('H:/Meu Drive/Excell/{0}'.format(file_name),mimetype=mime_type)
    #faz a criação do arquivo no drive
    file = service.files().create(body=file_metadata,
                                        media_body=media,
                                        fields='id').execute()
    
    id_file=file['id']

def updatar(self):
    
    SCOPES='https://www.googleapis.com/auth/drive'
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        '.\key.json', SCOPES)

    # Builda o objeto service
    service = build('drive', 'v3', credentials=credentials)

    #Essa parte vai definir os parametros do request à api do drive
    file_metadata = {'name': 'test.xlsm','parents':[parentid]}
    file_name='test.xlsm'
    mime_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    media = MediaFileUpload('./{0}'.format(file_name),mimetype=mime_type)
    #updata o arquivo no drive
    file = service.files().update(media_body=media,
                                        addParents=parentid,
                                        fileId=file_id).execute()
    #return id_file

here's the code that takes the info from the columns:

marcacoeslimite1 = 12
    marcacoeslimite2 = 24
    marcacoeslimite3 = 36
    file_loc="./test.xlsm"
    marcacoes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="AN")
    coddosclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="D")
    cidadedosclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="E")
    telclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols = "AF")
    nomedosclientes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="B")
    enderecodocliente = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="AK")
    x=0
    while x < len(marcacoes):
        if (marcacoes.iloc[x][0] ==0):
            codigo = coddosclientes.iloc[x][0]
            cidade = cidadedosclientes.iloc[x][0]
            codigo = coddosclientes.iloc[x][0]
            telefone = telclientes.iloc[x][0]
            nomedocliente = nomedosclientes.iloc[x][0]
            loc = enderecodocliente.iloc[x][0]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
artursouza
  • 23
  • 5
  • Please read this https://stackoverflow.com/a/20159305/8947333 and update your code so we can execute it and solve your problem – Be Chiller Too Oct 25 '21 at 18:24
  • The dataframe is gigantic and the sheet data is sensitive. I could make a dummy sheet and send the link to download to y'all,, but I'm pretty sure the biggest problem on trying to execute this is that you would have to replicate every single stage (including dealing with google drive api) to get to my problem. – artursouza Oct 25 '21 at 18:32
  • You can create a small dataframe with only the important columns. In your question, you wrote too much code and we can't see what's inside your dataframe, so if you want answers, I suggest that you create a small reproducible example. – Be Chiller Too Oct 25 '21 at 18:35
  • I think I understand the fact that people have to do too much to replicate the problem, but also, I don't think the problem is about the sheets I am working on, I think it's actually about the interaction that pandas is having specifically with a file that is downloaded via google drive API, so even if I send a dummy sheet, you guys would still have to download it from google drive and all that. But yeah, I get it that you guys may not want to go that deep, but I can only hope. – artursouza Oct 25 '21 at 18:44
  • Hi, have you tried (1) Without usecols, or a wider range of usecols? Maybe the whole data is needed to compute formulas. (2) Using a different engine? I didn't know that formulas were supported, there is no mention of it in the docs. – creanion Oct 25 '21 at 19:03
  • I don't think those are problems because if I try the same sheet but manually saved the code works fine. BUT I like the idea of using a different engine, do you have any recomendation ? – artursouza Oct 25 '21 at 19:06

0 Answers0