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)
