Excel Office Script修改：复制行后保留前两列删除其余内容

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 indices
    let rowsToMoveValues: (number | string | boolean)[][] = [];
    let rowsToClearIndices: number[] = [];

    // 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]);
            // 记录需要清空内容的行索引
            rowsToClearIndices.push(i);
        }
    }

    // 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)

    // 清空源表中符合条件行的除前两列外的内容
    const sheet = sourceTable.getWorksheet();
    console.log(`Clearing content from ${rowsToClearIndices.length} rows (excluding first two columns) in the source table.`);
    
    // 无需反转行顺序，因为清空内容不会改变行的位置
    rowsToClearIndices.forEach((rowIndex) => {
        // 获取当前行的范围
        const rowRange = sourceRange.getRow(rowIndex);
        // 计算需要清空的起始列（第3列，索引为2）和列数
        const startColumn = 2;
        const columnsToClear = rowRange.getColumnCount() - startColumn;
        // 获取需要清空的范围
        const clearRange = rowRange.getOffsetRange(0, startColumn, 1, columnsToClear);
        // 清除单元格内容
        clearRange.clear(ExcelScript.ClearApplyTo.contents);
    });

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

关键修改说明 #

• 替换行地址收集为行索引收集：不再存储整行的地址，而是记录符合条件的行在源表数据中的索引，方便后续精准定位需要清空的列范围。
• 移除整行删除逻辑：删掉了原脚本中删除整行的代码块，替换为清空指定列内容的逻辑。
• 精准定位清空范围：对每个目标行，获取从第3列（索引2，前两列为0和1）到该行最后一列的范围，仅清除这些单元格的内容，保留前两列的数据。
• 无需反转行顺序：清空内容不会改变行的位置，因此不需要像删除行那样从下往上操作，直接按顺序处理即可。

内容的提问来源于stack exchange，提问作者Eti