Spaces:
Sleeping
Sleeping
| # %% | |
| # !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/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=0' | |
| prjpartsURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=1481952020' | |
| prjsectionsURL= 'https://docs.google.com/spreadsheets/d/1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc/export?format=csv&gid=175272726' | |
| 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') | |
| print('ayhagaaa') | |
| 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,verify=False) | |
| 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,verify=False) | |
| dict1=response.json() | |
| # print(dict1) | |
| 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] | |
| # createSheet() | |
| ws = gc.open_by_key('1n8AG8_lCyhh1X97Dg3ZwWUXRUm-G2JdIUgQBTzE4qgc') | |
| 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() | |
| #append tables to google sheets | |
| table1 = table1.drop_duplicates() | |
| table2 = table2.drop_duplicates() | |
| table3 = table3.drop_duplicates() | |
| projectNamesSheet.set_dataframe(start='A1',df=table1) | |
| projectPartsSheet.set_dataframe(start='A1',df=table2) | |
| projectSectionsSheet.clear() | |
| 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 | |
| # Read the CSV data from the Google Sheet URL | |
| df = pd.read_csv(prjnamesURL) | |
| return df['ProjectName'].tolist(), df['ProjectId'].tolist() | |
| # %% | |
| def getprjParts(chosenprjid,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(prjpartsURL) | |
| print(chosenprjid,dfParts) | |
| dfParts= dfParts.iloc[np.where(dfParts['ProjectId']==int(chosenprjid))] | |
| parts=dfParts['ProjectPart'].tolist() | |
| partsIds=dfParts['ProjectPartId'].tolist() | |
| print(parts) | |
| print(partsIds) | |
| #Get projectName Id | |
| return parts,partsIds | |
| # %% | |
| def getprjSections(chosenprjid, chosenpartid,urlSection=prjsectionsURL): | |
| #GetprojectSections | |
| # url_1 = urlSection.replace('/edit#gid=', '/export?format=csv&gid=') | |
| dfSections = pd.read_csv(prjsectionsURL) | |
| # 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 chosenprjid and chosenpartid: | |
| dfSections=dfSections.iloc[np.where((dfSections['ProjectId']==int(chosenprjid)) & (dfSections['ProjectPartId']==int(chosenpartid)) ) ] | |
| sections=dfSections['ProjectSection'].tolist() | |
| return sections | |
| else: | |
| return | |
| # ############################################################################## | |