| Note that this HelloID connector has not been tested in a production environment! |
| ℹ️ Information |
|---|
| This repository contains the connector and configuration code only. The implementer is responsible to acquire the connection details such as username, password, certificate, etc. You might even need to sign a contract or agreement with the supplier before implementing this connector. Please contact the client's application manager to coordinate the connector requirements. |
This connector retrieves data from an Excel Online Sheet
It now supports that it is located in a Onedrive Folder from a User or a Sharepoint Site
By using this connector you will have the ability to get data from an Excel Online sheet.
The first step to connect to Graph API and make requests, is to register a new Azure Active Directory Application. The application is used to connect to the API and to manage permissions.
- Navigate to App Registrations in Azure, and select “New Registration” (Azure Portal > Azure Active Directory > App Registration > New Application Registration).
- Next, give the application a name. In this example we are using “HelloID PowerShell” as application name.
- Specify who can use this application (Accounts in this organizational directory only).
- Specify the Redirect URI. You can enter any url as a redirect URI value. In this example we used http://localhost because it doesn't have to resolve.
- Click the “Register” button to finally create your new application.
Some key items regarding the application are the Application ID (which is the Client ID), the Directory ID (which is the Tenant ID) and Client Secret.
The Microsoft Graph documentation provides details on which permission are required for each permission type.
To assign your application the right permissions, navigate to Azure Portal > Azure Active Directory >App Registrations. Select the application we created before, and select “API Permissions” or “View API Permissions”. To assign a new permission to your application, click the “Add a permission” button. From the “Request API Permissions” screen click “Microsoft Graph”. For this connector the following permissions are used as Application permissions:
- Read files in all site collections (Onedrive) by using Files.Read.All
- Read items in all site collections (Sharepoint) by using Sites.Read.All
Some high-privilege permissions can be set to admin-restricted and require an administrators consent to be granted.
To grant admin consent to our application press the “Grant admin consent for TENANT” button.
There are multiple ways to authenticate to the Graph API with each has its own pros and cons, in this example we are using the client_credentials grant type.
- First we need to get the Client ID, go to the Azure Portal > Azure Active Directory > App Registrations.
- Select your application and copy the Application (client) ID value.
- After we have the Client ID we also have to create a Client Secret.
- From the Azure Portal, go to Azure Active Directory > App Registrations.
- Select the application we have created before, and select "Certificates and Secrets".
- Under “Client Secrets” click on the “New Client Secret” button to create a new secret.
- Provide a logical name for your secret in the Description field, and select the expiration date for your secret.
- It's IMPORTANT to copy the newly generated client secret, because you cannot see the value anymore after you close the page.
- At last we need to get is the Tenant ID. This can be found in the Azure Portal by going to Azure Active Directory > Custom Domain Names, and then finding the .onmicrosoft.com domain.
The following settings are required to connect to the API.
| Setting | Description |
|---|---|
| Client ID | Id of the Azure app |
| Client Secret | Secret of the Azure app |
| Tenant ID | Id of the Azure tenant |
| Use Sharepoint (instead of Onedrive) | By default the Script searches for the file in a users onedrive folder - with this switch you can select to search a Sharepoint Site |
| Site Name (Sharepoint) | Name of the Sharepoint Site where the file is located |
| List Name (Sharepoint) | If the File is not located in the Default Documents Folder - Name the List where it is located |
| User ID (Onedrive) | Id of the Azure User where the Sheet is located - example: 12345678-1234-1234-1234-12345678901234 |
| Document Path | Path to the document - Replace "/" with %2F - example: sheet.xlsx if it is located in the root of your documents - folder%2Fsheet.xlsx if it is in a subfolder |
| Table Name | Name of the Sheet in the Document - example: Sheet1 or Tabelle1 |
Please correct the column numbers in the powershell script
Please make sure that you clicked on "Insert -> Table" in Excel or it will not work
For more information on how to configure a HelloID PowerShell connector, please refer to our documentation pages
If you need help, feel free to ask questions on our forum
The official HelloID documentation can be found at: https://docs.helloid.com/