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