How to integrate Subject7 with SharePoint and modify Excel files

If you have files stored on SharePoint and you would like to automate file modification, Subject7 allows you to integrate with SharePoint to read data from an Excel file, modify it during the test automation, and write data back to the Excel file.

Note that since the beginning of 2024, Microsoft has disabled creating authentication configuration using ACS (Azure Access Control service) and replaced it with Entra ID (previously Azure AD). Customers with existing applications which use ACS are still supported until April 2nd, 2026 while all new customers will be required to create an app using Entra ID configuration. 

To get started, follow the steps below to integrate Subject7 with SharePoint:

1. Navigate to https://azure.microsoft.com/en-us/ and click Sign in
2. In the search box on top, look for Microsoft Entra ID and click it
3. On the left panel, expand Manage and click App Registrations
4. Click New Registration
5. Enter a name for your application and leave the default option selected "Accounts in this organizational directory only (Subject7 only - Single tenant)". The Redirect URI is optional so no need to select anything. Click Register
6. After registering, you will be taken to the Overview section of new application. Next to the field called Application (client) ID, copy the value for integration setup. Also there is a field called Directory (tenant) ID - copy that value too.
7. On the left panel, expand Manage and navigate to API permissions section and click to add a new permission
8. Select Microsoft Graph > Application Permission and scroll to Sites
9. Expand Sites and locate the permission Sites.ReadWrite.All, then select it. Click Update Permissions.
10. Permission will be marked as yellow telling that it was not applied. Click on Grant admin consent above.
11. Navigate to section Certificates & secrets on the left oanel and create a new client secret. You will see ID and value of the new secret. Copy only the value.
12. On Subject7, go to Administration > Project Management
13. Click Integrations tab
14. Click Enabled next to Integration Status
15. Click Entra ID if this is your first time creating this integration
16. Enter the Tenant Name, Tenant ID, Client ID and Client Secret. The Tenant Name is part of the URL you use to access SharePoint
17. Once the integration with SharePoint is completed, you can now start to create automated tests. In your test case, follow these steps:
    1. Create a step with Set_List command and pick XLS or CSV from the Source Type
    2. Next to XLS Type or CSV Type, pick URL
    3. On SharePoint, locate the file you would like to create a list from by clicking on the checkmark next to it. If the Details page is not open, click on i icon on the right side.
    4. Scroll down on the right side to get to Path and click on Copy Direct link next to the Path
    5. Back on Subject7, paste the Path in the URL field.
    6. If the first line contains column names that you would like to display as header names, enable the option First line contains column names
    7. If you would like the list to start from 1, you can check the option
    8. Use '1' as column and row starting index, otherwise you can leave it unchecked.
    9. If you would like to read the entire file, you can leave Full selected next to Range, or if you would like to generate only some of the data into the list, you can click Selected and specify where to read data from. In order to get the Cell Index, you can simply click the cells on SharePoint and use the index which is visible on the left side as highlighted below.
    10. After executing this step, a list will be created from the SharePoint file.
    11. You can now use Modify_List command to make your modifications to the list on Subject7. Nothing will be modified on SharePoint just yet. The allowed modifications are:
        
        1. Add_Column
        2. Add_Row
        3. Modify_Value
        4. Remove_Column
        5. Remove_Row
    12. Suppose that your scenario is to add a new column called ID No. and fill it with values. You can use Add_Column and specify a column name. Then use Modify_Value to modify the values of the new rows under ID No. column.
    13. Once your modifications are completed, you can use Print command to upload the list back to SharePoint as a file.
    14. Under Print command, click List next to Source Type
    15. In the List Variable, enter @ sign to show the list of variables and select the variable name from your Set_List step
    16. If you would like to upload only a specific range from the file, you can add indexes in the From and To section. Otherwise, you can leave the default on which is Start to End.
    17. If you would like to include the header names into the exported file, you can check the option Include column names as first row, otherwise no header names will be exported if this option is unchecked.
    18. Select the extension of the exported file next to Type to either XLS or CSV.
    19. Next to Location, select Remote if you would like to upload to SharePoint or S7 Storage if you would like to get a link to a file stored on Subject7 repository. 
    20. If S7 Storage is selected, enter a variable name to assign the URL to it.
    21. If Remote is selected, enter the below:
        
        1. Filename: The name of the file that will be uploaded to SharePoint with the extension. For example, List_of_users.xlsx
        2. URL: A path of sample file which will be downloaded from SharePoint and re-uploaded with the new data from your list. This can be the same as the URL that you used in Set_List.
    22. If you select XLSX, you will notice Action options to appear. In this field, you can specify where you would like the new data to be appended to the existing file that you selected in the URL field. You can specify a Sheet Name, an Insert Position by Index and a Shift position.
    23. From the Action dropdown, you can also select Insert or Overwrite or New File where Insert will insert the data and shift the position of the other data based on your selection. Whereas Overwrite will simply overwrite the data without performing any shifting of the existing data and New File will create a new file.
    24. Once all steps are executed, your new file will be uploaded to SharePoint.