def postongdoc(dataframe, username, spreadsheetname, sheetname): SPREADSHEET = spreadsheetname USER = username s = Spread(USER, SPREADSHEET) s.df_to_sheet(dataframe, index=False, sheet=sheetname, start='A2', replace=True) s.update_cells((1, 1), (1, 2), ['Created by:', s.email])
def get_sheet(SHEET, CSV_SPREADSHEET='Asistensi PBO Jumat 2019'): spread = Spread(CSV_SPREADSHEET) spread.open_sheet(SHEET) df = spread.sheet_to_df() df['Nilai'] = pd.to_numeric(df['Nilai'], errors='coerce') links = [] for link in tqdm(df.REPO, desc='issue'): try: links.append(get_issues(link)) except: links.append(None) print(links) df['Issues'] = links spread.update_cells('H2', 'H28', links) df.drop(['Catatan', 'REPO'], axis=1, inplace=True) return df
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')
# This will ask to authenticate if you haven't done so before for 'example_user' # Display available worksheets spread.sheets file_name = "http://www.ats.ucla.edu/stat/data/binary.csv" df = pd.read_csv(file_name) # Save DataFrame to worksheet 'New Test Sheet', create it first if it doesn't exist spread.df_to_sheet(df, index=False, sheet='New Test Sheet', start='A2', replace=True) spread.update_cells((1, 1), (1, 2), ['Created by:', spread.email]) print(spread) # <gspread_pandas.client.Spread - User: '******', Spread: 'Example Spreadsheet', Sheet: 'New Test Sheet'> #%% #this works from gsheets import Sheets sheets = Sheets.from_files('~/.config/gspread_pandas/google_secret.json', '~/.config/gspread_pandas/storage.json') sheets #doctest: +ELLIPSIS url = 'https://docs.google.com/spreadsheets/d/1s-FFmQQdOJ1DdJsJneTWanF096REzaXRxD7dpKAZr_s/edit#gid=0' s = sheets.get(url) s
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)