Automatically Move Completed Tasks to an Archive (with Excel Office Scripts & Power Automate)

Subscribed from india

Can you make Excel shortcuts on coffee mugs to sell? So I can buy some.

I tried changing the office script as per my requirements. I have two criteria and two target columns to move the rows of data. Column 5 "Done" is to be moved table "Archives" and same column "Waiting" to be moved Table "Archives2" which I added newly. For both case Column1 Value not Equal to "0" So totally Column5 and Column1 to be match the criteria then moved data to be respective tables. I used ChatGPT but it moves both Criteria to Archives2 Table. Can anyone Change the Office Script?

function main(workbook: ExcelScript.Workbook) {
// You can change these names to match the data in your workbook.
const SOURCE_TABLE_NAME = "Tasks";

// Get the Table object.
let sourceTable = workbook.getTable(SOURCE_TABLE_NAME);

// If the source table is missing, report that information and stop the script.
if (!sourceTable) {
console.log(
`Source table missing - Check to make sure the source table (${SOURCE_TABLE_NAME}) is present before running the script.`
);
return;
}

// Save the filter criteria currently on the source table.
const originalTableFilters = {};
// For each table column, collect the filter criteria on that column.
sourceTable.getColumns().forEach((column) => {
let originalColumnFilter = column.getFilter().getCriteria();
if (originalColumnFilter) {
originalTableFilters[column.getName()] = originalColumnFilter;
}
});

// Get all the data from the table.
const sourceRange = sourceTable.getRangeBetweenHeaderAndTotal();
const dataRows: (
| number
| string
| boolean
)[][] = sourceTable.getRangeBetweenHeaderAndTotal().getValues();

// Create variables to hold the rows to be moved and their addresses.
let rowsToMoveValues: (number | string | boolean)[][] = [];
let rowAddressToRemove: string[] = [];

// Get the data values from the source table.
for (let i = 0; i < dataRows.length; i++) {
const column5Value = dataRows[i][5]; // Column 5 value
const column1Value = dataRows[i][1]; // Column 1 value

if (
((column5Value === "Done" && column1Value !== 0) || // Check Column 5 value for "Done"
(column5Value === "Waiting" && column1Value !== 0)) && // Check Column 5 value for "Waiting"
column1Value !== 0 // Check Column 1 value
) {
rowsToMoveValues.push(dataRows[i]);

// Get the intersection between table address and the entire row where we found the match. This provides the address of the range to remove.
let address = sourceRange
.getIntersection(sourceRange.getCell(i, 0).getEntireRow())
.getAddress();
rowAddressToRemove.push(address);
}
}

// If there are no data rows to process, end the script.
if (rowsToMoveValues.length < 1) {
console.log(
"No rows selected from the source table match the filter criteria."
);
return;
}

let targetTableName = "Archives"; // Default target table name

// Check if Column 5 value is "Waiting" to set the target table name
if (rowsToMoveValues.some(row => row[5] === "Waiting")) {
targetTableName = "Archives2";
}

console.log(`Adding ${rowsToMoveValues.length} rows to target table "${targetTableName}".`);

// Get the Table object for the target table.
let targetTable = workbook.getTable(targetTableName);

// If the target table is missing, report that information and stop the script.
if (!targetTable) {
console.log(`Target table "${targetTableName}" is missing.`);
return;
}

// Insert rows at the end of target table.
targetTable.addRows(-1, rowsToMoveValues);

// Remove the rows from the source table.
const sheet = sourceTable.getWorksheet();

// Remove all filters before removing rows.
sourceTable.getAutoFilter().clearCriteria();

// Important: Remove the rows starting at the bottom of the table.
// Otherwise, the lower rows change position before they are deleted.
console.log(
`Removing ${rowAddressToRemove.length} rows from the source table.`
);
rowAddressToRemove.reverse().forEach((address) => {
sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);
});

// Reapply the original filters.
Object.keys(originalTableFilters).forEach((columnName) => {
sourceTable
.getColumnByName(columnName)
.getFilter()
.apply(originalTableFilters[columnName]);
});
}

Im putting Leila's videos into my ASMR playlist because of the calming voice 😴

HI, your videos are awesome, I have tried to refresh connection through automate but while run code nothing happened could you guide me

Awesome

Hi Leila,

When are you going to create a course related to Office Scripts?

I haven't got much hope for Automate if you have to rewrite 50% of the code.

Everyone making video on How to use office scripts, but not found a single video on "How to enable automate tab in excel"?

Great Video Leila. As an exercise I changed it to be a To-Do-List for me.
I also added a sheet that has repetitive tasks that are automatically added to task list.
Each item on this list has a date for when the tasks are to be added.
I am in Australia, so I use the Australian date format dd/mm/yyyy
The script works fine when I run it but when it is scheduled and run from a 'flow' the dates are interpreted as mm/dd/yyyy it adds tasks based on that date format.
I've changed every date setup/option on OneDrive and Sharepoint that I can find, and it masks no difference.
Does anyone have any suggestions?

Hi Leila, how to add the Atomate option to top row ribbon on Excel? Can’t find it in desktop nor web version of Excel for Win10. MS search also has not helped on it. Thx for info about it.

Cool tip, but i just hide the done tasks using a filter!

your presentation is beautiful and clear, thank you!

HI Leila, it looks like MS have added a script into excel as an example of this. "Move Rows Between Tables"

/*
This script does the following:

Selects rows from the source table where the value in a column is equal to some value (FILTER_VALUE in the script).
Moves all selected rows into the target table in another worksheet.
Reapplies the relevant filters to the source table.
*/

function main(workbook: ExcelScript.Workbook) {
// You can change these names to match the data in your workbook.
const TARGET_TABLE_NAME = "Archive";
const SOURCE_TABLE_NAME = "Current";

// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 3;
const FILTER_VALUE = "Done";

// Get the Table objects.
let targetTable = workbook.getTable(TARGET_TABLE_NAME);
let sourceTable = workbook.getTable(SOURCE_TABLE_NAME);

// If either table is missing, report that information and stop the script.
if (!targetTable || !sourceTable) {
console.log(
`Tables missing - Check to make sure both source (${TARGET_TABLE_NAME}) and target table (${SOURCE_TABLE_NAME}) are present before running the script. `
);
return;
}

// Save the filter criteria currently on the source table.
const originalTableFilters = {};
// For each table column, collect the filter criteria on that column.
sourceTable.getColumns().forEach((column) => {
let originalColumnFilter = column.getFilter().getCriteria();
if (originalColumnFilter) {
originalTableFilters[column.getName()] = originalColumnFilter;
}
});

// Get all the data from the table.
const sourceRange = sourceTable.getRangeBetweenHeaderAndTotal();
const dataRows: (
| number
| string
| boolean
)[][] = sourceTable.getRangeBetweenHeaderAndTotal().getValues();

// Create variables to hold the rows to be moved and their addresses.
let rowsToMoveValues: (number | string | boolean)[][] = [];
let rowAddressToRemove: string[] = [];

// Get the data values from the source table.
for (let i = 0; i < dataRows.length; i++) {
if (dataRows[i][FILTER_COLUMN_INDEX] === FILTER_VALUE) {
rowsToMoveValues.push(dataRows[i]);

// Get the intersection between table address and the entire row where we found the match. This provides the address of the range to remove.
let address = sourceRange
.getIntersection(sourceRange.getCell(i, 0).getEntireRow())
.getAddress();
rowAddressToRemove.push(address);
}
}

// If there are no data rows to process, end the script.
if (rowsToMoveValues.length < 1) {
console.log(
"No rows selected from the source table match the filter criteria."
);
return;
}

console.log(`Adding ${rowsToMoveValues.length} rows to target table.`);

// Insert rows at the end of target table.
targetTable.addRows(-1, rowsToMoveValues);

// Remove the rows from the source table.
const sheet = sourceTable.getWorksheet();

// Remove all filters before removing rows.
sourceTable.getAutoFilter().clearCriteria();

// Important: Remove the rows starting at the bottom of the table.
// Otherwise, the lower rows change position before they are deleted.
console.log(
`Removing ${rowAddressToRemove.length} rows from the source table.`
);
rowAddressToRemove.reverse().forEach((address) => {
sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);
});

// Reapply the original filters.
Object.keys(originalTableFilters).forEach((columnName) => {
sourceTable
.getColumnByName(columnName)
.getFilter()
.apply(originalTableFilters[columnName]);
});
}

Leila, at the end you added some code so that if there is no DONE the script will still work. However, not having DONE makes the flow fails in Power Automate, any clue to fix this ?

huhu i wanna learn all this but where to start? help

There are 2 videos I would love if you would make (if you haven't already). 1) Show us things you can do in Office Scripts that you cannot do in Excel 2) Show us how to integrate office scripts with Excel if possible. I am very experienced with VBA already but I haven't touched office scripts yet.

This is altogether new school of learning and I got automate tab but no record button on desktop version. All are afraid of automation

..

The office 365 family have that feature?

Hi Leila, please do a course for Office Scripts!!! regards!

can i total every page when printting
please help in excel

Record button missing

Since there is a loop at the end I would remove the filtering and just do it all in the loop.
That way you make the code more efficient and you wouldn't need to worry about the error

I love you leila ❤

Should advise the client to use Microsoft planner.

Plz 🙏forgive for all offenses 🙏 lela

Hi Leela did y tech class privately

Yeah, brilliant, so you need to be a coder to create a task manager, bravo Microsoft, we are all programmers 😖 Or you can just keep all records at the same table and filter rows on all except with a done status

you are so different Leila, thanks for capacitating us dear, apricated love to u

Thank you, I can relate this more with Javascript syntax finally.

Hi Leila! Does this replace VBA?

Leila, you are simply, "the bomb" you have helped endless people. Thank you.

Great video Leila, and nice work figuring this out! So, it looks like people who want to automate tasks and do not know VBA end up automating them using...hmm...well.. something that looks like VBA!! Now, I am curious to see how some more complex tasks can be written in both Automate and VBA and check which of the two codes is more efficient. Does Microsoft have some hidden agenda to eliminate VBA at some point? Ouch! that would hurt... From my side, I will continue to play in my VBA sandbox for the foreseeable future :) Thank you again for this great content.

I’d so love to know what the ‘make it rock’ script does 😂

Number one Leila!

Sorry but there is no automate tab in the excel desktop... it's only for office 365

Difficult for me

It is the most intricated thing I have seen

Thank you leila so muchhhhhhhhhh!!! My company do flayers for party as an invitation. we usually send by emails. is there any way that we can do it online where they can view the information. will you please, do tutorial on Microsoft Sway on Microsoft 360 on how to do a flyer. Thanks in advance!!!

Leila, The explanation was very clear, like the new method and will try this once..
I liked the video editing as well, May I know what tool do u use to edit videos?

Great video once again! Does Office Spripts have interactive debug tools like VBA with things like the immediate window, watch, local variables and breakpoints?

Was this not already in the macro

Is it possible to do it without vba or script?

Super cool trick, I have some queries. For example if my formula is A2+Sheet2!A1+Sheet3!B5, CTRL +[ will take me to A2 cell but then how do i move to Sheet2!A1 and Sheet3!B5?

Hi Leila, Thanks For wonderful Video, Is it possible to use other workbooks in office script?