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.
To get started, follow the steps below to integrate Subject7 with SharePoint:
1. Go to https://TENANT_NAME.sharepoint.com/sites/SITE_NAME/_layouts/15/appregnew.aspx to create a new app
2. Fill the required values to create an app and click Create. For App Domain and Redirect URI, you can provide anything you want, for example: localhost / https://localhost
3. Copy the Client ID and Client Secret
4. Go to next page to add permissions to created app for a required site: https://client_tenant.sharepoint.com/sites/site_name/_layouts/15/appinv.aspx
For example, https://subjectseven.sharepoint.com/sites/Alexei-Test/_layouts/15/appinv.aspx
5. Insert Client ID from step 3 and click on “Lookup” button. Application will be found and associated with form.
6. Add required permissions to the related field. Permissions are provided in XML format. For example:
<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="FullControl" />
</AppPermissionRequests>7. If required FullControl can be replaced with set of permissions like Read and Write, Download, etc.
8. After everything is set click on “Create” button and then “Trust” button.
9. On Subject7, go to Administration > Project Management
10. Click Integrations tab
11. Click Enabled next to Integration Status
12. Enter the Tenant, Site, Client ID and Client Secret. The Tenant is part of the URL you use to access SharePoint and the Site Name can be retrieved from the URL after accessing a specific site. For example, if the URL is https://subject7.sharepoint.com/sites/Test/Shared%20Documents/Forms/AllItems.aspx, then the Tenant is subject7 and the site is Test
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. 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.
9. After executing this step, a list will be created from the SharePoint file.
10. 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:
Add_Column
Add_Row
Modify_Value
Remove_Column
Remove_Row
11. 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.
12. Once your modifications are completed, you can use Print command to upload the list back to SharePoint as a file.
13. Under Print command, click List next to Source Type
14. In the List Variable, enter @ sign to show the list of variables and select the variable name from your Set_List step
15. 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.
16. 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.
17. Select the extension of the exported file next to Type to either XLS or CSV.
18. 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.
19. If S7 Storage is selected, enter a variable name to assign the URL to it.
20. If Remote is selected, enter the below:
Filename: The name of the file that will be uploaded to SharePoint with the extension. For example, List_of_users.xlsx
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.
21. 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.
22. 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.
23. Once all steps are executed, your new file will be uploaded to SharePoint.
Comments
Please sign in to leave a comment.