-1

I am trying to extract btcusdt price from binance to a google spreadsheet on my gdrive in vain.

I tried the following:

import websocket, json, numpy as np

cc = 'btcusdt'
interval = '1m'

socket = f'wss://stream.binance.com:9443/ws/{cc}@kline_{interval}'  

from google.colab import auth
auth.authenticate_user()

import gspread
import pandas as pd
from oauth2client.client import GoogleCredentials

keyid = 'mykeyid'

gc = gspread.authorize(GoogleCredentials.get_application_default())

wb = gc.open_by_key('mykeyid')
wrksheet = wb.worksheet('btcusdt')


def on_message(ws, message):
    json_message = json.loads(message)
    cs = json_message['k']
        
    wrksheet.update('A5', cs[])
    
ws = websocket.WebSocketApp(socket, on_message=on_message) 

ws.run_forever()

I need to append each message (cs) to a new row.

Note that 'k' is a dictionary.

Thanking you in advance.

Nano
  • 1
  • 1
  • Please provide the error are you getting while running the code. – Devesh Kumar Singh Jul 05 '21 at 03:20
  • Hi! Welcome to Stackoverflow. Do you mind to include what error are you receiving when you tried to do it, please? In order to do a correct question, you should read the [How to ask](https://stackoverflow.com/help/how-to-ask) section and after that edit this question. – Inazense Jul 05 '21 at 07:57
  • Hi,in fact I am not havine – Nano Jul 05 '21 at 08:29
  • Hi, In fact I am not having error, but I want to append the data below to a google spreadsheet on my drive. I am learning by myself and don't know the technical wordings but here is what I wanted to do: Update a google spreadsheet with data received from a Binance API which returns a dictionary in this format: {"e":"kline","E":1623254972842,"s":"BTCUSDT","k":{"t":1623254940000,"T":1623254999999,"s":"BTCUSDT","i":"1m","f":899508924,"L":899513394,"o":"36224.28000000","c":"36147.27000000","h":"36249.96000000","l":"36084.01000000","v":"195.48456300",",...}} Thanking you for your help. – Nano Jul 05 '21 at 09:37

1 Answers1

0

The idea is to capture the last row of the worksheet, then get the value of that row and pass it to the find method available to determine the cell information.

def on_message(ws, message):
    json_message = json.loads(message)
    cs = json_message['k']
        
    # This will get the last row but consider other slicing options if you have headers
    last_row = wrksheet.get_all_values()[::-1]
    
    # Pass the value, I assume you only have 1 column to this from your code 'A5'
    cell = wrksheet.find(last_row[0])

    # Increment the cell row to make sure it will populate the last row + 1
    wrksheet.update('A'+str(cell.row+1), cs)
kripikroli
  • 46
  • 1