def get_google_spread_sheet(spread_sheet_name, site_name):
'''
Function that uses gspread_pandas to get a spreadsheet from google sheets and place it into a dataframe. See https://gspread-pandas.readthedocs.io/en/latest/getting_started.html
for more details.
:param spread_sheet_name: The name of the google spreadsheet to pull
:param site_name: The specific tab that you want to work with within the spread sheet.
:return: Returns a google sheet in a pandas dataframe.
'''
#Make sure the the objects are strings. Useful in cases where a site name may be an integer.
spread_sheet_name = str(spread_sheet_name)
site_name = str(site_name)
print("Pulling the Google sheet...")
#Start a timer to see how long it takes.
# start = timer()
#Try to pull the sheet, which requires having a functioning api token.
try:
spread = gspread_pandas.Spread(str(spread_sheet_name))
url = str(spread.url)
print("Retrieved {} from:\n".format(str(spread_sheet_name)))
print(" ", url)
spread.open_sheet(str(site_name))
print("Converting to data frame...")
df = spread.sheet_to_df(header_rows=1)
print('Data frame size is {rows} rows and {columns} columns'.format(
columns=int(df.shape[1]), rows=int(df.shape[0])))
# end = timer()
except:
print("Couldn't retrieve google spread sheet {}".format(site_name))
# elapsed = abs(start - end)
# print("Operation took: {:10.4f} seconds".format((float(elapsed))))
return df
def push_to_docs(df, email, book_name, sheet_name):
print("Pushing to docs .... ")
spread = gspread_pandas.Spread(email, book_name, sheet_name)
spread_df = spread.sheet_to_df()
spread_df = spread_df.append(df)
spread.df_to_sheet(spread_df)
#tab = workbook.fetch_tab(sheet_name)
#tab.insert_data(df)
return None
def write_to_sheet(outfile, credentials_path):
df = pd.read_csv(outfile)
spread = gspread_pandas.Spread(
config.SPREADSHEET_ID,
config=gspread_pandas.conf.get_config(file_name=credentials_path))
spread.df_to_sheet(df,
index=False,
sheet='Sheet1',
start='A1',
replace=True)
return
je_folder = [
file['id'] for file in file_list
if file['title'] == 'Jakub Eichler - mentoring'
][0]
file_list = drive.ListFile({
'q': f"'{je_folder}' in parents and trashed=false"
}).GetList()
#[print(e['title']) for e in file_list]
sql_folder = [file['id'] for file in file_list if file['title'] == 'SQL'][0]
file_list = drive.ListFile({
'q': f"'{sql_folder}' in parents and trashed=false"
}).GetList()
#[print(e['title']) for e in file_list]
sql_tutorial_db_sheet = [
file['id'] for file in file_list if file['title'] == 'sql_tutorial_db'
][0]
sql_tutorial_db_sheet = gp.Spread(sql_tutorial_db_sheet, creds=credentials)
sql_tutorial_db_sheet.sheets
sql_letters = sql_tutorial_db_sheet.sheet_to_df(sheet='letters', index=0)
sql_people = sql_tutorial_db_sheet.sheet_to_df(sheet='people', index=0)
sql_cities = sql_tutorial_db_sheet.sheet_to_df(sheet='cities', index=0)
#%% create a sql database and upload tables
connection = sqlite3.connect('je_tutorial.db')
cursor = connection.cursor()
sql_letters.to_sql('letters', connection, if_exists='replace', index=False)
sql_people.to_sql('people', connection, if_exists='replace', index=False)
sql_cities.to_sql('cities', connection, if_exists='replace', index=False)
connection.close()
# --- Structures for supported distributors --- #
unsupported_items = {'Unsupported Distributors': []}
digikey_items = {
'Link': [],
'Part': [],
'Price': [],
'Description': [],
'Detailed Description': []
}
# --- Load in links --- #
spreadsheet = gspread_pandas.Spread('GLV BOM', config=c)
urls = fio.read_urls(spreadsheet)
# --- Get distributors --- #
data = list() # Final list of all dataframes
for url in urls:
dstbtr = get_distributer(url)
if (dstbtr == 'digikey'):
digi_item = items.DigiKeyItem(url)
def get_price_history_df(end_date=today_date, start_date="2000-01-01"):
import gspread_pandas
# worksheet = gspread_pandas.Spread("stocks", "DJP stocks")
#TODO: Create a better way to save this config
worksheet_config = {
"type":
"service_account",
"project_id":
"djp-portfolio",
"private_key_id":
"669cd846c30e067d26d504007addfa5ad4c5bd03",
"private_key":
os.environ.get("GS_API_KEY"),
"client_email":
"*****@*****.**",
"client_id":
"107324578022794643121",
"auth_uri":
"https://accounts.google.com/o/oauth2/auth",
"token_uri":
"https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url":
"https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url":
"https://www.googleapis.com/robot/v1/metadata/x509/portfolio%40djp-portfolio.iam.gserviceaccount.com"
}
import pdb
pdb.set_trace()
worksheet = gspread_pandas.Spread(
"stocks",
"DJP stocks",
config={
"type":
"service_account",
"project_id":
"djp-portfolio",
"private_key_id":
"669cd846c30e067d26d504007addfa5ad4c5bd03",
"private_key":
os.environ.get("GS_API_KEY"),
"client_email":
"*****@*****.**",
"client_id":
"107324578022794643121",
"auth_uri":
"https://accounts.google.com/o/oauth2/auth",
"token_uri":
"https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url":
"https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url":
"https://www.googleapis.com/robot/v1/metadata/x509/portfolio%40djp-portfolio.iam.gserviceaccount.com"
})
worksheet.open_sheet("Price history")
history_sheet = worksheet.sheet_to_df(index=0)
history_sheet["date"] = pd.to_datetime(history_sheet["date"],
format="%Y-%m-%d")
history_sheet["price"] = history_sheet["price"].apply(float)
history_sheet["units"] = history_sheet["units"].apply(float)
history_sheet["name"] = history_sheet["stock"].str.split(":").str[1]
history_sheet["value"] = history_sheet.apply(
lambda row: row["price"] * row["units"], axis=1)
return history_sheet[(history_sheet["date"] >= start_date)
& (history_sheet["date"] <= end_date)]
sh.share(json.load(open('secret_key.json'))["client_email"],
perm_type='user',
role='writer')
# Add a worksheet to spreadsheet
worksheetName = 'work1'
try:
sh.add_worksheet(title=worksheetName, rows='10', cols='10')
except gspread.exceptions.APIError:
deleteYN = input(
"A worksheet with that name already exists. Type 'y' to delete or 'n' to choose a new name: "
)
if deleteYN == 'y':
sh.del_worksheet(worksheet=sh.worksheet(title=worksheetName))
elif deleteYN == 'n':
worksheetName = input('Enter new worksheet name: ')
sh.add_worksheet(title=worksheetName, rows='10', cols='10')
else:
pass
finally:
print("No errors.")
df = af.parse10k()
spread = gspread_pandas.Spread(json.load(
open('secret_key.json'))["client_email"],
spreadsheetName,
config=json.load(open('secret_key.json')))
spread.df_to_sheet(df=df, index=False, sheet='work1', start='A1', replace=True)
# authorization - wklejasz linki api google do przestrzeni w jakich chcesz operować, jest tego w opór na stronie googla
scopes = [
'https://www.googleapis.com/auth/spreadsheets', # odpowiada za edycję arkuszy
'https://www.googleapis.com/auth/drive' # odpowiada za operacje na plikach i folderach na dysku
]
# credentials z google cloud manager, ta opcja dla cos tam usługi
credentials = Credentials.from_service_account_file(
r"C:/Users/Cezary/Documents/IBL-PAN-Python/pythonsheets-credentials.json",
scopes=scopes)
gc = gspread.authorize(credentials)
#Translation test
s = gp.Spread('1t_-_cxUzvRG2iYyKN6WMXmk7wxC9imnrFfowYJ8oIWo',
creds=credentials)
s.sheets
df = s.sheet_to_df(sheet='multiple volumes', index=0)
# nazwa arkusza i id folderu, gdzie ma się pojawić, wczeniej trzeba udostepnić ten folder dla mejla usługi z pliku json credentials
sheet = gc.create('test', '1DjHGgS7rpWniyIY9FctYg60qkVtUcGpF')
sheet.id
s = gp.Spread(sheet.id, creds=credentials)
s
s.sheets
s.url
s.df_to_sheet(artykuly, sheet='trzeci')
df = s.sheet_to_df(sheet='trzeci', index=0)
results_data = get_results_data(basedir=args.base_dir,
pattern=args.file_pattern)
results_data.to_csv(args.output_fpath, index=False)
# below code stores as google sheet
if args.do_not_save_to_sheets:
exit()
import gspread_pandas
config = gspread_pandas.conf.get_config(args.google_auth_key_dir)
client = gspread_pandas.Client(config=config)
sheet = gspread_pandas.Spread(
spread=args.google_sheets_name,
client=client,
create_spread=True,
)
prev_results = sheet.sheet_to_df(sheet='results')
results_data = (prev_results.append(results_data).drop_duplicates(
subset='run_name', keep='last'))
sheet.df_to_sheet(results_data, index=False, replace=True, sheet='results')
# sharing
access_sheet = client.open(args.google_sheets_name)
authenticated_users = [
p["emailAddress"] for p in access_sheet.list_permissions()
]
for user in args.share_with_users:
if user not in authenticated_users:
access_sheet.share(user, perm_type="user", role="writer")
