def zd_api(request): config = conf.get_config("./gspread_pandas/") articles = [] url = "https://collegetrack.zendesk.com//api/v2/help_center/categories/360000085163/articles.json" # also not used, but if we wanted to add in users the api URL is: /api/v2/users.json while url: response = requests.get(url, auth=(ZD_USERNAME, ZD_PASSWORD)) if response.status_code != 200: print("Status:", response.status_code, "Problem with the request. Exiting.") exit() data = json.loads(response.text) for article in data["articles"]: articles.append( [ article["id"], article["title"], article["html_url"], article["updated_at"], ] ) url = data["next_page"] # converting the date column into a usable form # to_write_updated = [datetime.date(parse(i[3])) for i in to_write] df = pd.DataFrame(articles) df.columns = ["ID", "Title", "URL", "Date"] df.Date = pd.to_datetime(df.Date).dt.date df = df.sort_values(by="Date", ascending=True) df.loc[:, "ID"] = df["ID"].astype("int").astype("str") # df = df.set_index("ID") # articles.sort(key = lambda item: item[3], reverse=False) # articles.sort(key = lambda item: item[3], reverse=True) spread = Spread(SPREADSHEET_ID, config=config) spread.open_sheet(0) existing_sheet = spread.sheet_to_df(sheet="Sheet1") existing_sheet_subset = existing_sheet[ ["Internal / Staff Facing", "Person Responsible"] ].reset_index() merged_df = df.merge(existing_sheet_subset, on="ID", how="left") spread.df_to_sheet( merged_df, index=False, sheet="Sheet1", start="A1", replace=False )
class PandasGoogleSpreadsheetWrapper(PandasWrapperBase): def __init__(self, credentialsManager, spreadsheetId, dataFrame=None): super().__init__(dataFrame) self.__credentialsManager = credentialsManager self.__id = spreadsheetId self.__spreadsheet = Spread( spread=self.__id, creds=self.__credentialsManager.credentials) @property def credentialsManager(self): return self.__credentialsManager @property def spreadsheetId(self): return self.__id @property def spreadsheet(self): return self.__spreadsheet @property def active_sheet(self): return self.__spreadsheet.sheet def load(self, filePath): # filePath = SheetName print("Load GoogleSpreadsheet: " + str(self.__id) + " [" + str(filePath) + "]") self.onLoadPreprocessing(self.df) df = self.__spreadsheet.sheet_to_df(index=False, sheet=filePath) self.setDataFrame(df) print(" Loaded Length: " + str(len(self.df.index))) self.onLoadPostprocessing(self.df) return self def save(self, filePath): # filePath = SheetName print("Save GoogleSpreadsheet: " + str(self.__id) + " [" + str(filePath) + "]") self.onSavePreprocessing(self.df) self.__spreadsheet.open_sheet(filePath, create=True) self.__spreadsheet.df_to_sheet(df=self.df, index=False, headers=True, start='A1', replace=True) print(" Saved Length : " + str(len(self.df.index))) self.onSavePostprocessing(self.df) return self
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
repo.index.commit("backup of check_DB before modification") origin = repo.remotes.origin origin.push() scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'C:/Keys/mongoDB_secret.json', scope) client = gspread.authorize(creds) head_ind = 8 sheet_g = Spread(g_sheet_name) sheet_g.open_sheet("Sheet1", create=True) sheet_g.sheet.resize(rows=head_ind) DB_col_list = [ 'device_name', 'Type', 'checkstatus', 'description', 'servertime', 'last_fail', 'client_name', 'site_name', 'extra', 'dsc247', 'deviceid', 'checkid', 'consecutiveFails', 'Label' ] check_DB = pd.DataFrame(columns=DB_col_list) ## writing to excell file #excel_path = 'check_list.xlsx' #if os.path.isfile(excel_path): # os.remove(excel_path) i = 0
#TIER PRICING CALCULATOR import pandas as pd from gspread_pandas import Spread pricing = Spread('price', 'Plexchat Pricing') pricing.open_sheet(0) price_input = pricing.sheet_to_df(header_rows=1, index=True).apply(pd.to_numeric, errors='ignore') tier_1_MAU = int(price_input.loc["Tier 1", "MAU"]) tier_2_MAU = int(price_input.loc["Tier 2", "MAU"]) tier_3_MAU = int(price_input.loc["Tier 3", "MAU"]) tier_4_MAU = int(price_input.loc["Tier 4", "MAU"]) tier1_cloud_cost = price_input.loc["Tier 1", "COST_USER"] tier2_cloud_cost = price_input.loc["Tier 2", "COST_USER"] tier3_cloud_cost = price_input.loc["Tier 3", "COST_USER"] tier4_cloud_cost = price_input.loc["Tier 4", "COST_USER"] estimated_MAU = int(price_input.loc["ESTIMATED MAU", "MAU"]) tiers_MAU = { "Tier 1": tier_1_MAU, "Tier 2": tier_2_MAU, "Tier 3": tier_3_MAU, "Tier 4": tier_4_MAU } tiers_price_per_user = { "Tier 1": price_input.loc["Tier 1", "PRICE_USER"],
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)
Path2Pro This code is used to organize data from daily tournaments """ import numpy as np import pandas as pd import gspread_pandas as gp from oauth2client.service_account import ServiceAccountCredentials from gspread_pandas import Spread from collections import OrderedDict tournament = Spread('league', 'Path2Pro League (Season 1)') tournament.sheets ### pull tournament data into dataframes ### PLAYER NAMES tournament.open_sheet(5) tournament players = tournament.sheet_to_df(header_rows=1, index=True) ### PLAYER HASHTAGS tournament.open_sheet(6) tournament hashtags = tournament.sheet_to_df(header_rows=1, index=True) ### PLAYER TROPHIES tournament.open_sheet(7) tournament trophies = tournament.sheet_to_df(header_rows=1, index=True) ### PLAYER CLANS tournament.open_sheet(8)
# ['checkid','servertime'])#, ascending = False) temp = fails_select.loc[ row_ind, ['device_name', 'client_name', 'site_name', 'Type']] check_SQL[['device_name', 'client_name', 'site_name', 'Type']] = check_SQL.apply(lambda row: temp, axis=1) check_SQL_last = check_SQL[[ 'device_name', 'Type', 'checkstatus', 'description', 'servertime', 'client_name', 'site_name', 'extra', 'dsc247', 'deviceid', 'checkid', 'consecutiveFails' ]] print('Saving', len(check_SQL_last), 'extracted trajectory to the SQL table...') sheet_T = Spread(traj_sheet_name) sheet_T.open_sheet("Sheet1", create=False) if row_ind == 0: sheet_T.df_to_sheet(check_SQL_last, index=False, sheet='Sheet1', replace=True) else: row = sheet_T.sheet.row_count + 2 sheet_T.df_to_sheet(check_SQL_last, index=False, headers=False, sheet='Sheet1', start=(row, 1)) #------- save to excel file # excel_path = 'Check_trajectory.xlsx'