google_sheet_to_xml.py

# -*- coding: utf-8 -*-
"""Google Sheet to XML.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1T-b1N8Gq6wwbBurODzJIdhRQNeRbyBnz
"""

from googleapiclient.discovery import build
from google.oauth2 import service_account
import pygsheets
import xml.etree.ElementTree as ET
import tsadropboxretrieval

def authorizeLegend():
  SCOPES = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/drive',
  'https://www.googleapis.com/auth/drive.metadata'
  ]
  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 getTimes(drive_service,spreadsheetId):
  summaryTimes=drive_service.files().get(fileId=spreadsheetId,fields="createdTime, modifiedTime").execute()
  createdTime=summaryTimes.get('createdTime')
  modifiedTime=summaryTimes.get('modifiedTime')
  return createdTime,modifiedTime



def openSpreadsheet_DFs(legendTitle):
  spreadsheet_service,drive_service,gc=authorizeLegend()
  # legendTitle= '2123-HRW-01-BG-DR-S-110_P.03.pdf'
  print('found sheet ', legendTitle)
  ws=gc.open(str(legendTitle))
  spreadsheetId=ws.id

  worksheet0 = ws.worksheet(0)
  worksheet1 = ws.worksheet_by_title('XML Export Summary')

  df0=worksheet0.get_as_df()
  df1=worksheet1.get_as_df()
  createdTime,modifiedTime=getTimes(drive_service,spreadsheetId)
  return df0,df1,createdTime,modifiedTime




def create_xml(documentname , dbPath):
    df0,df1,createdTime,modifiedTime=openSpreadsheet_DFs(documentname)
    # createdTime,modifiedTime=getTimes()
    MarkupSummary=ET.Element('<MarkupSummary Version="1.0" Document="'+documentname+'">')
    # we make root element
    MarkupSummary = ET.SubElement(MarkupSummary, "MarkupSummary")
    MarkupSummary.attrib['Version'] = "1.0"
    MarkupSummary.attrib['Document'] = documentname
    # insert list element into sub elements
    for rowX,rowY in df1.iterrows():
      print(rowX,rowY)
      Markup = ET.Element("Markup")
      Markup = ET.SubElement(MarkupSummary, "Markup")

      Subject = ET.SubElement(Markup, "Subject")
      Subject.text=str(rowY[0])

      Date = ET.SubElement(Markup, "Date")
      Date.text=str(modifiedTime.split('T')[0].replace('-','/')+ ' '+ modifiedTime.split('T')[1].split('.')[0])

      Colour = ET.SubElement(Markup, "Colour")
      Colour.text=str(' ')

      Count = ET.SubElement(Markup, "Count")
      Count.text=str(' ')

      Author = ET.SubElement(Markup, "Author")
      Author.text=str('ADR Team')
      if rowY[2]=='m':
        Length = ET.SubElement(Markup, "Length")
        Length.text=str(rowY[1])
      else:
        Length = ET.SubElement(Markup, "Length")
        Length.text=str(0)

      Height = ET.SubElement(Markup, "Height")
      Height.text=str(0)
      if rowY[2]=='m2':
        Area = ET.SubElement(Markup, "Area")
        print('rowY',rowY)
        Area.text=str(rowY[1])
      else:
        Area = ET.SubElement(Markup, "Area")
        Area.text=str(0)
      Label = ET.SubElement(Markup, "Label")
      Label.text=str(rowY[0])

      Page_Label = ET.SubElement(Markup, "Page_Label")
      Page_Label.text=str(' ')

      Page_Index = ET.SubElement(Markup, "Page_Index")
      Page_Index.text=str(' ')

      Lock = ET.SubElement(Markup, "Lock")
      Lock.text=str('Unlocked')

      Tick_mark = ET.SubElement(Markup, "Tick_mark")
      Tick_mark.text=str(' ')

      Creation_Date = ET.SubElement(Markup, "Creation_Date")
      Creation_Date.text=str(createdTime.split('T')[0].replace('-','/'))

      x = ET.SubElement(Markup, "X")
      x.text=str(' ')

      y= ET.SubElement(Markup, "Y")
      y.text=str(' ')

      Document_Width= ET.SubElement(Markup, "Document_Width")
      Document_Width.text=str(' ')

      Document_Height= ET.SubElement(Markup, "Document_Height")
      Document_Height.text=str(' ')

      RiseDrop= ET.SubElement(Markup, "RiseDrop")
      RiseDrop.text=str(0)

      Unit= ET.SubElement(Markup, "Unit")
      Unit.text=str(rowY[2])

      Wall_Area= ET.SubElement(Markup, "Wall_Area")
      Wall_Area.text=str(' ')

      Depth= ET.SubElement(Markup, "Depth")
      Depth.text=str(0.00)

      Volume= ET.SubElement(Markup, "Volume")
      Volume.text=str(0)

      Measurement= ET.SubElement(Markup, "Measurement")
      Measurement.text=str(rowY[1])

      Layer= ET.SubElement(Markup, "Layer")
      Layer.text=str(' ')

      Capture= ET.SubElement(Markup, "Capture")
      Capture.text=str('No')

      File_Name= ET.SubElement(Markup, "File_Name")
      File_Name.text=str(documentname)

      tree = ET.ElementTree(Markup)
    tree = ET.ElementTree(MarkupSummary)
    # write the tree into an XML file
    # tree.write(str(documentname).split('.pdf')[0]+".xml", encoding ='UTF-8', xml_declaration = True ,method='xml')
    # dbPath='/TSA JOBS/ADR Test'+pdfpath+'XML/'
    # (pdfname,path)
    # path=path+pdfname
    xmllink= tsadropboxretrieval.uploadanyFile(doc=tree,pdfname=documentname,path=dbPath,flag='xml')
    return xmllink
# create_xml(legendTitle)