Chapter 10 spreadsheet automation. In this chapter, we will be going through an introduction of what spreadsheet automation is. What are the basic activities to automate a spreadsheet? What are the data table activities, and some sample use cases to see how these work. Introduction. spreadsheet automation is probably the most vital domain that needs lots and lots of automation in today's corporate world.

Almost every enterprise application uses spreadsheets, where the data is arranged in the form of rows and columns, forming a grid. This structuring of data makes it easier for data manipulations retrieval and a perfect candidate for its analysis and for the comprehension. Microsoft Excel application dominates in this area, as it provides my required capabilities is compatible with multiple formats such as CSV and is readily accessible across many operating system environments. UiPath provides a complete standalone package for basic Excel specific activities that are leveraged to perform operations, like once we showed in, you know, in the demo as well. Similar to the way data is stored in spreadsheets UiPath supports data structure known as data tables, where information is stored in the form of rows and columns. In this chapter, we'll be thoroughly using the principles of data operations, conditional activities, Variable Data entities, and overall workflow creation.

Basic activities, By now, you should be pretty comfortable in using UiPath activities, understand how the workflow is created, and make any ad hoc changes to optimize or debug an activity listed within the workflow. We'll provide a quick overview of some of the basic Excel activities in this chapter. So first and foremost, in case, you don't have an Excel or you don't see these Excel activities in your activities panel, I would ask you to go to Manage packages. And in the available search for Excel. For me, it's already installed. So it's showing a green mark.

If it's not, it would show an install button and they're just install them. They will be readily available for you in the activities panel. Okay, now the first activity we're going to talk about is Excel application scope. Okay, on working with MS Excel, this is always the first activity used in Excel automation. Excel application scope defines the directory where the target file for further processing is available. The operational actions are nested inside this activity under the do sequence And upon reaching end, the Excel file is automatically closed.

It acts as a container for all the activities within it and provides a logical scope or a boundary for the listed processes. If the file provided as an input doesn't exist, this activity will automatically create a blank file and perform the operations on it. thing to note here is that in properties under options label, visible attribute when checked, I'll show you this one. When checked reads the file available at the provided directory using MS Excel. For this Excel must be installed and displays all further processing. If unchecked, the file is read internally by the bot and is not shown to the user.

Everything happens in the back end itself. Another additional information is that the file path is provided in the Excel application scope. Being it's a string, like, you know, same way if we are providing a static value in here, it needs to be in the double quotes. And if it is a variable with the directory information, we can simply pass that variable and we don't need to have any double quotes. Right. So, we'll be using that in a sample use case in a minute, but first we'll go through all the activities.

Second is read range. Well read range scans data from an Excel file within the custom range provided as an input to the activity and stores it in a data table. Here right, so this data can be further modified using data operations which we have gone through and it is empty by default with significant which signifies like in here, the ranges, you know, by default empty which signifies the whole world worksheet will be read by the workflow. The range is provided in like the following format, like, you know, you want to read from let's say cell A one, two, C three, so you use colons to denote the range. And just to give you a quick example how this looks like. So a one, two c three, only this much data would be read using read range activity with these parameters, right?

So, that's how it is, then we have right range. Okay, so a quick thing. Right range, we have two separate. One is for workbook. One is for Excel, right? You can use any of those but since it is an Excel application that you will be using, you know, if you're using the Excel application scope, it's better to of course, use the Excel activities because they will be more compatible and the chances of them erroring out would be less So, right range activity, as the name itself tells is gonna take the data table as input and write it to the current Excel document, the current XML document being the one that is provided within the Excel application scope itself.

Right? The headers for a data table right need to be made sure that you know you're putting the data or not like if your data table has headers, so let's take a quick example. Right once you read the range, once you read data from an Excel sheet, right, there's an add headers property here, which means you also want the headers of that file to be read or the first row of the Excel sheet is going to be considered as the header for that. And the data table will also be in the same format as it is being read. And once you will be writing the data, if you want the headers to be present in that data into the Excel sheet Then you can add the headers, or if you just want the data port and new headers, then you can leave it as is, which is blank and the data would be written into the corresponding excel sheet.

Okay. All right. Next we're going to talk about is within Excel is it append range. Okay. append range behaves similar to the right range activity with one very slight difference. Instead of erasing the pre existing data, it appends itself after it.

So right range is going to overwrite. It doesn't matter. It doesn't, you know, like this activity doesn't care if there's any pre existing data in the Excel sheet or not. It's simply going to write from the starting cell, which is provided in here in the activity. And this is the input data table, but for the append range. It's gonna append on the pre existing data.

So by that We mean is that, you know, there are only two input parameters worksheet name and data table, as you can see, and it's going to see where the data is not available in the a column. And then from there itself, it's going to write the whole data. Right? Okay, fairly simple. And next, we're gonna talk about SOT table. Okay, this activity as the name itself tells, again, source the data table based on the column name provided to it as input.

So, here we have the column name, it could be like, you know, name or something, whatever the column name is, right? I'm taking the header as name. So it's gonna take that and it's gonna simply sort that data based on the alphabetical name or the numbers that are given in a column. So that's how it is and You should be always you know, sure that he used the correct Column Name Of course, and also that it should match precisely with the Excel what what is being provided in the Excel and if it exists the table name it is available under the design ribbon. So, this table name is sort of a little tricky thing here. Let me show you how to get a table name from an Excel right.

Suppose you have no suppose we have a table like this right within the Excel. So, if you go to the Design tab, right which gets automatically added once we have a table that has Here's the table name, and that's the table name that we need to give in the salt table. So, say this is like name class Okay, roll number section something some some school error or something right? If you want to sort this data on the basis of this column name, then here you provide name and the table name is going to be tabled to which again can be modified first in here and then correspondingly in here, but it needs to be there. Right? Okay.

Next is retail. So, retail as it says it's going to read a cell which is going to be the cell value will be provided in here, and the worksheet needs to be provided in here. And, of course, the Excel application scope is providing the Excel or the word workbook name. And it's going to read that cell and the output would be in here where we will create a new variable and stored the read data in in this variable, it will automatically do that. Right. And corresponding to read cell, we have right cell as well.

We use these, these are those activities which we use very extensively in Excel. Right? Because there may be like a click, I worked on those use cases on on those projects where we have to consolidate hundreds or thousands of Excel files into one file. And we read specific data out of those files. So these are these activities that are used in those cases. And, you know, you need to have some sort of standardization in terms of being able to automate a process and that's how we read the particular cells and we write in the particular control Related output file in the particular, you know, going through a loop or something, we type the data down and that's how it is.

So right cell corresponding to or, like pretty similar to the read cell, it's gonna write the data into the spreadsheet. and the value of like, this is the cell number where the right operation needs to take place. And this value is going to be the variable which we got probably from the retail or from anywhere across the whole workflow. You know, it should be in the variable pain and just use that variable name and you should be able to put that data in the Excel spreadsheet, right? And then the last one, which we're gonna talk about is select. range.

There it is. So, select range selects all the individual cells found within a range provided by the user. The selected cells can further be operated upon to perform some mass action on them, like delete, copy, etc, right? So many times you do that, you know, you want to select all the cells from within another worksheet from a one to C three, right. That's how the Select operation would work on. And then you can simply click Delete to delete all the data to show you how that is.

Okay, this simply and that's how you perform mass operations. So, you know, select range could be of those useful or in cases that you know, you may have I have been in those cases where we have selected this and perform some mass calculate some mass operation, then probably something from here to here. So you might need to add two or three times this select range activity and perform those computations. Okay. Next is data table activities. Data Table activities are used to perform computations on data tables like adding rows or clearing out data remove columns etc.

I show you where they are. There it is data table okay. Being a data structure in itself, the information is arranged in rows and columns and mindfully accessed and processed. Major editable activities are which we are going to discuss now. Here itself, output data table. So this activity doesn't display the contents of a data table, mind you, that doesn't happen rather, it converts the data table into a string making it easier to display right string variable can simply be displayed by putting it in a right line activity, just verifying the contents of a data table.

So that way, you know, you don't have to go through the whole for each row loop and something you just output the data table like this. And you can quickly check whether you know the data is correct or not. Okay, next is build a data table. So Bill data table is one of those activities where you get the option to create the data table from scratch. In here, we mentioned the header row labels, data types, and like for the values it's going to hold and the data elements as well. Let's take a quick example.

So it's called like column one and column two of stringer and teacher. I'm simply going to delete those. And let's say the first column is going to be name. It's going to be a string type. No default value, maximum length is gonna be 20 or something. Right?

And then I add a column, which is gonna be class, abstract string again, this time, let's say 10. Then enrollment number, right, this one, let's say I want it to be unique for all all students, and it could have been of integer type as well. So, something like that. And, you know, these are the things if it is maximum length is minus one, that means, like, you can have as many length of characters as you want, there's no limit to it. So something like this and you can put data as well, like, wherever class 10 enrollment number 123456 or You know, Shreya, glass 12 and 654321, something like that. Right?

You can press OK. And if you want, you can also output this data table to see how it looks like. You can first you need to store this data table in a variable. So we take it as DT one, let's say, right and this output data table in the input, we need to provide the DD one and text could be dT dt one underscores string or something. Right and he's in the right place. activity, if you print, you run and I'm checking this, there it is, all the data along with the headers are shown. Right?

Okay, so we have got to know how to build a table. So this is mostly used when you know, let's say once once when you're dealing with the data which for which the structure you already know, right, so you can probably not have the headers instead, you can just have the data and the you can build the data table structurally and just get the data out of an Excel sheet and use it for further computations or stuff. Okay, next, one of the very major activities which we use is for each row, right, we have actually gone through one example before, but for each row is gonna be used to traverse through a data table. It's reading one row at a time, once you go through it, it has a for each row, which is the counter variable is what we call it in whichever the data table is.

And you can simply do any computations based on each row. So the number of iterations bit depends on the number of rows. Mind you, some people get confused that that it's, it could be probably because of the, like the number of iterations is going to be based on the number of columns. That's not the case. It's the number of rows, right, left to right, horizontal. Okay, so next activity we're going to talk about is get your item right.

Another very important activity. And in this, we provide the role which is AI trading through the you know, data table and we provide the column index column name or the column object. Usually the column name should suffice, right? Which is gonna be name. In this case, let's say because we have taken some student data or something, right? And we can store the data in a variable called name.

Me. I have named already in here. So no problem. I can still use that variable. It's just that I cannot create it because it's already been created. Okay, so once I got that name, right, I can simply print it and see if I'm able to retrieve the name column correctly or not, which is gonna be in here.

And if I run this the first name is Robert. The second name is Shrek. And that's it. So it works, right. That's how we retrieve the data. elements from the rows that are trading through the data.

So the last activity that we're going to talk about is Add Data row, which is our Add Data column, which are this, which are gonna like like this. Structurally, they are both the same activities. One is going to add a data column, the other one is going to add a row. And what it does is, say, you know, there are two ways we can add the values to data elements within a data row of a data table. What we can do is either we provide once we're trading through the data table, row by row, we can provide that row a counter variable as an input to the Add Data row activity, along with the data table name, or we can provide an array row by row we mean that you know, we'll be providing the values of data elements of that row in a in the form of an array.

So currently, the table that we have, right, it's in this structure where it has named class and enrollment numbers, the headers. So let's say we add name as given, right? class has three eyes or three, and then says enrollment number is 111. Right? And the data table is let's see data table one, right? And we connected.

There it is. And we simply run this program. And upon execution, we can see in the output panel that Kevin has also been added, right? Similarly, we can add a column as well and we can provide the values for all the rows that have been added. for that particular column, we can provide the values within that activity, and those values get added to the table itself. That's how the operations work upon.

Right. There are a few more but they should be pretty explanatory, these are gonna cover all the spreadsheet automations. And we should be able to, you know, work with the data tables now, and on the spreadsheet, Excel applications as well. So, now let's take a sample use case, right? Let's delete all these activities right here. And consider that we have a student dot XLS x file with some data for their recent let's say math result right maths exam result.

So here it is sheet one. I'm going to delete this table or something. Named class roll number and marks, marks 70. Same class, let's take same class it doesn't matter actually. Are we taking any random data in here? Any last we'll take one more just for people.

Right? Let's take 115 and with mustard, nine Oh, okay. So we have this data, right and we're asked to create a workflow to find out how many people are Securing more than sorry, I haven't seen a video. So to see how many people actually scored more than 80% marks, right, something like that. So students haven't got it on the rest of you. Okay.

So, which is gonna be our, let's say probably who scored more than 90% marks? Right? So what we're gonna do is just a quick sample case, right? And depends, you want to just put it on the output panel itself, that works. So take the Excel application scope, right? Always name your activities as good as possible to those so students data computations right in here.

Provide the value of the directory level information right? Which is gonna be like there this mash students dot XLS x right here. Okay. And in here, you first want to read the range. That means reading all the data in sheet one, right? if let's say, let me rename this to students student data, right?

Let's say that's how it is and I saved it as well in here as well. I'll have to write that down student data. Yep, it's good. It's all the data is stored. And we still have those variables If you want, we can use them or we can create new, right? I use a DD one but You would rather Actually, let's do this student underscore dt, because the naming convention is really, really important can make your program very comprehensible to other people.

So we have the student data through this free range activity, we got all the data right. Now, what we're going to do is because we don't want or if we want, we can simply close this right, we don't need we have got the data in the data table. That means the data is now available to be read to be operated upon within the workflow itself. Right. So now, it's going to close the application because the student this Excel application scope is done. So the application would also be closed.

Then let's use for each row, which is going to iterate through each row of student data table. Okay, can you see what could be the issue? Let me tell you. There is no student data table available right now. Why? Scope variable scope that is correct.

So now in here, if you see student data is here scope is just do which is like the sequence. So you want to put it to the whole spreadsheet automation or there would be like multiple sequences. Of course, we could have got that student data, you're actually reading through it right. And we can use Metro item where the data table row is the row itself. And the column name is going to be marks, is it? Yes.

Another thing to remember is that you should now go and check it. I should have mentioned it before. Once we reading the data, did we check for errors? Yes, that means the data table that will be generated with will also have headers, and that way we'll be able to use column names Do you know do any computations further? In here, right, we're getting the data based on the marks column name, right? And we can store it in a value a new variable called marks.

Right? And within that, you know, scope of the sequence this body sequence itself. We can use an if condition. If marks is greater than 80, or 90%, right? 90 means it's not going to include 90 as well, it's going to include more than 90 starting from 91, all the way up to hundred. Right, then, let's say what could we print right?

We print just the name of the person or we could have printed the whole you know, anything we want. So what we can do is there are two ways, we can either get the row item from like the name here itself, and it is going to be a good activity. I'll, I'll tell you why. So see, we could have got this get row item in here as well with the name data, or we can get it in this as in the, like when the condition is true, right? It's better to get it in here. Because see, in this case, strictly from this case, I'm telling you, right, we have like four data rows in our table, right, which is gonna be these four rows.

And not everybody has code more than 90% marks, that means two of the times, this whole sequence would not even be executed or like our, you know, workflow would not even come to this place. For the first two, in this case for the first two rows, so if we were getting the row item from, you know, for the name as well up above, that's always going to be executed. And we want if you don't need to execute an activity, it's better to not execute it just for efficiency purposes. I know in here, it won't make much of a difference. But in large, complex projects, optimization actually plays a huge role. Getting one or two activities also could make a difference because right now, you're interacting with those applications like Excel or you know, sa p or Oracle which are very, like, you know, compatibles very mature applications.

But there is a possibility you might be using those applications, which are like homegrown by a company itself, right where the UI elements would be, I don't know we are looking and the compatibility should be huge in terms of interacting with the robot. So you want to be minimize your effort and make your bot as optimal in terms of its throughput and efficiency. So that's how you should be implementing or putting the activities. Right. So we got the row item, this is gonna be row again, and we'll take name this time. And we can put it in name, control, okay, name, and we can print the name.

Right, else keep continuing, there's nothing that needs to be added here, that only those student names would be printed, which, you know, have more than 90% marks. Okay. And, you know, there's a lot of other stuff could have been done like, we can probably print the whole row into another data table, or could be directly putting it to an Excel sheet. Although I creating it one by one, like the data table, and then putting into Excel is not a good idea, you would rather want to save it in, like internally within the bot in a in another data table first, and then write the whole data table to an Excel because that would be that would again give you a faster throughput. Right? So once you run this activity, and there is a possibility that you know, you may want to close this, it's automatically going to open that file and read the data and open the file, close the file automatically read the data, everything is done in like in a matter of few seconds.

And Smith and vevo are the only two people who got more than 90% marks. Right? That's a very fairly easy example. You can play around with this. The activities are fairly straightforward for Excel and plays a huge role in automation because Excel is used very, very extensively in almost every enterprise application. All right.