UiPath

UiPath MicrosoftOffice365 Excel related activity use case

In UiPath Studio development, there are cases where you want to manipulate Excel stored in OneDrive or SharePoint in Office365.

This article describes how to manipulate Excel files stored in OneDrive and SharePoint using Microsoft Office365 Activities.

 

 Related Articles Learn the Creation Techniques f UiPath robotics creation with Udemy’s online courses that take it up a notch

This site was created by translating a blog created in Japanese into English using the DeepL translation.

Please forgive me if some of the English text is a little strange

How to Prepare and Use Office365 Activities

How to Install Office365 Activities

Microsoft Office365 Activities are not present in the process created by default.

A separate MicrosoftOffice365.Activities activity package must be added.

 

For instructions on how to install Microsoft Office365 Activities, please refer to the Installing the Office365 Activities Package article.

 

How to use Office365 Activities

Each Office365 activity is used by placing the activity within the “Microsoft Office 365 Scope” or “Use OneDrive and SharePoint”.

The “Microsoft Office 365 Scope” is recommended since all activities under the “Integration>Microsoft>Office365” can be used.

However, “App Registration” must be configured for AzureAD.

 

F-pen
F-pen
See Authentication with Microsoft Office 365 Scope for specific setup instructions.

“Use OneDrive and SharePoint” only uses activities under the “Integration>Microsoft>Office365>Files” and can only manipulate files on Office365.

sea other
sea other
See Authentication with OneDrive and SharePoint for specific setup instructions.

 

List of file manipulation activities in Office365

Activities that manipulate Office365 files can be found under “Integration>Microsoft>Office365>Excel”.

 

The following table shows the activities under “Integration>Microsoft>Office365>Excel”.

Activity Location Activity Name What you can do in Activities
Integrations> Microsoft> Office365>
Excel
VLookup Range Uses the Microsoft Graph Use workbook functions API to find a value in a range using the VLOOKUP function.
Copy Sheet Uses the Microsoft Graph Get range, Add worksheet, and Update range APIs to copy an existing worksheet (CopySheet) in a specified workbook (CopyWorkbook) and then paste it as a new worksheet (PasteSheet) into the same or a different workbook (PasteWorkbook).
Get Sheets Uses the Microsoft Graph Get worksheet API to retrieve and return an array of worksheets (Sheets) from a specified workbook (Workbook).
Add Sheet The Add Sheet activity uses the Microsoft Graph Add worksheet API to add a new worksheet (Sheet) to a specified workbook (Workbook).
Rename Sheet Uses the Microsoft Graph Update worksheet API to change the name of a specified workbook (Workbook) worksheet from its current name (OriginalSheetName) to a new name (NewSheetName).
Write Cell Uses the Microsoft Graph Update range API to write a value (Cell Value) to a specified cell (CellAddress) in a workbook (Workbook) worksheet (SheetName).
Get Cell Color Uses the Microsoft Graph Get range format API to retrieve the color properties of specified cells (CellAddress) in a workbook (Workbook) worksheet (SheetName).
Read Cell Uses the Microsoft Graph Get range API to retrieve the value of a specified cell (CellAddress) in a workbook (Workbook) worksheet (SheetName).
Create Workbook Uses the Microsoft Graph Upload item API to create a new workbook (Name) and upload it to a specified location (Parent).
Read Column Uses the Microsoft Graph Get range API to retrieve cell values from a specified column (StartingCell) in a workbook (Workbook) worksheet (SheetName).
Write Range Uses the Microsoft Graph Update range API to write a range of cell data (DataTable) starting from a specific cell (Starting Cell) in a specified workbook (Workbook) worksheet (SheetName).
Set Range Color Uses the Microsoft Graph Update range format API to set the background color of a specified range.
Clear Range Uses the Microsoft Graph Clear range API to clear values, format, fill, border, etc. of a range from the specified sheet.
Copy Range Uses the Microsoft Graph Get range, Add worksheet, and Update range APIs to copy a range and pastes it to another location.
Delete Range Uses the Microsoft Graph Clear range (when Shift is None) or Delete range (when Shift is Up or Left) API to delete a range of cell data (Range) from a specified workbook (Workbook) worksheet (SheetName).
Append Range Uses the Microsoft Graph Update range API to write a range of cell data (DataTable) to a specified workbook (Workbook) worksheet (SheetName).
Read Range Uses the Microsoft Graph Get range API to retrieve values from a specified range of cells (Range) in a workbook (Workbook) worksheet (SheetName).
Read Row Uses the Microsoft Graph Get range API to retrieve the values of cells from a specified row (StartingCell) in a workbook (Workbook) worksheet (SheetName).
Integrations> Microsoft> Office365>
Excel> Tables
Get Table Range Uses the Microsoft Graph Get range API to retrieve the range associated with the specified table.
Create Table Uses the Microsoft Graph Add table API to create a table from the specified range.
Delete Column Uses the Microsoft Graph Delete table column API to delete a column from a table.
Insert Column Uses the Microsoft Graph Create Column API to insert a new column in a table. The previous column at the indicated position is shifted to the right.
Delete Rows Uses the Microsoft Graph Delete table row API to delete the specified rows from a table.
Insert Rows Uses the Microsoft Graph Create row API to insert rows at the specified position in a table. Any rows below the inserted rows are shifted downwards.

 

F-pen
F-pen
For other activities, see the list of Office365 activities.

 

Excel operation in Office365

To work with Excel in Office365, use the activity found under “Integration>Microsoft>Office365>Excel”.

Sea Other
Sea Other
In this chapter, I’ll show you some examples of frequently used activities.

 

Create Workbook

To create a new Excel file on OneDrive or SharePointOnline, first use the “Find Files And Folders” activity to find the folder where the file will be created and get it as a “Microsoft.Graph.DriveItem”.

The retrieved DriveItem can then be used in the “Create Workbook” activity to create an Excel file.

 

Create Workbook setting items

Setting Location Setting item Setting details
Properties Common DisplayName The display name of the activity.
Input Conflict Behavior Indicates the conflict resolution behavior in case a workbook with the same name already exists.
Name The name of the newly created workbook.
Parent The DriveItem of the parent directory under which the workbook is created.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Output Workbook The DriveItem of the newly created workbook.
Workbook URL The URL of the newly created workbook.

 

 

sample process
Search the target folder in SharePointOnline and create an Excel file using the retrieved DriveItem.

 

・Variable

 

・Properties of “Microsoft Office 365 Scope”

 

・Properties of “Find Files And Folders”

 

・Properties of “Create Workbook”

 

・Target folder for SPO before workflow execution

 

・Target folder for SPO after workflow execution

F-pen
F-pen
After the workflow is executed, an Excel file is created.

 

Write Cell

To write a cell to an Excel file on OneDrive or SharePointOnline, first search for the target Excel file in the Find Files And Folders activity and retrieve it as a Microsoft.Graph.DriveItem type DriveItem type”.

To write a cell to an Excel file in OneDrive or SharePointOnline, first search for the target Excel file in the “Find Files And Folders” activity, and then call the “Microsoft.Graph.DriveItem” type.

The retrieved DriveItem is then used in the “Write Cell” activity to write the cell.

 

Write Cell setting items

Setting Location Setting item Setting details
Properties Common DisplayName  The display name of the activity.
Input Cell Address The address to the cell in which the data is inserted.
Cell Value The value written to the given cell.
Sheet Name The name of the sheet from which data is read.
Workbook The DriveItem of the document to modify.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.

 

 

 

sample process
Search for the target Excel file in SharePointOnline and write to the cell using the retrieved DriveItem.

 

 

・Variable

 

・Properties of “Microsoft Office 365 Scope”

 

・Properties of “Find Files And Folders”

sea other
sea other
The Query is a string that uniquely identifies the target Excel, and the subfolder is the folder (or folder path) where the target Excel is stored.

 

・Properties of “Write Cell”

F-pen
F-pen
Setting the property as described above will write “B2-write” to the B2 cell of sheet “Sheet1”.

 

・Folder where the target Excel file is stored

 

・Target Excel file before workflow execution

 

・Target Excel file after workflow execution

sea other
sea other
The specified string is written in cell B2.

 

Write Range

To write to a range of Excel files on OneDrive or SharePointOnline, first search for the target Excel file in the “Find Files And Folders” activity, then select “Microsoft.Graph. DriveItem” type.

Next, the retrieved DriveItem is used in the “Write Range” activity to write to the range.

 

Write Range setting items

Setting Location Setting item Setting details
Properties Common DisplayName  The display name of the activity.
Input DataTable The table to insert.
Sheet Name The name of the sheet to which data is written.
StartingCell The address at which the top-left cell of the table is written.
Workbook The DriveItem of the document to modify.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Options IncludeHeaders If selected, the data table’s headers are also inserted into the workbook.

 

 

sample process
Read CSV file as a data table and write the contents of the data table to an Excel file in SharePointOnline.

 

・Variable

 

・Properties for Read CSV

 

・Properties for Microsoft Office 365 Scope

 

・Properties for Find Files And Folders

 

・Properties for Write Range

 

・CSV file to be read

 

・SharePoint folder where the target Excel file is stored

 

・Excel file before workflow execution

 

・Excel file after workflow execution

F-pen
F-pen
For the specified C2 to D5, the contents of the data table read in the CSV file are written.

 

Read Cell

To read cell contents from an Excel file in OneDrive or SharePointOnline, first search for the target Excel file in the “Find Files And Folders” activity, and then type “Microsoft. Retrieve the data.

Next, the contents of the cell are read by using the retrieved DriveItem in a “Read Cell” activity.

 

Read Cell setting item

設定場所 設定項目 設定内容
Properties Common DisplayName The display name of the activity.
Input Cell Address The address of the cell from which data is read.
Sheet Name  The name of the sheet from which data is read.
Workbook The DriveItem whose data is read.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level.
Options Value Type The type of data that is read from the document.
Output CellValue The cell’s contents.

 

 

 

sample process
Search for the target Excel file in SharePointOnline, read the specified cell using the retrieved DriveItem, and output to the log.

 

 

・Variables

 

・Properties of Microsoft Office 365 Scope

 

・Properties of Find Files and Folders

 

・Properties of Read Cell

 

・SharePoint folder where the target Excel file is stored

 

・Excel file to be loaded

 

・Log of workflow execution results

sea other
sea other
The value of the specified B3 cell is output to the log.

 

Read Range

To load a range of cells from an Excel file on OneDrive or SharePointOnline, first search for the target Excel file in the Find Files And Folders activity and get it as a Microsoft.Graph.DriveItem type DriveItem type.

Next, the cell is read in a range by using the retrieved DriveItem in a Read Range activity.

 

Read Range setting items

Setup Location Setting item Setting contents
Properties Common DisplayName The display name of the activity.
Input Range The range of cells to read.
Sheet Name The name of the sheet from which data is read.
Workbook The DriveItem whose data is read.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level1111
Options Add Headers If selected, the first row from the range is added sd column headers in the DataTable.
Value Type The type of data that is read from the document.
Output DataTable The result of the read.

 

 

sample process
Search for the target Excel file in SharePointOnline, read the specified range using the retrieved DriveItem, and output to the log.

 

 

・Variables

 

・Properties of Microsoft Office 365 Scope

 

・Properties of Find Files And Folders

 

・Properties of Read Range

F-pen
F-pen
By setting “A1:B4” as the input range, the contents of cells A1~B4 will be output.

 

・Properties of Output Data Table

 

・SharePoint folder where the Excel files to be loaded are stored

 

・Excel file to be loaded

 

・Log of workflow execution results

sea other
sea other
The contents of Excel files A1~B4 are output to the log.

 

Clear Range

To clear a range of cell values from an Excel file on OneDrive or SharePointOnline, first search for the target Excel file in the Find Files And Folders activity and then type Microsoft.Graph.DriveItem DriveItem type”.

The retrieved DriveItem is then used in a Read Range activity to clear a range of cell values.

 

Clear Range setting items

Setup Location Setting items Setting details
Properties Common DisplayName The display name of the activity.
Input Clear Action The default value is All.
Range Indicates the range that will be cleared.
Sheet Name The name of the sheet from which the range will be cleared.
Workbook The Drive Item of the Excel document.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level

 

 

 

sample process
Search for the target Excel file in SharePointOnline and clear the specified range using the retrieved DriveItem.

 

 

・Variables

 

・Properties of Microsoft Office 365 Scope

 

・Properties of Find Files And Folders

 

・Properties of Clear Range

 

・SharePoint folder where the target Excel file is stored

 

・Target Excel file

 

・Excel file after workflow execution

F-pen
F-pen
The values of cells A1~C2 are cleared.

 

 

Delete Range

To remove cell values from an Excel file on OneDrive or SharePointOnline, first search for the target Excel file in the Find Files And Folders activity and get it as a Microsoft.Graph.DriveItem type DriveItem.

The retrieved DriveItem is then used in a Delete Range activity to delete a range of cell values.

 

Setting items for Delete Range

Setup Location Setting items Setting details
Properties Common DisplayName The display name of the activity.
Input Range  The range to delete. Enter your range in A1-style notation.
Sheet Name The name of the sheet from which to delete the data.
Shift Indicates how the surrounding cells respond once the range is deleted.
Workbook The DriveItem of the document to modify.
Misc Private If selected, the values of variables and arguments are no longer logged at Verbose level

 

 

sample process
Search for the target Excel file in SharePointOnline and delete the specified range using the retrieved DriveItem.

 

・Variables

 

・Properties of Microsoft Office 365 Scope

 

・Properties of Find Files And Folders

 

・Properties of Delete Range

 

・SharePoint folder where the target Excel file is stored

 

・Target Excel file

 

・Excel file after workflow execution

sea other
sea other
Cells A1~C2 have been deleted, so the values of D1~E2 have been moved to A1~B2.

 

 

 

summary

  • To manipulate Excel on Office365 SharePoint or OneDrive, first add the MicrosoftOffice365.Activities activity package.
  • Microsoft Office 365 Scope” to the workflow that manipulates Excel files, and place the “Integration>Microsoft>Office365>Excel” activity within it.
  • Search for files and folders in the Find Files And Folders activity and retrieve the results as Microsoft.Graph.DriveItem type and use them in the Excel activity to manipulate them.

Back to Table of Contents

 

 same category UiPath

 

The operator of this blog, F-penIT blog