How can I read range of an excel file located on the SharePoint and create a new excel file by adding the value (No Document) at the end of some rows?

Help Studio
1

How can I read range of an excel file located on the SharePoint and create a new excel file by adding the value (No Document) at the end of some rows?

Basically as we cant connect and download the file and reupload the file to the sharepoint, I want to find an alternative to copy the info of the Excel file and add the informaiton of the No document at the end of some rows (depending if the rows has been processed or not). Maybe by coping the file somewhere else and work from that location instead from the sharepoint??

Forum Engagement Daily Reports
2

I dont think so we can edit Excel file directly on SharePoint, so the best approach is to copy the file locally update it, and then upload it back.

3

Hi @mce

Copy the SharePoint Excel file to a local working folder, use read range to load the data into a DataTable, loop through the rows to append "No Document" where required, then write the updated DataTable into a new Excel file stored locally, and finally move or upload that file back to SharePoint

Happy Automation

4

Use Read Range activity of O365 Integration.

image
image313×189 2.65 KB

Use Copy File or Folder activity to copy the file to another location with name

image
image310×250 5.53 KB

Use Write Cell activity to write the desired value

image
image317×200 2.92 KB

5

Hi How to copy the sharepoint Excel file to a local without creating a connexion ?

6

download it using HTTP Request.Give the bot the shared link so that anyone with the link can view and this removes the need for credentials inside UiPath.
Then use HTTP Request → Method: GET → URL = shareable link → Output = local file.Hope it helps

7

@mce

you mean you want to do it via ui?

if so its not a good option

better create a connection or get client creds and use them in scope

you have all activities acailable

you can simply copy the file in sharepoint and then use write cell to write directly

cheers

8

and then ? It seems that It doesnt download the file. I have used the following parameter =>

image
image1607×606 35.8 KB

but still not able to download it

9

Please check if the file is shared publicly (Anyone with link) if yes then copy the link and Convert it to a direct-download link by adding in the end “&download=1”
Exple: https://company.sharepoint.com/:x:/g/…?e=abcd123&download=1

10

Not sure I understand what to do? Can you explain it further ?

11

1.Go to the file → Click Share and you see option like " Anyone with the link" you MUST choose this
2.Then copy the url link
3.Convert it to a direct-download link - By adding “&download=1” in the end ot the url
4. Use this final link in UiPath HTTP Request**

Method: GET
Url: the download=1 link
Save response → your local file path