loop and concatintion

[somthingmendi123]

In honor of the dear Click Sense developers
I have tables of goals for 2018 and 2019 in the model, which I chained, but I want to create a situation where every new goal file that arrives in a folder on the computer will be automatically chained to the model. That is, I have to create a loop and connection of all the data in an orderly manner.

I want to generate the script so that it is generic. that if a file from the year 2020 / 2021 is added for example to the folder - he will upload it to me as well.
I need to use a loop that will extract the year from the name of the file into a variable and use the variable to extract the appropriate tab from Excel.

How do I write the script?

[biwek86619]

import os
import openpyxl

def extract_year(filename):
  year = filename[-4:]
  return int(year)

def get_tab(excel_file, year):
  sheet_name = f"{year} Goals"
  return excel_file[sheet_name]

def main():
  # Get the folder where the goal files are stored
  folder_path = "C:\\Users\\your_username\\Desktop\\goals"

  # Get a list of all the files in the folder
  files = os.listdir(folder_path)

  # Create a loop to iterate through the files
  for file in files:
    # Extract the year from the file name
    year = extract_year(file)

    # Open the Excel file
    excel_file = openpyxl.load_workbook(f"{folder_path}/{file}")

    # Get the appropriate tab from the Excel file
    tab = get_tab(excel_file, year)

    # Chain the tab to the model
    tab.chain()

if __name__ == "__main__":
  main()