Have you noticed the new Automate
tab in Excel for desktop? Now, I made a video before where I used it to bring
AI GPT-3 into Excel. If you haven't watched it, watch it after this video but not now, because what
we're going to do today is really cool too. We're going to test Record Actions in Office Scripts
and move rows from one table to another table based on a value in a cell. How far will Record
Actions actually take us? We're going to have to see. This was actually inspired by one of our
community members; I think it was on LinkedIn, and it reminded me of something I had to
do every single day as a project manager. So, there was one project I was working on that
was quite intense. We had to sit together and review all the tasks at the beginning of the day.
We had a 6L table, and all the tasks were listed there. My task was at the end of each day to
remove anything that was completed and add it as a separate row to the bottom of another
table, which was our archive tasks. This way, every day we start the meeting, and there
would be no "done" tasks on our active project list. It's a simple thing, right? But it was
super annoying. For one, I lacked Excel skills; I was doing every step manually, right? Cut,
paste, delete. And two, sometimes I'd forget to do it, and I'd be at dinner somewhere, enjoying
my meal with friends, and I'd be like, "Oh my God, I forgot to move those rows!" It was just an
annoying little thing that was constantly on my mind. If only I had the Automate tab at the
time, I'd never have to worry about it because the rows would be automatically removed during my
sleep. I'd walk into the meeting, open the file, and everything would be done without me
lifting a finger. Right, the finger-lifting though is something you need to do at the
very beginning to get this whole setup done. There are two players involved here: Office
Scripts and Power Automate. You're going to get them to work together, and you'll
have one less thing to worry about. Okay, so over here in Excel for desktop, I have
this simplified table with the different responsibilities, the priority of the task, the
task itself, and the status of the task. Now, this is officially an Excel table; we can
see in Table Design, the table name is Tasks. What I want to happen is that at the end of
the day, all tasks that have status "done" are going to be removed from this table, and
they're going to be pasted in a second table, which is sitting on my Archive sheet at the bottom
of this table. This table is called Archives. Now, I want to create a script that does
this, and I also want to outsource the script to Power Automate so that it's done
every single night at, let's say, 10 pm. My starting point is going to be right here, the
Automate tab. So, I'm in Excel for desktop for Office 365 Enterprise Edition. I already have
the Automate tab. If you don't have it yet, you'll have to use Excel on the web. Also, keep
in mind that Office Scripts are also controlled by your admin, so they could be turned
off for you and for your organization. To start writing your script, you could go to "New
Script". You're going to see the code editor pop up. On the site, you have the main function, and
you can type in your code here. But that's only if you know what you're doing. If you don't, because you don't, let's say, know how to reference a cell or how to reference a filter using Office Scripts, you could get started by
recording your actions. This way, at least you get some code that you can work with and tweak.
So let's do that. Let's go ahead and record our actions. We can see the recording is running on
the side. And we have to think this through to see how we could make this dynamic. I don't want to
go and specifically select these rows because the code editor is not going to know what's the logic
behind that. I want to tell it to only select "done" rows. So one way I could do this is to use
the filter options. I'm going to go to "Status" and only select "done." Click on "OK." We can see
the code editor is recording that action. Then I only want to copy the visible cells. So I'm going
to press "Ctrl+A" and then "Ctrl+C." Notice only the visible cells are being copied. This is the
same as if you go to "Go to Special" and select visible cells. And once we've copied this, we are
going to go to the "Archive" sheet, go to cell A1, and use the shortcut key "Ctrl + Down" to jump to
the bottom of this table. And then go one more cell down, and then I'm going to paste, Ctrl + V.
So now let's go back to "Task." I want to delete these rows. So I'm going to right-mouse click and
delete. Optimally, I want to delete the table rows, but I don't have an option here. So I'm going
to go to "Delete" and delete the entire sheet row. Okay, so these are gone. Now let's go
to "Status" and unfilter everything else. And we're back to our table. We've moved
the "done" tasks. Everything is being recorded. I can just see a description
of what's happening. I'm going to stop. Our code was generated. To view it,
we can go to "Edit." And that's it. The great thing about the code is that we also
get some comments that tell us what's happening at each stage. The question, though, is does it work?
So what do you think? Just by looking at this, do you think it's going to work? Well, let's try it
out. I'm just going to copy these last three rows, paste them in here, and let's change some of these
to "done." I'll just change this one to "done," and let's change this one to "done." Okay, so if
we've run this, what we want to happen is that these rows that have "done" tasks are going to be
removed from here and pasted on the bottom here. Okay, so let's run this and see what happens.
It ran. One of them was removed. The other one is still here. What happened in "Archive"? Well,
everything was copied. It wasn't just a "done" task, but it was everything. So, yeah, the code is
clearly not working. It was kind of working 50%, but not what we needed to do. So we need
to tweak this. Okay, so let's remove these rows here. Let's right-mouse click, delete,
delete these table rows. Okay, so our archive is back to what it was. Now, let's see what's
happening here and what we need to tweak. We're going to start from the top. What the code does is
that it has created a Task object, a Table object, and it's assigned it to workbook.getTable("Tasks").
This is my tasks table so we can see that's the tasks table. Then it got the Status column, and
then it's adding a filter for Done. Then it's assigned Archive to workbook.getWorksheet("Archive").
Okay, so far so good. The problem happened here. So after I did some investigation, I realized that
it's always copying everything between the header and total. It doesn't copy the visible range.
So we need to tweak that, and we want to copy the visible view. So, we want to get visible view,
and we have that method here. But unfortunately, it doesn't work with the copyFrom, so we need
to set this up in a different way, and the way I came up with is this. So first, we can define
our visible view. So we're going to define a variable for that. We're going to get visible
view, and you can call this whatever you want, and we're going to reference our Table
object, get range between header and total, and we want to get the visible view.
Okay, so that's the first step. The next step is we need to define our paste
areas, our paste cell, and then we need to expand the size of the range that we want to
paste into. This type of behavior is something we don't need in VBA, but it looks like we need
that in Office Scripts. So what I'm going to do is define paste cell to be this. Basically, we're
going to go to cell A1 in the Archive sheet. We're going to jump to the bottom and go one cell down.
So all of this part is actually what we need. So I'm just gonna grab this and paste it here.
We don't need this part. So now we're ready to paste our done tasks. But remember, we need to
specify the location of our page. So basically, the size of our paste area. So I'm going to
define that as well, "let paste_location," or you can call it "let paste_range," equals paste cell.
We want to resize this, so "getresizedrange." Now, whenever Intellisense is not popping up, it is
because we've typed something wrong. "paste_cell" is like this. So "getresizedrange," and now we can
define the range. The range is basically the size of our visible view. I call these long names,
"get_visible_view," but I want to get the row count. Okay, so now I know how many rows my visible view
has, but I have to deduct one because Office Script is zero-based. So this is the size of the
rows. Now we're at the column, so we need to go back to "get_visible_view." This time, we're going
to get the column count, and we're also going to minus one. Okay, so now we know where we're
pasting into and how big our paste range is. Now comes the actual paste thing. So
that's going to be "paste_location.setValues," and we're setting it to "get_visible_view.getvalues."
Okay, so now is the time to grab the values of our visible range. Now, if you're wondering where
you can find the syntax, you can find it from the Office Scripts API reference that you're going to
find in Microsoft Learn. If you scroll down here, you'll see the common classes. I'm in the
Table object here, you can find all the different methods that are associated with a
table, and this was the one that we saw before, "getRangeBetweenHeaderAndTotal()." This gets
the Range object that's associated with the data body of the table. "getRowCount()' is what I used
before. You'll find the definitions all in here, together with some examples. Now,
let's go back and finish our code. Now, the part I'm not happy with is this
part right here. I'm deleting specific rows. I don't want that. I also don't want
to delete the entire row because I might have something to the side of the table. So,
I'm going to remove that. This part is fine, we're clearing the filter. But right
here, I want to delete "done" tasks, and the way I want to do it is to loop through
the table from the bottom to the top. Whenever I come across "done" in the Status column,
I want to delete it. Because I'm deleting, it's better to loop from bottom to top
instead of looping from top to bottom. So, first of all, let's figure out how many
rows we're dealing with. I'm going to create a new variable called "tasks_rows". This is going
to equal "Tasks", so our table, and we're going to get the row count of the table. Next, we want to
grab the values that we have in this range. So, let's create "tasks_range", and this is going to
equal "Tasks". Now, we're going to get the Range Between Header and Total, and we're going to grab
the values of this range. Okay, so, so far so good. Now, let's start with our loop. We start with
the "for" statement. We are going to define "i" equals "tasks_rows", right? So, this is the
number of rows that we have in our table, and we're going to deduct one from
it because Office Script is zero-based. And we're going to loop through this as
long as "i" is greater than minus one, right? Because we're going from the bottom to
the top. So, as long as it's greater than -1, we're going to do this. And because we're going
the other way around, I'm going to do "i--". Now, we're going to open the curly bracket, and
inside here, we're going to do our check. So, "if (tasks_range)" and we have to
define the row and the column, the row is going to be "i", we have
to put this inside square brackets, the column is going to be "5". Why 5? Because the
Status is our fifth column. This is zero, right? 0, 1, 2, 3, 4, 5. Now, because we also
want to make sure that letter case doesn't matter, we're going to make sure this is a string, so
convert it to a string, and if we want to convert it to upper case. Okay, so once we have that,
we're going to check whether this equals "done". Now, we need to open the curly bracket here
as well for "if" statements, and if it is, we're going to do "tasks.deleteRowsAt". We
have the address, right? It's "i" and "1". Okay, so that's pretty much it. If at any point in
time you want to check something, you can write it to the console. So, for example, let's say
we want to know what is our row count here, we can do "console.log" and put in our "tasks _rows" here. So, it's a great way of debugging your script. So, I have no idea if this
is going to work, but it doesn't look like we have any major syntax issues. Let's just
test it out. So, our Archives now are clean. Only done tasks are there. We have two "done"
tasks here. Let's run this and see what we get. "Done" tasks were removed from here, and in here, we
just have our two "done" tasks that were copied to Archive. And down here, we can see what
we wrote to the console. So, it's at nine because we had nine rows at the time, right?
So, now it's seven because two were removed. Okay, so let's just comment this out. Well,
that's it basically. Our script is done. Next step is to outsource the running of the
script to Power Automate. Before we do that, let's give it a proper name and let's make
sure that the script is saved and it is. Okay, so now I want to set this up so that it
runs at 10 PM every single day. Where do I need to go to do that? Well, not far. I just have to
go back to the Automate tab and select Automate a task. This gives me Power Automate directly inside
Excel, and I just need to select this template: Schedule an Office Script to run in Excel. This
is going to open a tab on my browser. I can see the template here. If you aren't logged in, you
might need to log in now. And then, you just have to select Continue. And all you need to do here is
select what you want to have inside the boxes. So, how often do we want our script to run? Well,
I want it to run once every single day. If I wanted it for a month, week, I can change
my selection here. Under Advanced options, I can define the time zone. Mine is UTC plus
one. Then, at which hour or hours do I want this to run? I wanted to run at 10 PM, so I'm
going to select 22. In the preview, I can see that it runs at 10 PM every single day. Next up,
I need to define the location of my file. So, my file is saved on OneDrive for Business. If you
have yours saved on SharePoint, you can adjust your selection accordingly. Document library in this
case is OneDrive. The file itself is saved in this folder right here, and it's called Completed
Tasks. Next up, I need to select my script, which is called Archive Done Tasks. And that's
it. We just have to save. You can adjust the name of your flow if you want to, and then it's
good that you test it. We could, in this case, run a manual test. We can set this to manual and
run our test. But before we do that, let's just go back to our file and change some of these to done.
So, I'm going to change this one to done, and let's also change this one to done as well. Okay,
I'm just going to make sure that this is saved. Now, let's go back to our flow and let's trigger
this manually just to make sure that it works. We're going to click on Test and run the flow.
Okay, so it successfully started. We can monitor this if you want from here or we just go back to
our Excel file and see if it ran successfully. So, I'm going to go back to Excel, and we're
going to see that our "done" tasks were removed, and they were added to our archives. Now,
this is going to run at 10 PM every single night. Of course, you don't have to
have your file open. All you need to do is just make sure that it's saved on your
OneDrive or on your SharePoint. That's it. One thing to point out is that you can go back
to your code at any time and optimize it as you need. Well, what happens, for example, if
we don't have any "done" tasks here? What do I get if I run this? Well, it's going to
run with errors, and we don't want that. So, we're going to make this better by adding an
if statement so that it only runs if our visible row count is more than one. So, right here,
I could add an if statement and check whether the "get_visible_view.getRowCount()," if this
is greater than zero, and if it is, then I'm going to run the rest of my statements.
So, I'm going to do the copying and pasting here. So, I'm going to do this until here, and we are going to want to indent this.
Now, you can optimize this even more by also making sure that this part doesn't run. Just
for the purpose of this demonstration, this is now running without errors. So, that's our Office
Scripts and Power Automate duo. The recorder does help us to get started faster, and then we need
a bit of knowledge to tweak it. Power Automate takes care of the rest. Let me know what you think
about this. Comment below. I hope you enjoyed this video. Subscribe if you aren't subscribed
yet, and I'll catch you in the next video.
