Spaces:
Sleeping
Sleeping
| # %% | |
| # !pip install requests | |
| # %% | |
| # pip install pygsheets | |
| # %% | |
| import requests | |
| import json | |
| import pandas as pd | |
| # %% [markdown] | |
| # ## GoogleSheet | |
| # %% | |
| # from __future__ import print_function | |
| from googleapiclient.discovery import build | |
| from google.oauth2 import service_account | |
| import pygsheets | |
| 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 | |
| # %% | |
| 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':'adr','password':'Tameen'} | |
| #token="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJodHRwOi8vc2NoZW1hcy54bWxzb2FwLm9yZy93cy8yMDA1LzA1L2lkZW50aXR5L2NsYWltcy9uYW1lIjoiQURSIiwibmJmIjoiMTY4MDg1OTgzMyIsImV4cCI6IjE2ODA5MDMwMzMifQ.-FULhKD_M-cyIdSMMIYDlEo9DoZtc00yIlyXIPuyLZI" | |
| head={'Content-Type':'application/json'} | |
| response=requests.post(url="https://consoletest.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://consoletest.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) | |
| return table1,table2,table3 | |
| # %% [markdown] | |
| # Set Tables to Sheets | |
| # %% | |
| def AppendtablestoSheets(ws): | |
| 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) | |
| # %% | |
| #-------------------------------------------------------------------------------CALL THIS FUNCTION TO BEGIN---------------------------------------------------------------------- | |
| #-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| def getPrjNames(): | |
| ws,gc,spreadsheetId=createSheet() | |
| AppendtablestoSheets(ws) | |
| projectNamesSheet=ws.worksheet_by_title('Project Names') | |
| prjnamesfromSheet=projectNamesSheet.get_col(2, returnas='matrix', include_tailing_empty=False) | |
| # print(prjnamesfromSheet[2]) | |
| return prjnamesfromSheet[1:] | |
| # %% | |
| def getprjParts(chosenprjname): | |
| #Get projectName Id | |
| ws,gc,spreadsheetId=createSheet() | |
| projectNamesSheet=ws.worksheet_by_title('Project Names') | |
| prjnamesfromSheet= getPrjNames() | |
| index=prjnamesfromSheet.index(chosenprjname.replace('"', '')) | |
| # index,indexE = [o for o, x in prjnamesfromSheet if x == str(chosenprjname)] #==clicked value | |
| prjnameId=projectNamesSheet.cell((index+2 ,1)).value #retrieved Id | |
| print('iddd',prjnameId) | |
| #Get projectParts | |
| projparts=[] | |
| projectPartsSheet=ws.worksheet_by_title('Project Parts') | |
| prjpartsfromSheet=projectPartsSheet.get_col(1, returnas='matrix', include_tailing_empty=False) | |
| indices = [o for o, x in enumerate(prjpartsfromSheet[1:]) if x == prjnameId] #==clicked value | |
| print('ind',indices) | |
| for index in indices: | |
| projparts.append([projectPartsSheet.cell((index+2 ,3)).value, projectPartsSheet.cell((index+2 ,2)).value]) #retrieved Id | |
| return projparts | |
| # %% | |
| def getprjSections(chosenpart): | |
| #GetprojectSections | |
| ws,gc,spreadsheetId=createSheet() | |
| projsections=[] | |
| projectSectionsSheet=ws.worksheet_by_title('Sections') | |
| prjsectionsfromSheet=projectSectionsSheet.get_col(2, returnas='matrix', include_tailing_empty=False) | |
| chosenpart=chosenpart.replace('"', '') | |
| indices2 = [o for o, x in enumerate(prjsectionsfromSheet) if x == chosenpart] #part clicked on | |
| print('indices2',indices2) | |
| for index in indices2: | |
| projsections.append(projectSectionsSheet.cell((index+2 ,3)).value) #retrieved Id | |
| return projsections | |
| # %% | |
| # tableTrial=pd.DataFrame(data=[[0,'0x','part1'],[0,'1x','part2']],columns= ['ProjectId','ProjectPartId','ProjectPart']) | |
| # tableTrial3=pd.DataFrame(data=[[0,'0x','0.0'],[0,'0x','1.0'],[1,'1x','3.1']],columns= ['ProjectId','ProjectPartId','ProjectSection']) | |
| # %% | |
| #Ex: proj 1 ekhtrnah fl dropdown-->go search in table where column of names has the name proj 1 , get id of this proj1 (same row and return it to this line) | |
| #get parts | |
| # partsList=list(tableTrial.loc[tableTrial['ProjectId'].isin([id])].get('ProjectPart')) | |
| #get sections | |
| # sectionsList=list(tableTrial3.loc[tableTrial3['ProjectId'].isin([projid]) & tableTrial3['projpartid'].isin(['0x'])].get('ProjectSection')) | |
| # tableTrial3 | |
| # %% | |