def main_function(self): with open(self.TWITTER_CREDS_LOCATION) as f: twitter_credentials = json.load(f) api = twitter.Api( consumer_key=twitter_credentials['CONSUMER_KEY'], consumer_secret=twitter_credentials['CONSUMER_SECRET'], access_token_key=twitter_credentials['ACCESS_TOKEN'], access_token_secret=twitter_credentials['ACCESS_TOKEN_SECRET'], tweet_mode='extended') relevant_members = api.GetListMembers(list_id=self.TWITTER_LIST_ID) master_list = [] for user in relevant_members: recent_tweets = api.GetUserTimeline(user_id=user.id) for status in recent_tweets: result = None result = self.pull_status(status) if result: master_list.append(result) df = pd.DataFrame( master_list, columns=['title', 'link', 'retweets', 'seconds_since']) mask = (df.title.str.split(' ').str.len() > self.minimum_words_in_title) df = df[mask] self.df = df[df['seconds_since'] <= self.time_limit] self.df.title = self.df.title.str.replace('\n', '').replace( '\r', '').replace('\t', '') reset = self.df.groupby('title')['seconds_since'].min().reset_index() self.df = reset.merge(self.df, how='inner', on=['title', 'seconds_since']) with open(self.GOOGLE_SERVICE_CREDS_LOCATION) as f: service_account = json.load(f) spread = Spread(self.GOOGLE_SHEETS_ID, config=service_account, sheet=self.sheet_name) spread.clear_sheet(sheet=self.sheet_name) update_time = datetime.strftime(datetime.now() - timedelta(hours=4), '%Y-%m-%d %H:%M:%S') update_time = f'Last Updated: {update_time} EST' spread.update_cells(start='A1', end='A1', sheet='last_updated', vals=[update_time]) spread.df_to_sheet(self.df, index=False, sheet='news', start='A1')
class GoogleSheets(object): def __init__(self, file_name): self.file_name = file_name """Authenication from oauth token from "My First Project", project in google developer console - owned by [email protected] user""" self.spread = Spread(('1096395197574-0lbfvsdbd5crh7hnlgmm4asll1u3f0g5' '.apps.googleusercontent.com'), self.file_name) def to_csv(self, sheet_name, output_file_path): df = self.spread.sheet_to_df(sheet=sheet_name, index=0) df.to_csv(output_file_path, index=False) def csv_to_sheet(self, csv_path, start_tuple, output_sheet_name): df = pd.read_csv(csv_path) self.spread.df_to_sheet(df, start=start_tuple, sheet=output_sheet_name, index=False) def clear_sheet(self, sheet_name): # self.spread.open_sheet(sheet=sheet_name) self.spread.clear_sheet(sheet=sheet_name, rows=0, cols=0) def df_to_sheet(self, df, sheet_name): self.spread.df_to_sheet(df=df, sheet=sheet_name, index=False, headers=True, replace=True, freeze_headers=True) def sheet_to_df(self, sheet_name, index=1, header_rows=1, start_row=1): return self.spread.sheet_to_df(sheet=sheet_name, index=index, header_rows=header_rows, start_row=start_row)
import os import openpyxl import pandas as pd import wget from gspread_pandas import Spread, Client import sys sheet_link = sys.argv[1] sheet_name = sys.argv[2] parameters_dict = {} parameters_list = [] file = wget.download(sheet_link) book = openpyxl.load_workbook(file) sheet = book[sheet_name] for i in range(2, sheet.max_row + 1): # to get rows parameters_dict = {} for j in range(1, sheet.max_column + 1): # to get columns parameters_dict[sheet.cell(row=1, column=j).value] = sheet.cell(row=i, column=j).value parameters_list.append(parameters_dict) os.remove(os.path.join(os.getcwd(), file)) lists = parameters_list for each_list in lists: spread = Spread(each_list["gsheet_workbook_name"]) data = pd.read_excel(each_list["excel_location"], each_list["excel_sheet_name"]) spread.clear_sheet(spread.get_sheet_dims(each_list["gsheet_sheet_name"])[0], spread.get_sheet_dims(each_list["gsheet_sheet_name"])[1], each_list["gsheet_sheet_name"]) spread.df_to_sheet(data, index=False, sheet=each_list["gsheet_sheet_name"], start='A1', replace=True)
class GoogleSpread(object): def __init__(self, spread, sheet=0, creds=None, create_sheet=False, conf_file=None): if creds: self.creds = creds else: credentials = ParseConfiguration(conf_file).get_google_creds() self.creds = get_creds(config=credentials) self.spread = Spread(spread, sheet=sheet, creds=self.creds, create_sheet=create_sheet) @property def spread_url(self): return self.spread.url @property def worksheet_name(self): return self.spread.sheet @property def spread_name(self): return self.spread.spread def df_to_sheet(self, df, index=True, headers=True, start_cell=(1, 1), sheet=None, replace=False): self.spread.df_to_sheet(df, index=index, headers=headers, start=start_cell, sheet=sheet, replace=replace) def sheet_to_df(self, index=1, header_rows=1, start_row=1, sheet=None): return self.spread.sheet_to_df(index=index, header_rows=header_rows, start_row=start_row, sheet=sheet) def find_sheet(self, sheet): return self.spread.find_sheet(sheet) def open_sheet(self, sheet, create=False): self.spread.open_sheet(sheet, create=create) def update_cells(self, start, end, vals, sheet=None): self.spread.update_cells(start, end, vals, sheet=sheet) def get_sheet_dims(self, sheet=None): return self.spread.get_sheet_dims(sheet=sheet) def clear_sheet(self, rows=1, cols=1, sheet=None): self.spread.clear_sheet(rows=rows, cols=cols, sheet=sheet) def create_sheet(self, name, rows=1, cols=1): self.spread.create_sheet(name, rows=rows, cols=cols) def delete_sheet(self, sheet): self.spread.delete_sheet(sheet)