-1

I need to access data in sharepoint files in python using the machine user's access to the file. I need pandas output and a reliable method to refresh the query. I am thinking of using excel to run SharePoint queries.

I cannot use GraphAPI to do so.

What are some options?

Isaacnfairplay
  • 217
  • 2
  • 18

1 Answers1

-1

You can use the win32com.client module to do so.

This will quickly refresh the connection and load the data from the excel file.

Inspiration from the following stack overflow post: Refresh excel in python

import win32com.client as win32

def file_query_func(source_file: Path, source_sheet: str, query_time: int):
    def func():
        if source_file.suffix != '.xlsx' not in str(source_file):
            raise ValueError(f"Source file must be an Excel file: {source_file}")
        
        excel = win32.Dispatch("Excel.Application")
        workbook  = excel.Workbooks.Open(source_file)
        for conn in workbook.connections:
            conn.Refresh()
            print(f"Refreshed {conn}")
        excel.CalculateUntilAsyncQueriesDone() # this must be done on the workbook object itself
        workbook.Save() 
        print(f"Saved Workbook")
        workbook.Close(True)
        excel.Quit()
        return pd.read_excel(source_file,sheet_name=source_sheet)
    return func
Isaacnfairplay
  • 217
  • 2
  • 18