# %% # !pip install requests # %% # pip install pygsheets # %% import requests import json import pandas as pd import threading from threading import Timer from threading import Thread import queue import time # from __future__ import print_function from googleapiclient.discovery import build from google.oauth2 import service_account import pygsheets import numpy as np from datetime import datetime # from save_thread_result import ThreadWithResult # from threading import Thread, Event prjnamesURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=0' prjpartsURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=2102042200' prjsectionsURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=1708565170' dfNames=pd.DataFrame() dfParts=pd.DataFrame() que= queue.Queue() returnString='' #FIRST TIME ONLY , DONT CALL UNLESS NEEDED def AuthorizeGoogleSheets(): SCOPES = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive' ] credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES) spreadsheet_service = build('sheets', 'v4', credentials=credentials) drive_service = build('drive', 'v3', credentials=credentials) gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json') return spreadsheet_service,drive_service,gc # %% #FIRST TIME ONLY , DONT CALL UNLESS NEEDED def createSheet(): spreadsheet_service,drive_service,gc=AuthorizeGoogleSheets() #create sheet titles=gc.spreadsheet_titles() if 'API tables Output' in titles: ws=gc.open(str('API tables Output')) spreadsheetId=ws.id else: spreadsheet_details = { 'properties': { 'title': 'API tables Output' } } sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, fields='spreadsheetId').execute() spreadsheetId = sheet.get('spreadsheetId') permission1 = { 'type': 'anyone', 'role': 'writer', # 'emailAddress': 'marthe.adr@gmail.com' } drive_service.permissions().create(fileId=spreadsheetId, body=permission1).execute() ws = gc.open_by_key(spreadsheetId) projectNamesSheet = ws.worksheet(0) projectNamesSheet.title='Project Names'#first sheet ws.add_worksheet("Project Parts") # secondsheet ws.add_worksheet("Sections") # thirdsheet print('SPREADHSEET IDDDD=====',spreadsheetId) return ws,gc,spreadsheetId # %% [markdown] # # **New** API # %% def APIValues(): query={'loginname':'Marthe','password':'marthemain'} #token="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJodHRwOi8vc2NoZW1hcy54bWxzb2FwLm9yZy93cy8yMDA1LzA1L2lkZW50aXR5L2NsYWltcy9uYW1lIjoiQURSIiwibmJmIjoiMTY4MDg1OTgzMyIsImV4cCI6IjE2ODA5MDMwMzMifQ.-FULhKD_M-cyIdSMMIYDlEo9DoZtc00yIlyXIPuyLZI" head={'Content-Type':'application/json'} response=requests.post(url="https://console.trevorsadd.co.uk/account/login",json=query,headers=head) key=response.text head={'Content-Type':'application/json;charset=UTF-8',"Authorization":'Bearer {}'.format(key)} response=requests.post(url="https://console.trevorsadd.co.uk/project/getprojectslist",json=query,headers=head) dict1=response.json() return dict1 # %% [markdown] # ### Generate Tables # %% def GenerateTables(): dict1=APIValues() ##First table should ONLY contain: All project Ids and names #Second table should contain ProjId,ProjectPartId,ProjectPart #Third table should contain ProjId,ProjectPartId,ProjectSection table1=pd.DataFrame(columns= ['ProjectId','ProjectName']) table2=pd.DataFrame(columns= ['ProjectId','ProjectPartId','ProjectPart']) table3=pd.DataFrame(columns= ['ProjectId','ProjectPartId','ProjectSection']) for item in dict1: table1 = pd.concat([table1, pd.DataFrame([[item.get('id'),item.get('projectname')]], columns=table1.columns)], ignore_index=True) projectPartsList=item.get('projectparts') for part in projectPartsList: table2= pd.concat([table2,pd.DataFrame([[item.get('id'),part.get('id'),part.get('name')]], columns=table2.columns)], ignore_index=True) sections=part.get('projectsections') for section in sections: table3= pd.concat([table3,pd.DataFrame([[item.get('id'),section.get('projectPartId'),section.get('section')]], columns=table3.columns)], ignore_index=True) # table1 = pd.concat([table1, pd.DataFrame([['2222','trial1']], columns=table1.columns)], ignore_index=True) # table2= pd.concat([table2,pd.DataFrame([['2222','66','partname']], columns=table2.columns)], ignore_index=True) # table3= pd.concat([table3,pd.DataFrame([['2222','66','newSectionsss']], columns=table3.columns)], ignore_index=True) return table1,table2,table3 # Set Tables to Sheets def AppendtablestoSheets(): gc=AuthorizeGoogleSheets()[2] ws = gc.open_by_key('1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk') table1,table2,table3= GenerateTables() projectNamesSheet=ws.worksheet_by_title('Project Names') projectPartsSheet=ws.worksheet_by_title('Project Parts') projectSectionsSheet=ws.worksheet_by_title('Sections') projectNamesSheet.clear() projectPartsSheet.clear() projectSectionsSheet.clear() #append tables to google sheets projectNamesSheet.set_dataframe(start='A1',df=table1) projectPartsSheet.set_dataframe(start='A1',df=table2) projectSectionsSheet.set_dataframe(start='A1',df=table3) # return table1,table2,table3 # %% #-------------------------------------------------------------------------------CALL THIS FUNCTION TO BEGIN---------------------------------------------------------------------- #-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- def getPrjNames(url=prjnamesURL): #url first sheet of names url_1 = url.replace('/edit#gid=', '/export?format=csv&gid=') df = pd.read_csv(url_1) return df['ProjectName'].tolist() # %% def getprjParts(chosenprjname,urlNames=prjnamesURL,urlparts=prjpartsURL): url_1 = urlNames.replace('/edit#gid=', '/export?format=csv&gid=') global dfNames dfNames = pd.read_csv(url_1) chosenprjname=chosenprjname.replace('"', '') chosenprjnameList=chosenprjname.split(' ') for i, row in dfNames.iterrows(): prjnameString=dfNames['ProjectName'].loc[i] if all(name in prjnameString for name in chosenprjnameList): projectNameID=dfNames.iloc[i]['ProjectId'] # projectNameID=dfNames.iloc[np.where(dfNames['ProjectName'].str.startswith(chosenprjnameList[1]))]['ProjectId'] #ProjectName == chosenprjname url_2 = urlparts.replace('/edit#gid=', '/export?format=csv&gid=') global dfParts dfParts = pd.read_csv(url_2) dfParts= dfParts.iloc[np.where(dfParts['ProjectId']==int(projectNameID))] parts=dfParts['ProjectPart'].tolist() print(parts) #Get projectName Id return parts # %% def getprjSections(chosenprjname, chosenpart,urlSection=prjsectionsURL): #GetprojectSections url_1 = urlSection.replace('/edit#gid=', '/export?format=csv&gid=') dfSections = pd.read_csv(url_1) chosenprjname=chosenprjname.replace('"', '') chosenpart=chosenpart.replace('"', '') projectId='' projectPartID='' chosenprjnameList=chosenprjname.split(' ') chosenpartList=chosenpart.split(' ') for i, row in dfNames.iterrows(): prjnameString=dfNames['ProjectName'].loc[i] if all(name in prjnameString for name in chosenprjnameList): projectId=dfNames.iloc[i]['ProjectId'] # projectId=dfNames.iloc[np.where(dfNames['ProjectName'].astype(str)==chosenprjname)]['ProjectId'] #ProjectName == chosenprjname for i, row in dfParts.iterrows(): prjpartString=dfParts['ProjectPart'].loc[i] if all(part in prjpartString for part in chosenpartList): projectPartID=dfParts['ProjectPartId'].loc[i] # projectPartID=dfParts.iloc[np.where(dfParts['ProjectPart'].astype(str)==chosenpart)]['ProjectPartId'] if projectId and projectPartID: dfSections=dfSections.iloc[np.where((dfSections['ProjectId']==int(projectId)) & (dfSections['ProjectPartId']==int(projectPartID)) ) ] sections=dfSections['ProjectSection'].tolist() return sections else: return # ############################################################################## def update_df(table1,table2,table3): #API old dictionary prjnamesURL= 'https://docs.google.com/spreadsheets/d/1I4P7wiCXTAwnld0YQfUJC8bPtuiETna1HGWVuHbuDBk/edit#gid=0' url_1 = prjnamesURL.replace('/edit#gid=', '/export?format=csv&gid=') table1N,table2N,table3N=GenerateTables() table1N = pd.read_csv(url_1) t1Diff=table1N[~table1N.isin(table1)].dropna() t2Diff=table2N[~table2N.isin(table2)].dropna() t3Diff=table3N[~table3N.isin(table3)].dropna() returnString='' if not t1Diff.empty: returnString='Changes have been made in Projects. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).' if not t2Diff.empty: returnString='Changes have been made in Parts. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).' if not t3Diff.empty: returnString='Changes have been made in Sections. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).' if (not t1Diff.empty) and (not t2Diff.empty): returnString='Changes have been made in Projects and Parts. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).' if (not t1Diff.empty) and (not t3Diff.empty): returnString='Changes have been made in Projects and Sections. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).' if (not t2Diff.empty) and (not t3Diff.empty): returnString='Changes have been made in Parts and Sections. Would you like to retrieve them in the console? By saying yes, you may need to repeat the process(if any).' # print(t1Diff,t2Diff,t3Diff) # result= returnString # print(returnString) # # que.put(returnString) print('kkkkk',datetime.now()) threading.Timer(5.0,update_df, args=(table1,table2,table3)).start() lock=threading.Lock() lock.acquire() if lock.locked: lock.release() yield returnString # class CustomThread(Thread): # # constructor # def __init__(self, worker_func,table1,table2,table3): # super().__init__() # self._worker_func = worker_func # self.table1_thread=table1 # self.table2_thread=table2 # self.table3_thread=table3 # self.value1=None # def run(self): # # while not self.stop_event.is_set(): # result=self._worker_func(self.table1_thread,self.table2_thread,self.table3_thread) # self.value1=result # time.sleep(20.0) # def calltoUpdate(table1,table2,table3): # while True: # thread = CustomThread(update_df,table1,table2,table3) # # start the thread # thread.start() # # wait for the thread to finish # thread.join() # # report all values returned from a thread # print('OUTPIITT',thread.value1) # yield thread.value1 def do_every (interval, worker_func, iterations = 0): if iterations != 1: threading.Timer ( interval, do_every, [interval, worker_func, 0 if iterations == 0 else iterations-1] ).start () worker_func () def print_hw (): print ("hello world") def print_so (): hi="stackoverflow" print('ghh') return hi # call print_so every second, 5 times total