I have created a power automate desktop flow. It extracts data from SAP and then uses Python to create a report from that data. The issue I am having is with the following portion of the code:
CODE:
def add_rows_and_revisions(num_of_revs, revision_names😞
base_file = "Tims Report Base (Never Ever Change).xlsx"
destination_file = "Tims Report.xlsx"
# Copy the base file to the destination file
shutil.copyfile(base_file, destination_file)
# Load the workbook and select the active sheet
workbook = openpyxl.load_workbook(destination_file)
sheet = workbook.active
revision_names = revision_names.split(',')
# Ensure the number of revisions entered does not exceed num_of_revs
if len(revision_names) != num_of_revs:
print(f"You must enter exactly {num_of_revs} revisions.")
return
# Find all cells containing "First Revision" and store their row numbers
revision_rows = []
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
for cell in row:
if cell.value == "First Revision":
revision_rows.append(cell.row)
# Add rows with the same formatting as the cells above and insert revisions
for row_num in sorted(revision_rows, reverse=True):
# Determine the number of rows to add based on the table above
num_rows_above = 1 # Initialize with 1 to include the "First Revision" row itself
current_row = row_num
while current_row > 1 and sheet.cell(row=current_row - 1, column=1).value is not None:
num_rows_above += 1
current_row -= 1
# Replace the "First Revision" cell with the first revision name
first_revision_cell = sheet.cell(row=row_num, column=1)
first_revision_cell.value = revision_names[0]
# Copy the formatting from the "First Revision" cell to a variable
first_revision_formatting = {
"font": copy(first_revision_cell.font),
"border": copy(first_revision_cell.border),
"fill": copy(first_revision_cell.fill),
"number_format": copy(first_revision_cell.number_format),
"protection": copy(first_revision_cell.protection),
"alignment": copy(first_revision_cell.alignment)
}
# Insert additional rows for the remaining revisions
sheet.insert_rows(row_num + 1, num_of_revs - 1)
for i in range(1, num_of_revs):
for col in range(1, sheet.max_column + 1):
new_cell = sheet.cell(row=row_num + i, column=col)
# Apply the formatting from the "First Revision" cell
new_cell.font = first_revision_formatting["font"]
new_cell.border = first_revision_formatting["border"]
new_cell.fill = first_revision_formatting["fill"]
new_cell.number_format = first_revision_formatting["number_format"]
new_cell.protection = first_revision_formatting["protection"]
new_cell.alignment = first_revision_formatting["alignment"]
# Insert the revision names into the new rows under column A
sheet.cell(row=row_num + i, column=1).value = revision_names[i]
# Define a border style with no borders
no_border = Border()
# Iterate through column F and remove borders until 'GC Pack NR' is reached
for row in range(1, sheet.max_row + 1):
cell = sheet.cell(row=row, column=6)
if cell.value == "GC Pack NR":
break
cell.border = no_border
# Save the modified workbook
workbook.save('Tims Report Base - Modified.xlsx')
# Print a success message
print(f"Rows and revisions have been added under each 'First Revision' cell based on the user input.")
print("Duplicated and updated successfully")
return "Tims Report Base - Modified.xlsx"
# Call the function to execute the code
num_of_revs = int(sys.argv[1])
revision_names = sys.argv[2]
add_rows_and_revisions(num_of_revs, revision_names)
My flow looks like this:
And Finally inside "Run PowerShell Script" I have the following:
I was getting script errors before however I fixed them and now when I run the PowerShell script I am given no error but nothing happens, there should be an excel file created in the folder where my code is however that does not happen. Furthermore the Powershell Output variable is also blank and same goes for ScriptError variable. I have been stumped past few days if anyone has any answers please let me know thanks.
@VishnuReddy1997 @Deenuji @eetuRobo 
