I have created a script that will move an entire row to another tab on the worksheet if certain text is entered into a selected cell.
I want to be able to do this if the cell is not empty rather than having certain text and I would like the row to be deleted all except the first column.
The script is below and works really well, I'm not great at coding and managed to cobble this together from some other scripts i found but i now can't manage to edit it to fit this new task.
I tried using Javascript not equals signs and other symbols and can remove rows that are empty but i can't seem to make it work.
function main(workbook: ExcelScript.Workbook) {
// You can change these names to match the data in your workbook.
const TARGET_TABLE_NAME = 'TableNAdded';
const SOURCE_TABLE_NAME = 'TableN';
// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 27;
const FILTER_VALUE = 'Y';
// 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]);
});
}
