I am new to office-script and I have tried to work on this issue with ChatGPT, but this was not helpful at all. :-(
I am trying to copy rows from one sheet to another with the code below, but this code does not include the formulas. I have no idea how to implement this into the code.
Within the next step I would need the same logic to move rows to a new sheet (including formulas).
A row can contain just data and formulas, some cells are empty at this stage. I had a problem with the format of the rows when I started. It always took over the format of the table header when the target table was empty. But that seems to be gone.
Some columns (different) are hidden in both sheets (target and source), but this was not a problem with the script below, it was only a problem with a solution from ChatGPT, which also did some strange things like insert the rows twice with overwriting existing rows. Strange AI! ;-)
Any help welcome!
here is the "copy code" so far:
/*
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 = "Start";
const SOURCE_TABLE_NAME = "BasicInfo";
// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 5;
const FILTER_VALUE = "to be processed";
// 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);
// Reapply the original filters.
Object.keys(originalTableFilters).forEach((columnName) => {
sourceTable
.getColumnByName(columnName)
.getFilter()
.apply(originalTableFilters[columnName]);
});
}
see above within my problem description
New code used to move rows to the next sheet and deleting the row in the source table (no rows will be moved and a filter is set at index 3 afer the script has run). I tried it with index 7 where just text is entered and no formula will generate the status and it worked, but with index 8 the script is not running as expected):
function main(workbook: ExcelScript.Workbook) {
const TARGET_TABLE_NAME = "Work";
const SOURCE_TABLE_NAME = "Start";
// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 8;
const FILTER_VALUE = "processed";
let desTab = workbook.getTable(TARGET_TABLE_NAME);
let srcTab = workbook.getTable(SOURCE_TABLE_NAME);
if (desTab && srcTab) {
// Clear auto filter on table srcTab
srcTab.getAutoFilter().clearCriteria();
// Apply checked items filter on table srcTab column Col5
srcTab.getColumnById(FILTER_COLUMN_INDEX).getFilter().applyValuesFilter([FILTER_VALUE]);
const filterRange = srcTab.getRangeBetweenHeaderAndTotal().getSpecialCells(ExcelScript.SpecialCellType.visible);
if (filterRange) {
let anchorCell = desTab.getColumnById(1).getRange().getLastCell()
if (anchorCell.getValue()) {
// move to next line if not-blank
anchorCell = anchorCell.getOffsetRange(1, 0)
}
anchorCell.copyFrom(filterRange);
// Update formula
desTab.getRangeBetweenHeaderAndTotal().replaceAll(`${SOURCE_TABLE_NAME}[`, "[", { completeMatch: false, matchCase: false });
// Get range address
const rangeList: string[] = filterRange.getAddress().split(",");
// Remove data rows from source table
for (let i = rangeList.length - 1; i > -1; i--) {
let areaRange = srcTab.getWorksheet().getRange(rangeList[i]);
areaRange.getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
}
srcTab.getAutoFilter().clearCriteria();
}
}
else {
console.log("No data in filtered source table")
}
}
function main(workbook: ExcelScript.Workbook) {
const TARGET_TABLE_NAME = "Start";
const SOURCE_TABLE_NAME = "BasicInfo";
// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 5;
const FILTER_VALUE = "to be processed";
let desTab = workbook.getTable(TARGET_TABLE_NAME);
let srcTab = workbook.getTable(SOURCE_TABLE_NAME);
if (desTab && srcTab) {
// Clear auto filter on table srcTab
srcTab.getAutoFilter().clearCriteria();
// Apply checked items filter on table srcTab column Col5
srcTab.getColumnById(FILTER_COLUMN_INDEX).getFilter().applyValuesFilter([FILTER_VALUE]);
const filterRange = srcTab.getRangeBetweenHeaderAndTotal().getSpecialCells(ExcelScript.SpecialCellType.visible);
if (filterRange) {
let anchorCell = desTab.getColumnById(1).getRange().getLastCell()
if (anchorCell.getValue()) {
// move to next line if not-blank
anchorCell = anchorCell.getOffsetRange(1, 0)
}
anchorCell.copyFrom(filterRange);
// Update formula
desTab.getRangeBetweenHeaderAndTotal().replaceAll(`${SOURCE_TABLE_NAME}[`, "[", { completeMatch: false, matchCase: false });
// Get range address
const rangeList: string[] = filterRange.getAddress().split(",");
// Remove data rows from source table
for (let i = rangeList.length - 1; i > -1; i--) {
let areaRange = srcTab.getWorksheet().getRange(rangeList[i]);
areaRange.getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
}
srcTab.getAutoFilter().clearCriteria();
}
}
else {
console.log("No data in filtered source table")
}
}