Flex.bi Support Center
Flex.bi Support Center
Breadcrumbs

How to import data from an Excel document in MS Sharepoint

  1. Go to https://portal.azure.com/#home and click on the View button under Manage Microsoft Entra ID.

    Screenshot 2024-08-26 at 13.01.29.png

  2. Click on the option App Registrations.

    Screenshot 2024-08-26 at 13.03.22.png


  3. Click on the New Registration button.

    Screenshot 2024-08-26 at 13.04.38.png


  4. Set up a new application as shown in the screenshot below. Enter redirect URL https://flex.bi/bi/source_applications/auth/rest_api/callback.

    Screenshot 2024-08-26 at 13.07.15.png


  5. Copy the Application (client) ID and store it for later use. Click on the Add a certificate or secret link.

    Screenshot 2024-08-26 at 13.08.53.png


  6. On the next page, click on the New client secret option.

    Screenshot 2024-08-26 at 13.14.49.png

  7. On the next page, enter a name for the new client secret and select one of the period options. Then click on the Add button.

    Screenshot 2024-08-26 at 13.18.04.png


  8. Copy the Value of the Secret. You will need it later, to set up the Rest API connection.

    Screenshot 2024-08-26 at 13.20.25.png


  9. Select API Permissions from the menu on the left.

    Screenshot 2024-08-26 at 13.23.43.png


  10. Click on the Add Permission option.

    Screenshot 2024-08-26 at 13.25.08.png


  11. On the next page, click on Microsoft Graph tile.

    Screenshot 2024-08-26 at 13.26.03.png


  12. Click on Delegated permissions.

    Screenshot 2024-08-26 at 13.27.03.png

  13. Select the Files.Read.All permission and then click on the Add permissions button.

    Screenshot 2024-08-26 at 13.28.09.png


  14. Do the same, to add the Sites.ReadWrite.All permission.

    Screenshot 2024-08-26 at 14.01.14.png


  15. Go to your http://flex.bi account and create a new Rest API application. Use the following settings:

    1. Client ID: the value you copied earlier.

    2. Client Secret: the value you copied earlier.

    3. Scope: offline_access https://graph.microsoft.com/.default.

    4. Authorize URL: https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize.

    5. Token URL: https://login.microsoftonline.com/organizations/oauth2/v2.0/token.

  16. Set the following Source Data URL and click on the Continue button: https://graph.microsoft.com/v1.0/sites?search=*

  17. On the popup, grant the required access to your Microsoft account.

    Screenshot 2024-08-26 at 14.01.59.png


  18. On the data preview page, expand the values section and find the id of your SharePoint site. Copy it for later use.

    Screenshot 2024-08-26 at 14.02.40.png


  19. Click on the Back to Edit button and change the Source Data URL to the following: https://graph.microsoft.com/v1.0/sites/{site ID}/drives, where {site ID} is the id value you just copied. Click on the Continue button.

  20. On the data preview page, expand the values section and find the id of your SharePoint drive. Copy it for later use.

    Screenshot 2024-08-26 at 14.11.58.png


  21. Click on the Back to Edit button and change the Source Data URL to the following: https://graph.microsoft.com/v1.0/sites/{site ID}/drives/{drive ID}/items/root/children, where {site ID} is the id value you used before and {drive ID} is the id you just copied. Click on the Continue button.

  22. On the data preview page, expand the values section and find the id of your Excel document. Copy it for later use.

    Screenshot 2024-08-26 at 14.14.39.png


  23. Click on the Back to Edit button and change the Source Data URL to the following: https://graph.microsoft.com/v1.0/sites/{site ID}/drives/{drive ID}/items/{item ID}/workbook/worksheets/{worksheet name}/usedRange, where {site ID}, {drive ID}are the id values you used before and {item ID} is the id you just copied, and {worksheet name} is the name of the worksheet that you want to import. Click on the Continue button.

  24. Now you should see the containts of the Exel document ready for mapping and import.