google_sheet_Legend.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 ast
import re
import pandas as pd
from google.oauth2 import service_account
from googleapiclient.discovery import build
import pygsheets
import re


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

spreadsheet_service,drive_service,gc=authorizeLegend()


def create_new_sheet(path):
    spreadsheet_details = {
        'properties': {
            'title': path
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, fields='spreadsheetId').execute()
    spreadsheetId = sheet.get('spreadsheetId')
    permission1 = {
        'type': 'anyone',
        'role': 'writer',
    }
    drive_service.permissions().create(fileId=spreadsheetId, body=permission1, supportsAllDrives=True).execute()
    return spreadsheetId


def update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath):
    ws = gc.open_by_key(spreadsheetId)
    worksheet = ws.worksheet(0)
    worksheet.title = 'Legend and data created'
    worksheet.clear()
    ws.create_developer_metadata('path', pdfpath)
    splittedpdfpath = re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,/<>?]', pdfpath)
    if splittedpdfpath[-2].startswith('2.2') or splittedpdfpath[-2].startswith('2.1'):
        worksheet.set_dataframe(start='A1', df=SimilarAreaDictionary)
    if splittedpdfpath[-2].startswith('2.8'):
        DoorsLegend(SimilarAreaDictionary,spreadsheetId,worksheet)
        return
    
    else:
        top_header_format = [
            {'mergeCells': {  # areas
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 1,
                    'endRowIndex': 2,
                    'startColumnIndex': 3,
                    'endColumnIndex': 5
                }
            }},
            {'mergeCells': {  # perimeters
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 1,
                    'endRowIndex': 2,
                    'startColumnIndex': 5,
                    'endColumnIndex': 7
                }
            }},
            {'mergeCells': {  # lengths
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 1,
                    'endRowIndex': 2,
                    'startColumnIndex': 7,
                    'endColumnIndex': 9
                }
            }},
            {'mergeCells': {  # legend and data created
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 11
                }
            }}
        ]
        spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': top_header_format}).execute()
        worksheet.cell((1, 1)).value = 'Legend and Data Created'
        worksheet.cell((2, 1)).value = 'Guess'
        worksheet.cell((2, 2)).value = 'Color'
        worksheet.cell((2, 3)).value = 'Count'
        worksheet.cell((2, 4)).value = 'Areas'
        worksheet.cell((2, 6)).value = 'Perimeters'
        worksheet.cell((2, 8)).value = 'Lengths'
        worksheet.cell((2, 10)).value = 'Texts'
        worksheet.cell((2, 11)).value = 'Comments'
        second_row_data = ['Nr', 'm2', 'Total', 'm', 'Total', 'm', 'Total']
        worksheet.update_row(3, second_row_data, col_offset=2)
        worksheet.update_col(1, list(SimilarAreaDictionary['Guess']), row_offset=3)
        worksheet.update_col(3, list(SimilarAreaDictionary['Occurences']), row_offset=3)
        worksheet.update_col(4, list(SimilarAreaDictionary['Area']), row_offset=3)
        worksheet.update_col(5, list(SimilarAreaDictionary['Total Area']), row_offset=3)
        worksheet.update_col(6, list(SimilarAreaDictionary['Perimeter']), row_offset=3)
        worksheet.update_col(7, list(SimilarAreaDictionary['Total Perimeter']), row_offset=3)
        worksheet.update_col(8, list(SimilarAreaDictionary['Length']), row_offset=3)
        worksheet.update_col(9, list(SimilarAreaDictionary['Total Length']), row_offset=3)
        worksheet.update_col(10, list(SimilarAreaDictionary['Texts']), row_offset=3)
        worksheet.update_col(11, list(SimilarAreaDictionary['Comments']), row_offset=3)
        
        if splittedpdfpath[-2].startswith('1.0'):
            colorsUsed = [
                [SimilarAreaDictionary['R'].iloc[i], SimilarAreaDictionary['G'].iloc[i], SimilarAreaDictionary['B'].iloc[i]]
                for i in range(len(SimilarAreaDictionary))
            ]
        elif splittedpdfpath[-2].startswith('3.2'):
            colorsUsed = list(SimilarAreaDictionary['Color'])
        rowsLen = len(SimilarAreaDictionary.values.tolist())
        lastcell = worksheet.cell((rowsLen + 2, 1))
        lastcellNotation = str(lastcell.address.label)
        columnsLen = len(SimilarAreaDictionary.columns.values.tolist())
        lastUsedCol = columnsLen + 1
        worksheet.adjust_column_width(start=2, end=3)
        worksheet.adjust_column_width(start=10, end=10)
        worksheet.adjust_column_width(start=4, end=9, pixel_size=60)
        startrow = 3
        endColindex = 11
        endrow = 3
        sheetId = '0'
        batch_requests = []
        for i in range(len(colorsUsed)):
            r, g, b = colorsUsed[i]
            batch_requests.append({
                "updateCells": {
                    "range": {
                        "sheetId": sheetId,
                        "startRowIndex": i + startrow,
                        "startColumnIndex": 1,
                    },
                    "rows": [
                        {
                            "values": [
                                {
                                    "userEnteredFormat": {
                                        "backgroundColor": {
                                            "red": r / 255,
                                            "green": g / 255,
                                            "blue": b / 255,
                                            "alpha": 0.4,
                                        }
                                    }
                                }
                            ]
                        }
                    ],
                    "fields": "userEnteredFormat.backgroundColor",
                }
            })
        batch_requests.append({
            "updateBorders": {
                "range": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": len(SimilarAreaDictionary) + endrow,
                    "startColumnIndex": 0,
                    "endColumnIndex": endColindex
                },
                "top": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "bottom": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "left": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "right": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "innerHorizontal": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                },
                "innerVertical": {
                    "style": "SOLID",
                    "width": 2,
                    "color": {
                        "red": 0.0,
                        "green": 0.0,
                        "blue": 0.0
                    }
                }
            }
        })
        spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': batch_requests}).execute()
   
    model_cell = worksheet.cell('A1')
    model_cell.set_text_format('bold', True)
    model_cell.set_horizontal_alignment(pygsheets.custom_types.HorizontalAlignment.CENTER)
    model_cell.color = (213 / 255, 219 / 255, 255 / 255)
    pygsheets.DataRange('A2', 'K2', worksheet=worksheet).apply_format(model_cell)


def legendGoogleSheets(SimilarAreaDictionary, path, pdfpath, spreadsheetId=0):
    titles = gc.spreadsheet_titles()
    if path in titles:
        ws = gc.open(path)
        spreadsheetId = ws.id
    else:
        spreadsheetId = create_new_sheet(path)
        ws=gc.open_by_key(spreadsheetId)
    update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath)
    spreadsheet_url = f"https://docs.google.com/spreadsheets/d/{spreadsheetId}"
    drive_service.permissions().update(transferOwnership=True, fileId=spreadsheetId, permissionId='11OfoB4Z6wOVII8mYmbnCbbqTQs7rYA65')
    
    namepathArr = [path, spreadsheetId, ws.get_developer_metadata('path')[0].value]
    return gc, spreadsheet_service, spreadsheetId, spreadsheet_url, namepathArr

#######################

def mapnametoLegend(McTName): 

  sectionKey = McTName.pop()
  key=sectionKey[0]
  section=sectionKey[1]

  # spreadsheet_service,drive_service,gc=authorizeLegend()
  spreadsheet_key =str(key)  # Please set the Spreadsheet ID.
  
  ws = gc.open_by_key(spreadsheet_key)
  # guessednamesfinal=getguessnames(gc,ws)
  sheetnames=[]
  unit=''
  # ws.add_worksheet("Summary")  # Please set the new sheet name.
  for i in ws._sheet_list:
    print(i)
    sheetnames.append(i.title)
    print(i.index)
  if 'XML Export Summary' in sheetnames:
    worksheetS = ws.worksheet_by_title('XML Export Summary')
  else:
    ws.add_worksheet("XML Export Summary")  # Please set the new sheet name.
  worksheetw = ws.worksheet(0) #legend
  worksheetS = ws.worksheet_by_title('XML Export Summary')
  summaryId= ws[1].id
  print('summaryyyID',summaryId)
  print('summaryyyID2',worksheetS.id)
  worksheetS.clear()

  countnames=0
  row0=['MC_T Name','Qty','Unit']
  worksheetS.update_row(1,row0)
  
  
  for i in range(len(McTName)):
    allgbnames=''
    item=''
    print(McTName[i][0])
    
    # firstpart= re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,./<>?]', McTName[i][0])
    print('kkk' ,McTName[i][2])
    if McTName[i][2].startswith('Area'):
      if section.startswith('1.0') or section.startswith('3.2'):
        rowvalue=5# column 5 
      ar=0
      unit='m2'
    if McTName[i][2].startswith('Perimeter'):
      if section.startswith('1.0') or section.startswith('3.2'):
        rowvalue=7# column 7
      ar=0   
      unit='m'
    if McTName[i][2].startswith('Length'):
      if section.startswith('1.0') or section.startswith('3.2'):
        rowvalue=9# column 7
      ar=0   
      unit='m'
    if McTName[i][2].startswith('Count'):
      if section.startswith('1.0') or section.startswith('3.2'):
        rowvalue=3# column 7

      ar=0   
      unit='Nr'
 
    print('mcct',McTName[i][1])
    if isinstance(McTName[i][1], list):
      guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False)

      for m in McTName[i][1]:
        if m:
          if m.startswith('text1'):
            name=m.removeprefix('text1')
            allgbnames+= name +' +'
            indices = [o for o, x in enumerate(guessednames) if x == name]
            print(indices)
            for j in range(len(indices)):
              # print('kjjjj',roww[j])
              ar+=float(worksheetw.cell((indices[j]+1 ,rowvalue)).value) 
          else:
              item+=m + ' ,'
              print(item)
          n= McTName[i][0] + ' ( '+ allgbnames[:-2] +' , ' + item[:-1]  + ' )  ' 
    else:
      if McTName[i][1].startswith('text1'):
        name=McTName[i][1].removeprefix('text1')
        allgbnames+= name

        roww=worksheetw.find(name)
        print(roww)
        for j in range(len(roww)):
          print('kjjjj',roww[j])
          ar+=float(worksheetw.cell((roww[j].row ,rowvalue)).value) 
        n= McTName[i][0] + ' ( '+ allgbnames + ' )  ' 

    rowi=[str(n),ar,unit]
    worksheetS.update_row(i+2,rowi)
    # worksheetS.adjust_column_width(start=1,end=4)
    worksheetS.adjust_column_width(start=1,end=1, pixel_size=350)
    worksheetS.adjust_column_width(start=2,end=2, pixel_size=100)
    worksheetS.adjust_column_width(start=3,end=3)
    
  xx=(worksheetS.cell( ( len(McTName) +1 ,3)) ).address.label
  model_cell1 =worksheetS.cell('A2')
  model_cell1.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.LEFT )
  pygsheets.DataRange('A2', str(xx), worksheet=worksheetS).apply_format(model_cell1)


  model_cell =worksheetS.cell('A1')
  model_cell.set_text_format('bold', True)
  model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER )
  pygsheets.DataRange('A1','C1', worksheet=worksheetS).apply_format(model_cell)
  
  body2={
  "requests": [
    {
      "updateBorders": {
        "range": {
          "sheetId": str(summaryId),
          "startRowIndex": 0,
          "endRowIndex":  len(McTName) +1 ,
          "startColumnIndex": 0,
          "endColumnIndex": 3
        },
        "top": {
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "bottom": {
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "left":{
          "style": "SOLID",
          "width":2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "right":{
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "innerHorizontal":{
          "style": "SOLID",
          "width":2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
        "innerVertical": {
          "style": "SOLID",
          "width": 2,
          "color": {
            "red": 0.0,
            "green":0.0,
            "blue":0.0
          },
        },
      }
    }
  ]
  }
  spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_key, body=body2).execute()
  return summaryId #,guessednamesfinal   

  # print(x,xarea)

def getguessnames(gc,ws):
  guessednamesfinal=[]
  worksheetw = ws.worksheet(0)
  guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False)
  print(guessednames[2:])
  for item in guessednames[2:]:
     if item not in guessednamesfinal:
        guessednamesfinal.append(item)
  print(guessednamesfinal)
  return guessednamesfinal

################################################################

def deletefromlegend(deletedrows,SimilarAreaDictionarycopy,section, areaPermArr=[]):
  items=[]
  print('dletefromlegend')
  idx=0
  if section.startswith('1.0'):
    areaPermArr=ast.literal_eval(areaPermArr)
  myDict=eval(SimilarAreaDictionarycopy)
  SimilarAreaDictionarycopy=pd.DataFrame(myDict)
  # deletedrows=eval(deletedrows)
  strings=deletedrows['content']
  areastodelete = []
  perimstodelete=[]
  lengthstodelete=[]

  for item in strings:
    newitem=str(item).split('\n \n')
    input_str = " ".join(str(newitem).split())
    # Search for the Area value
    matchA = re.search(r"Area=(\d+\.\d+)", input_str)
    matchL = re.search(r"Length=(\d+\.\d+)", input_str)
    matchP = re.search(r"Perimeter=(\d+\.\d+)", input_str)

    if matchA:
      areastodelete.append(float(matchA.group(1)))
    if matchP:
      perimstodelete.append(float(matchP.group(1)))
    if matchL:
      lengthstodelete.append(float(matchL.group(1))) 
  print('Areas to delete:', areastodelete)
  print('Perimeters to delete:', perimstodelete)
  print('Lengths to delete:', lengthstodelete)

  for i in range(len(areastodelete)):#item in areastodelete:
    if section.startswith('1.0'):
      tol=0.3
    elif section.startswith('3.2'):
      tol=1
    areamin=round(areastodelete[i],1)- tol
    areamax=round(areastodelete[i],1)+ tol
    if section.startswith('1.0'):
      for p in range(len(areaPermArr)):  
        if areastodelete[i] in areaPermArr[p]:
          width= areaPermArr[p][1]
          height= areaPermArr[p][2]
          break
      widthMin= width -10
      widthMax= width +10
      heightMin = height-10
      heightMax=height+10
      if len(areastodelete)>0:
        found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax)  ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]]
    elif section.startswith('3.2'):
      found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax)   )]]
    if len(found.index.values) >0:
      occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences']
      if occ== 1: #drop row
        SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0])
      else: #occ minus 1 , total area - areavalue , total perim - perimvalue
        print('occ>1')
        if section.startswith('1.0'):
          if len(areastodelete)>0:
            idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax)  ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]
        elif section.startswith('3.2'):
          idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )]
        if len(areastodelete)>0:
          comment = SimilarAreaDictionarycopy.loc[idx, 'Comments']
          if pd.notna(comment.iloc[0]) and 'Area' in str(comment.iloc[0]):
            matches = re.findall(r'\b\d+\b', str(SimilarAreaDictionarycopy.loc[idx, 'Comments']))
            area_occurrences = int(matches[1]) -1
            perimeter_occurrences = int(matches[2])
            print(area_occurrences, perimeter_occurrences)
            SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
            if area_occurrences > perimeter_occurrences:
              SimilarAreaDictionarycopy.loc[idx,'Occurences'] = area_occurrences
            elif perimeter_occurrences> area_occurrences:
              SimilarAreaDictionarycopy.loc[idx,'Occurences'] = perimeter_occurrences 
            elif int(area_occurrences)==int(perimeter_occurrences):
                SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1                     
                if section.startswith('1.0'):
                  SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i]
         
          else:
            print('not yet')
            area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1
            perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0]
            print(area_occurrences,perimeter_occurrences)
            SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
          SimilarAreaDictionarycopy.loc[idx,'Total Area'] = SimilarAreaDictionarycopy.loc[idx,'Total Area'] - areastodelete[i]
 

  for i in range(len(perimstodelete)):#item in areastodelete:
    if section.startswith('1.0'):
      tol=0.3
    elif section.startswith('3.2'):
      tol=10
    
    if len(perimstodelete)>0:
        print(perimstodelete[i])
        perimmin=round(perimstodelete[i],1)- 0.3
        perimmax=round(perimstodelete[i],1)+ 0.3
        print('perimmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm')
    if section.startswith('1.0'):
      for p in range(len(areaPermArr)):  
        if areastodelete[i] in areaPermArr[p]:
          print('AAA',areaPermArr[p])
          area= areaPermArr[p][0]
          width= areaPermArr[p][1]
          height= areaPermArr[p][2]
          break
      widthMin= width -10
      widthMax= width +10
      heightMin = height-10
      heightMax=height+10
      found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[( (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax)   ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]]
    elif section.startswith('3.2'):
      found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[( (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax)    )]]
    
    if len(found.index.values) >0:
      occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences']
      if occ== 1: #drop row
        print('occ=1')
        print(found)
        SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0])

      else: #occ minus 1 , total area - areavalue , total perim - perimvalue
        print('occ>1')
        if section.startswith('1.0'):
          if len(perimstodelete)>0:
            idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax)   ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]
        elif section.startswith('3.2'):
          if len(perimstodelete)>0:
            perimmin=round(perimstodelete[i],1)- 1
            perimmax=round(perimstodelete[i],1)+ 1
            idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax)    )]
        
          
        if len(perimstodelete)>0:
          comment = SimilarAreaDictionarycopy.loc[idx, 'Comments']
          if pd.notna(comment.iloc[0]) and 'Area' in str(comment.iloc[0]):
            matches = re.findall(r'\b\d+\b', str(SimilarAreaDictionarycopy.loc[idx, 'Comments']))
            area_occurrences = int(matches[1]) 
            perimeter_occurrences = int(matches[2])-1
            print(area_occurrences, perimeter_occurrences)
            SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
            if area_occurrences > perimeter_occurrences:
              SimilarAreaDictionarycopy.loc[idx,'Occurences'] = area_occurrences
            elif perimeter_occurrences> area_occurrences:
              SimilarAreaDictionarycopy.loc[idx,'Occurences'] = perimeter_occurrences 
            elif int(area_occurrences)==int(perimeter_occurrences):
                SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1                     
                if section.startswith('1.0'):
                  SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i]
         
          else:
            print('not yet')
            area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] 
            perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1
           
            SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}'
            area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1
            perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0]
          
            
          SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] = SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] - perimstodelete[i]
  return SimilarAreaDictionarycopy


def DoorsLegend(Dictionary,spreadsheetId,worksheet):
    top_header_format = [
            {'mergeCells': {  # legend and data created
                'mergeType': 'MERGE_ROWS',
                'range': {
                    'sheetId': '0',
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 2
                }
            }}
    ]
    spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': top_header_format}).execute()
    worksheet.cell((1, 1)).value = 'Legend and Data Created'
    worksheet.set_dataframe(start='A2', df=Dictionary)

    body2={ 
      "requests": [
        {
          "updateBorders": {
            "range": {
              "sheetId": str(0),
              "startRowIndex": 0,
              "endRowIndex":  4,
              "startColumnIndex": 0,
              "endColumnIndex": 2
            },
            "top": {
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "bottom": {
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "left":{
              "style": "SOLID",
              "width":2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "right":{
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "innerHorizontal":{
              "style": "SOLID",
              "width":2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
            "innerVertical": {
              "style": "SOLID",
              "width": 2,
              "color": {
                "red": 0.0,
                "green":0.0,
                "blue":0.0
              },
            },
          }
        }
      ]
    }
    spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body2).execute()
    model_cell = worksheet.cell('A1')
    model_cell.set_text_format('bold', True)
    model_cell.set_horizontal_alignment(pygsheets.custom_types.HorizontalAlignment.CENTER)
    model_cell.color = (213 / 255, 219 / 255, 255 / 255)
    pygsheets.DataRange('A2', 'B2', worksheet=worksheet).apply_format(model_cell)

######################