Microsoft 365 - Excel¶
Process and perform operations on your online Excel files through a Microsoft 365 account. Write, read, and update your spreadsheets easily via the BotCity plugin for Excel.
Warning
To be able to use this plugin, you need to have a Microsoft 365 account with a properly created and configured project.
After creating a project, you need to complete the authentication process through the Microsoft 365 Credentials plugin.
For more details, see the previous sections:
Installation¶
Importing the Plugin¶
After you installed this package, the next step is to import the package into your code and start using the functions.
Setting the service account¶
As mentioned above, in order to use the Excel plugin, it is necessary to perform the account authentication process through the Microsoft 365 Credentials plugin.
With the credentials plugin instance obtained, let's use it to instantiate the Excel plugin.
from botcity.plugins.ms365.credentials import MS365CredentialsPlugin, Scopes
from botcity.plugins.ms365.excel import MS365ExcelPlugin
# Instantiate the Credentials plugin
service = MS365CredentialsPlugin(
client_id='<APPLICATION ID>',
client_secret='<SECRET VALUE>',
)
service.authenticate(scopes=[Scopes.BASIC, Scopes.FILES_READ_WRITE_ALL])
# Instantiate the Excel plugin
excel = MS365ExcelPlugin(service_account=service)
Tip
To use the Excel service, you can only add the scope Scopes.FILES_READ_WRITE_ALL referring to the Files.ReadWrite.All permission of the project.
See more details about scopes and permissions at this link.
Getting the Excel file¶
The first step we must do to use Excel services is to configure the file that will be used.
This Excel file must be stored on a OneDrive that you have access to with your account. With that in mind, we can get the file reference in two ways.
Using file path in OneDrive¶
The simplest way to search for the Excel file that will be used is to pass the file's absolute path on OneDrive.
In this case, the default OneDrive of the account will be used as a reference.
Tip
The file path must follow the pattern: '/path/to/file'.
The first / means we are starting from the root folder, and the rest of the path will be the "absolute path" of the item on the drive.
Using the OneDrive plugin to get the file¶
If you are already using the OneDrive plugin in your code or for some other reason, prefer to do it this way, you also have the option to get the file using the OneDrive plugin.
Using one of the methods that return a File object, you can use this returned object as a parameter of the get_excel_file method.
from botcity.plugins.ms365.credentials import MS365CredentialsPlugin, Scopes
from botcity.plugins.ms365.onedrive import MS365OneDrivePlugin
from botcity.plugins.ms365.excel import MS365ExcelPlugin
# Instantiate the Credentials plugin
service = MS365CredentialsPlugin(
client_id='<APPLICATION ID>',
client_secret='<SECRET VALUE>',
)
service.authenticate(scopes=[Scopes.BASIC, Scopes.FILES_READ_WRITE_ALL])
# Instantiate the OneDrive Plugin
onedrive = MS365OneDrivePlugin(service_account=service)
# Instantiate the Excel plugin
excel = MS365ExcelPlugin(service_account=service)
# Getting the Excel file using the OneDrive plugin
file = onedrive.get_file_by_name("My-File.xlsx")
# Setting the file to be used through the 'File' object
excel.get_excel_file(file_or_path=file)
Tip
See more details about ways to search for files in the drive by accessing the OneDrive plugin documentation.
Managing worksheets¶
We can start performing operations on the worksheets with the Excel file properly configured.
Using the get_excel_file method, we can pass the worksheet name that will be used as a reference in the operations.
If no specific worksheet is passed, the first worksheet in the file will be used by default.
Fetching existing worksheets¶
In addition to defining which worksheet will be used when searching for the Excel file, we can list all existing worksheets or search for a specific worksheet.
We can also define a new active worksheet using the Worksheet object returned or through the worksheet's name.
# Returning the list of worksheets
sheets = excel.list_sheets()
# Returning a specific Worksheet
results_sheet = excel.get_worksheet(sheet_name="Results")
# Defining the active sheet through the 'active_sheet' property
excel.active_sheet = results_sheet
# Another way to configure the active worksheet
excel.set_active_sheet("New Sheet")
Creating and removing worksheets¶
You can also create new sheets in the file and delete existing sheets.
Tip
To be able to use a sheet that was created, you must use the set_active_sheet method right after creation.
Reading data from worksheet¶
We can easily return the contents of a worksheet in different ways.
We can search for a specific cell, row, or column. In addition, we can return all existing content or even content only from a specific range.
Warning
The Microsoft Graph API uses some patterns for certain types of data.
If you use cells with the Date format in your spreadsheets, remember that the API always returns these values in the pattern mm/dd/yyyy.
Therefore, even using dates in the format dd/mm/yyyy, do the necessary treatments when reading the data using the plugin.
If possible, we recommend using dates in the spreadsheet in Text format so that the API returns the content without applying specific patterns.
When searching for the entire content of the worksheet, a list of lists will be returned, where each list will correspond to a row of the worksheet.
# Returning all existing content in the worksheet in the format: List[List[object]]
values = excel.as_list()
# Each list corresponds to a row in the worksheet
for row in values:
print(row)
# Returning the content of a given range of the worksheet
range_values = excel.get_range(range_="A1:E5")
# Each list corresponds to a row in the worksheet
for row in range_values:
print(row)
Writing new data¶
To write new data in the worksheet, we can use the same strategy we use for reading.
You can enter data in a single cell or use a specific worksheet range.
Warning
The Microsoft Graph API uses some patterns for certain types of data.
If you use float numbers, we recommend using . instead of , when entering the data in the worksheet.
This way, we prevent the API from applying unwanted formatting to the values used.
# Entering data in a specific cell
excel.set_cell(column='L', row=7, value="Finished")
# Entering data in a specific range
# Each list represents a line that will be inserted
values = [
['Product', 'Stock', 'Price'],
['Notebook', '500', '1250.95'],
['Smartphone', '250', '700.99'],
['Mouse', '90', '65.25']
]
# The range used must be compatible with the size of the data list
excel.set_range(range_="A1:C4", values=values)
In addition, we can also insert new rows or columns. By default, these new values will be inserted at the end of the worksheet, but you can also use a specific range.
Deleting data¶
In addition to reading and writing data in the worksheet, we could remove existing data.
It is possible to clear all the worksheet content and remove values from a range or a specific row or column.
Warning
When using the methods to remove columns, remember that each column removed will cause the columns to its right to be moved to the left.
When using the methods to remove rows, remember that each row removed will cause the rows below it to move up.
# Clearing all worksheet content
excel.clear()
# Clearing the values of a specific range
excel.clear(range_="B2:D10")
# Removing values from a single column
excel.remove_column(column="C")
# Removing values from specific columns
excel.remove_columns(columns=["F", "H", "J"])
# Removing values from a single row
excel.remove_row(row=15)
# Removing values from specific rows
excel.remove_rows(rows=[2, 4, 6])
Tip
For all the above operations, you can pass the worksheet that will be used through the worksheet parameter.
In this case, when passing a worksheet as a parameter, it will be used as a reference in the context of the method instead of the current active worksheet.