API.py

# %%
# !pip install requests

# %%
# pip install pygsheets

# %%
import requests
import json
import pandas as pd
import threading

# %%
# 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

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()

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

# 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)

# %%
#-------------------------------------------------------------------------------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('"', '')
  projectNameID=dfNames.iloc[np.where(dfNames['ProjectName'].astype(str)==chosenprjname)]['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=dfNames.iloc[np.where(dfNames['ProjectName'].astype(str)==chosenprjname)]['ProjectId']  #ProjectName == chosenprjname
  projectPartID=dfParts.iloc[np.where(dfParts['ProjectPart'].astype(str)==chosenpart)]['ProjectPartId']
  dfSections=dfSections.iloc[np.where((dfSections['ProjectId']==int(projectId)) & (dfSections['ProjectPartId']==int(projectPartID)) ) ]
  sections=dfSections['ProjectSection'].tolist()
  return sections


##############################################################################
def update_df(): #get file from drive
    url_1 = prjnamesURL.replace('/edit#gid=', '/export?format=csv&gid=')
    df_Old = pd.read_csv(url_1)
    threading.Timer(20.0,update_df).start() 
    print(datetime.now())

    df_New = pd.read_csv(url_1)
    df_diff = pd.concat([df_Old,df_New]).drop_duplicates(keep=False)
    print('kkkkkkkkkkkkkkkkkkkkk',df_diff)
    # if df_diff.shape[0] >0:
    #   print('new')
    #   print(df_diff)
    #   AppendtablestoSheets()