How do I append data in google sheet using python?

I am trying to download the data from IB api and upload it onto a google sheet. If the data already exists in the sheet, then I want the new data to be added into the sheet without overwriting the previous data. This is what I have done so far:

from ibapi.client import EClient
from ibapi.wrapper import EWrapper
from ibapi.contract import Contract
from threading import Timer
import pandas as pd
from datetime import datetime
import os
import pygsheets

class TestApp(EWrapper, EClient):
    def __init__(self):
        EClient.__init__(self, self)
        self.df = pd.DataFrame(columns=['accountName', 'contract', 'position','marketPrice', 'marketValue', 'averageCost', 'unrealizedPNL','realizedPNL'])
        self.df1 = pd.DataFrame(columns=['key', 'value', 'currency'])

    def error(self, reqId, errorCode, errorString):
        print("Error: ", reqId, " ", errorCode, " ", errorString)

    def nextValidId(self,orderId):
        self.start()

    def updatePortfolio(self, contract: Contract, position: float, marketPrice: float, marketValue: float,
                        averageCost: float, unrealizedPNL: float, realizedPNL: float, accountName: str):
        self.df.loc[len(self.df)] = [accountName, contract.symbol,position,marketPrice, marketValue,averageCost, unrealizedPNL,realizedPNL]

        # See unrealized P&L, which will be the total unrealized P&L since the position was open, the
        # realized P&L which would be the realized profit loss for the current day

        #  If you’ve closed out any positions, as well as the account name, the current market value,
        #  the average cost used to open the position and of course the position size

        print("UpdatePortfolio.", "Symbol:", contract.symbol, "SecType:", contract.secType, "Exchange:",
              contract.exchange,
              "Position:", position, "MarketPrice:", marketPrice, "MarketValue:", marketValue, "AverageCost:",
              averageCost,
              "UnrealizedPNL:", unrealizedPNL, "RealizedPNL:", realizedPNL, "AccountName:", accountName)

    def updateAccountValue(self, key: str, val: str, currency: str, accountName: str):
        # returns the cash balance, the required margin for the account, or the net liquidity

        self.df1.loc[len(self.df)] = ['ExchangeRate', val, currency]
        print("UpdateAccountValue. Key:", key, "Value:", val, "Currency:", currency, "AccountName:", accountName)

    def updateAccountTime(self, timeStamp: str):
        print("UpdateAccountTime. Time:", timeStamp)

    def accountDownloadEnd(self, accountName: str):
        print("AccountDownloadEnd. Account:", accountName)

    def start(self):
        # ReqAccountUpdates - This function causes both position and account information to be returned for a specified account
        # Invoke ReqAccountUpdates with true to start a subscription
        self.reqAccountUpdates(True, "U0000000") # <----- Change account number here 

    def stop(self):
        self.reqAccountUpdates(False,"U00000") # <----- Change account number here 
        self.done = True
        self.disconnect()


def main():
    app = TestApp()
    app.connect("127.0.0.1", 7496, 0)

    Timer(5, app.stop).start()
    app.run()


    # service file is the ib_data json file key
    service_file_path = "googlesheet_api_json"

    gc = pygsheets.authorize(service_file= service_file_path )

    spreadsheet_id = '00000'
    sh = gc.open_by_key(spreadsheet_id)

    # inserting date and time to the data
    now = datetime.now()
    dt_string = now.strftime("%d/%m/%Y %H:%M:%S")
    app.df['Date_Time'] = dt_string

    #check if the google sheet has previous data

    wk_sheet_stock = gc.open('workbook').sheet1
    cells = wk_sheet_stock.get_all_values(include_tailing_empty_rows=False, include_tailing_empty=False, returnas='matrix')

    if len(cells) < 1:
        print('no data in the file')
        data_write = sh.worksheet_by_title('stocks')
        data_write.clear('A1',None,'*')

        data_write.set_dataframe(app.df, (1,1), encoding='utf-8', fit=True)
        data_write.frozen_rows = 1

        data_write = sh.worksheet_by_title('currency')
        data_write.clear('A1', None, '*')
        data_write.set_dataframe(app.df1, (1, 1), encoding='utf-8', fit=True)
        data_write.frozen_rows = 1

    else:
        print('adding data to the file')

        stock_df_values = app.df.values.tolist()
        currency_df_values = app.df1.values.tolist()

        sh.values_append('stocks', {'valueInputOption': 'RAW'}, {'values': stock_df_values})
        sh.values_append('currency', {'valueInputOption': 'RAW'}, {'values': currency_df_values})
        
if __name__ == "__main__":
   main()

I get the error: AttributeError: 'Spreadsheet' object has no attribute 'values_append'

I am confused as to how do I go about this. Thank you for your help!

I believe your goal is as follows.

• You want to append the values using values_append.
• You want to remove your current issue of I get the error: AttributeError: 'Spreadsheet' object has no attribute 'values_append'.
• You want to achieve this using pygsheets for python.

Modification points:

• When I saw the document of pygsheets, it seems that the method of values_append is for class pygsheets.sheet.SheetAPIWrapper. In this case, sh of sh = gc.open_by_key(spreadsheet_id) is not it. I think that this might be the reason for your current issue of AttributeError: 'Spreadsheet' object has no attribute 'values_append'.

• And, I cannot know your values of stock_df_values and currency_df_values. So, in this answer, I propose a modification point by guessing that those values are the 2-dimensional array. Please be careful about this.

When these points are reflected in your script, how about the following modification?

From:

sh.values_append('stocks', {'valueInputOption': 'RAW'}, {'values': stock_df_values})
sh.values_append('currency', {'valueInputOption': 'RAW'}, {'values': currency_df_values})

To:

gc.sheet.values_append(spreadsheet_id, stock_df_values, "ROWS", "stocks")
gc.sheet.values_append(spreadsheet_id, currency_df_values, "ROWS", "currency")

Note:

• I guessed that the values of "stocks" and "currency" are the sheet names in the Spreadsheet of sh = gc.open_by_key(spreadsheet_id). Please be careful about this.

• If you cannot know whether the values of stock_df_values and currency_df_values are a 2-dimensional array, please modify them as follows.

    gc.sheet.values_append(spreadsheet_id, stock_df_values if isinstance(stock_df_values[0], list) else [stock_df_values], "ROWS", "stocks")
    gc.sheet.values_append(spreadsheet_id, currency_df_values if isinstance(currency_df_values[0], list) else [currency_df_values], "ROWS", "currency")
  

• In this answer, I suppose that other parts except for sh.values_append of your script work fine. Please be careful about this.

Reference:

• values_append