How to Read Excel File from Mailbox Folder using Microsoft Graph API and PowerShell

Key Takeaways

• How to use Microsoft Graph API and PowerShell to read an email from a mailbox folder that has an Excel attachment and store the result in an array.
• How to use the Microsoft Graph PowerShell SDK module and the Import-Excel module to work with the data.

Learn how to use Microsoft Graph API and PowerShell to read the latest email from a mailbox folder that has an Excel attachment and store the result in an array.

Microsoft Graph API is a powerful tool that allows you to access data from various Microsoft 365 services, such as Outlook, OneDrive, SharePoint, and more. One of the common tasks that you may want to perform with Microsoft Graph API is to read an email from a mailbox folder and extract the data from an attachment. In this article, we will show you how to use Microsoft Graph API and PowerShell to read the latest email from a mailbox folder that has an Excel attachment with a common name and is always sent by the same sender and subject. We will also show you how to store the result in an array for further processing.

Prerequisites

Before you start this tutorial, you should have the following:

• A Microsoft 365 account with an Exchange Online mailbox
• PowerShell 7 or higher installed on your computer
• Microsoft Graph PowerShell SDK module installed on your computer
• An app registration in Azure Active Directory (AAD) with the following permissions:
  • Mail.Read
  • Files.Read
  • offline_access

Step 1: Connect to Microsoft Graph

The first step is to connect to Microsoft Graph using the Connect-MgGraph cmdlet. This cmdlet will prompt you to sign in with your Microsoft 365 account and consent to the permissions requested by the app registration. You will need to provide the client ID and tenant ID of the app registration as parameters. For example:

Connect-MgGraph -ClientId "your-client-id" -TenantId "your-tenant-id"

Step 2: Get the latest email from the mailbox folder

The next step is to get the latest email from the mailbox folder that matches the criteria. You can use the Get-MgUserMessage cmdlet to retrieve the messages from a specific folder. You will need to provide the user ID and the folder ID as parameters. You can also use the -Filter parameter to filter the messages by the sender and the subject. For example:

$userId = "your-user-id"
$folderId = "your-folder-id"
$sender = "[email protected]"
$subject = "Excel Report"
$message = Get-MgUserMessage -UserId $userId -MailFolderId $folderId -Filter "from/emailAddress/address eq '$sender' and subject eq '$subject'" -Top 1 -OrderBy "receivedDateTime desc"

The -Top 1 parameter will return only the latest message, and the -OrderBy parameter will sort the messages by the received date and time in descending order.

Step 3: Get the Excel attachment from the email

The next step is to get the Excel attachment from the email. You can use the Get-MgUserMessageAttachment cmdlet to retrieve the attachments from a specific message. You will need to provide the user ID, the message ID, and the attachment ID as parameters. You can also use the -Expand parameter to get the content of the attachment. For example:

$attachmentId = "your-attachment-id"
$attachment = Get-MgUserMessageAttachment -UserId $userId -MessageId $message.Id -AttachmentId $attachmentId -Expand "content"

The -Expand “content” parameter will return the binary content of the attachment as a byte array.

Step 4: Convert the Excel attachment to a CSV file

The next step is to convert the Excel attachment to a CSV file. You can use the Import-Excel module to work with Excel files in PowerShell. You will need to install this module on your computer if you don’t have it already. You can use the Import-Excel cmdlet to import the Excel file from the byte array and convert it to a CSV file. For example:

Import-Module ImportExcel
$excelFile = [System.IO.MemoryStream]::new($attachment.Content)
$csvFile = "output.csv"
Import-Excel -Path $excelFile -WorksheetName "Sheet1" | Export-Csv -Path $csvFile -NoTypeInformation

The -WorksheetName parameter will specify the name of the worksheet to import. The -NoTypeInformation parameter will omit the type information from the CSV file.

Step 5: Read the CSV file and store the result in an array

The final step is to read the CSV file and store the result in an array. You can use the Import-Csv cmdlet to import the CSV file and convert it to a PowerShell object. You can then assign the object to a variable or an array. For example:

$data = Import-Csv -Path $csvFile

You can now access the data in the array using the column names or the index. For example:

$data[0].Name # returns the name of the first row
$data[1].Age # returns the age of the second row

Frequently Asked Questions (FAQs)

Question: How can I get the folder ID of a mailbox folder?

Answer: You can use the Get-MgUserMailFolder cmdlet to list the folders in a mailbox. You can also use the -Filter parameter to find a folder by name. For example:

Get-MgUserMailFolder -UserId $userId -Filter "displayName eq 'Reports'"

Question: How can I get the attachment ID of an email attachment?

Answer: You can use the Get-MgUserMessageAttachment cmdlet to list the attachments in a message. You can also use the -Filter parameter to find an attachment by name. For example:

Get-MgUserMessageAttachment -UserId $userId -MessageId $message.Id -Filter "name eq 'Report.xlsx'"

Question: How can I create an app registration in Azure Active Directory?

Answer: You can follow the steps in this tutorial to create an app registration and grant the required permissions.

Summary

In this article, we have shown you how to use Microsoft Graph API and PowerShell to read the latest email from a mailbox folder that has an Excel attachment and store the result in an array. We have also shown you how to use the Microsoft Graph PowerShell SDK module and the Import-Excel module to work with the data. We hope you have found this article useful and informative. If you have any questions or feedback, please feel free to leave a comment below.

Disclaimer: This article is for educational purposes only and does not constitute legal or professional advice. The author and the publisher are not liable for any damages or losses that may result from the use of the information in this article.