Exporting data from python dataframe to googlesheets

Question

I am trying to export a python pandas dataframe into google sheets but the values that appear in the cells all start with an apostrophe ('). Is there a way for me to format the values so the output would be a normal number without manually formatting the numbers on google sheets? I am using df2gspread to upload my data to google sheets. I'm assuming this is because the numbers are being read as str, but I did pd.to_numeric to all numerical values so I thought it would have resolved but no. Any ideas on how I should approach/fix this?

import gspread
from df2gspread import df2gspread as d2g
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import numpy as np

scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']

#service_account from Google API.
credentials = ServiceAccountCredentials.from_json_keyfile_name(
         "service_account.json", scope)
gc = gspread.authorize(credentials)
wks = gc.open("Two").sheet1
data = wks.get_all_values()
headers = data.pop(0)
ssid = 'insert-ssid'
wks_name = 'Transform'
wks_name1 = 'Summary'
cell_of_start_df = 'B2'

pd.set_option('display.max_rows', 300)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)

df = pd.DataFrame(data, columns=headers)
df2 = ...

d2g.upload(df,
    ssid,
    wks_name,
    credentials = credentials,
    col_names = True,
    row_names = True,
    start_cell = cell_of_start_df,
    clean = True)

d2g.upload(df2,
    ssid,
    wks_name1,
    credentials = credentials,
    col_names = True,
    row_names = True,
    start_cell = cell_of_start_df,
    clean = True)

I get AttributeError: 'str' object has no attribute 'values_update' when using the update method

values = [df.columns.values.tolist()]
values.extend(df.values.tolist())
wks_name.values_update('Transform', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

values2 = [df2.columns.values.tolist()]
values2.extend(df2.values2.tolist())
wks_name1.values_update('Summary', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

My error occurs in line wks_name.values_update saying 'str' has no attribute "values update". I have tried changing possible variables but it seems like I'm missing something.

I thought that your issue might be related to this thread. How about this? — Tanaike
– Tanaike, Commented Oct 30, 2020 at 23:56
I tried a few variations of that thread but I keep getting an issue where it would say '____' has no attribute to '____'. My new attempt looks something like. gc = gspread.authorize(credentials) wks = gc.open("Project").sheet1 data = wks.get_all_values() ssid = 'insert sheet id' wks_name = 'Transform' values = [df.columns.values.tolist()] values.extend(df.values.tolist()) wks_name.values_update('Transform', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values}) — DayLy
– DayLy, Commented Nov 2, 2020 at 2:14
Thank you for replying. I have 2 questions for your replying. 1. Where does the error of I keep getting an issue where it would say '____' has no attribute to '____'. occur? 2. When the script is put to the comment, I think that the readability becomes low. So can you add the script in your question? — Tanaike
– Tanaike, Commented Nov 2, 2020 at 2:34
Sure, here is pretty much the main import/export function i'm using. The error happens on the wks_name.values_update portion. — DayLy
– DayLy, Commented Nov 2, 2020 at 15:39
Thank you for your response. From your response, I posted a modified script as an answer. Could you please confirm it? — Tanaike
– Tanaike, Commented Nov 2, 2020 at 23:32

Tanaike · Accepted Answer · 2020-11-02 23:32:10Z

0

Modification points:

In your script, it seems that wks_name and wks_name1 are the string values. I think that this is the reason of your issue.
At gspread, it seems that values_update is the method of Class gspread.models.Spreadsheet.

About your additional script,

  values = [df.columns.values.tolist()]
  values.extend(df.values.tolist())
  wks_name.values_update('Transform', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

  values2 = [df2.columns.values.tolist()]
  values2.extend(df2.values2.tolist())
  wks_name1.values_update('Summary', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

I think that the 1st part can be used. But about 2nd part, although I'm not sure about df2, I think that df2.values2.tolist() and {'values': values} might be df2.values.tolist() and {'values': values2}, respectively.

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

Modified script:

df = ...
df2 = ...

gc = gspread.authorize(credentials)
wks = gc.open("Two")

values = [df.columns.values.tolist()]
values.extend(df.values.tolist())
wks.values_update('Transform', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})

values2 = [df2.columns.values.tolist()]
values2.extend(df2.values.tolist())
wks.values_update('Summary', params={'valueInputOption': 'USER_ENTERED'}, body={'values': values2})

Here, 'valueInputOption': 'USER_ENTERED' is used.

References:

answered Nov 2, 2020 at 23:32

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DayLy Over a year ago

I'm having troubles using this method. Does this only work if the dtypes are all the same? Some variables that were working fine with the upload method are suddenly raising errors.

Tanaike Over a year ago

@DayLy Thank you for replying. I apologize for the inconvenience. Unfortunately, I cannot replicate your situation. Because when I tested the modified script, I can confirm that the script works. This is due to my poor skill. I deeply apologize for this. In order to correctly understand about your issue, can you provide the script for replicating your issue? By this, I would like to confirm it. When you can cooperate to resolve your issue, I'm glad. If you can do, please add it to your question. Can you cooperate to resolve your issue?

Collectives™ on Stack Overflow

Exporting data from python dataframe to googlesheets

1 Answer 1

Modification points:

Modified script:

References:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related