如何使用从Binance API接收的数据更新谷歌电子表格

Question

我试图从binance中提取btcusdt价格到我的gdrive上的谷歌电子表格，但没有成功。

我尝试了以下方法：

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()

我需要将每条消息(cs(附加到一个新行中。

请注意，"k"是一本字典。

提前感谢您。

Answer 1

我们的想法是捕获工作表的最后一行，然后获取该行的值，并将其传递给可用于确定单元格信息的find方法。

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)