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..
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.
Posts Related:
- Incluir NOVOS Usuários - Planilha Cadastro de Membros e Controle de DÃzimos.
- Best Infographic PowerPoint Slide Design Tutorial Thin Line Infographic Scheme With 6 Options Office
- How to Create an Employee ID Card Template Using Microsoft Word Word
These rows that have "done" tasks are going to be removed from here and pasted on the bottom here. - ID Card Make
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.
