M
M
Maruf17772021-02-07 23:36:47
Python
Maruf1777, 2021-02-07 23:36:47

How to constantly update data requested from google spreadsheet for telegram bot?

Hello, I have a problem with streaming data from google spreadsheet to telegram bot

import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import telebot
import threading
import multiprocessing

PORT = int(os.environ.get('PORT', '8443'))
TOKEN = "TOKEN"

bot = telebot.TeleBot("TOKEN")

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('delivery-302112-f683f6060657.json', scope)
client = gspread.authorize(creds)

# GET Spread SHEET Values
sh = client.open('FLEET')

coordinates = []
arrive = []
unit = []
def infi():
    # threading.Timer(20, infi).start()
    # time.sleep(15)
    sheet2 = sh.worksheet("LOCATION")
    coordinates_1 = sheet2.col_values(5)
    arrive_1 = sheet2.col_values(6)
    unit_1 = sheet2.col_values(1)
    coordinates.append(coordinates_1)
    for i, n, z in zip(coordinates_1, arrive_1, unit_1):
        coordinates.append(i)
        arrive.append(n)
        unit.append(z)
infi()
# CREATING DICTIONARY
data_dict = {"COORDINATES": pd.Series(coordinates),
             'UNIT': pd.Series(unit),
             "ARRIVE" : pd.Series(arrive)
             }

# print(data_dict)
df = pd.DataFrame(data_dict, columns=["UNIT", "COORDINATES", "ARRIVE"])
# print(df['ARRIVE'])
# print(f'DF INFO: {df.info()}, \nUNIT COLUMNS{df["column_new"]}')
unit1 = df['UNIT']
coordinates1 = df["COORDINATES"]
num1 = unit1[:16]
num2 = unit1[16:]
# # print(unit)
#
def value(type):
    unique_index = pd.Index(unit1)
    m = unique_index.get_loc(type)
    number = unit1[m]
    info1 = coordinates1[m]
    final = info1
    return final
# #
markup = telebot.types.ReplyKeyboardMarkup(row_width=3)
for i,n in zip(num1,num2):
    txt = str(i)
    mxt = str(n)
    markup.row(txt,mxt)

@bot.message_handler(commands=['start'])
def start_message(message):
    bot.send_message(message.chat.id, 'Hello U Wrote Me /start', reply_markup=markup)

@bot.message_handler(content_types=['text'])
def send_text(message):

    response = message.text.lower()
    text = value(response)

    if message.text.lower() == str(response):
        bot.send_message(message.chat.id, str(text))
    elif message.text.lower() != '':
        bot.send_message(message.chat.id, 'NaN')

if __name__ == "__main__":
    bot.polling(none_stop=True)

The essence of the bot, the choice is given to the identification numbers of employees, when choosing an ID, the bot must give data on their coordinates and their status (from the Google spreadsheet which are constantly updated) The problem is that the program takes static data once as df and does not update, I tried to use multithreading but everything just got a lot more confusing

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
shurshur, 2021-02-07
@shurshur

For example, make downloading data a separate function and remember the time of the last download. If at the moment send_text is called, the current time exceeds the last download time by more than a certain amount (5 minutes, 1 hour - how often should the data be updated?), then call the download function again and update the variable with the last download time.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question