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](https://stackoverflow.com/q/63273092/7108653). How about this? — Tanaike, Oct 30 '20 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, Nov 02 '20 at 02: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, Nov 02 '20 at 02: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, Nov 02 '20 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, Nov 02 '20 at 23:32

score 0 · Answer 1 · answered Nov 02 '20 at 23:32

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:

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. — DayLy, Nov 04 '20 at 02:21
@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? — Tanaike, Nov 04 '20 at 02:29

Exporting data from python dataframe to googlesheets

1 Answers1

Modification points:

Modified script:

References: