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

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.

 

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

 

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.

 

 

Excel operation in Office365

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

 

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

 

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”

 

・Properties of “Write Cell”

 

・Folder where the target Excel file is stored

 

・Target Excel file before workflow execution

 

・Target Excel file after workflow execution

 

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

 

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

 

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

 

・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

 

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

 

 

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

 

 

 

summary

Back to Table of Contents

 

 same category UiPath