Office Scripts过滤问题：如何获取表格可见行的第一列数据

问题描述 #

我正在编写一个Office Scripts脚本，目标是筛选数据并找到第一列包含AB S.A.、Ingram Micro spółka z o.o.、ALSO Polska Sp. z o.o.这三个字符串的最新日期，计划通过逐个日期应用过滤并检查的方式实现。目前代码其余部分运行正常，但最后一段获取可见行第一列数据的逻辑有问题：打印firstColumnValues时显示的是整个表格的所有行数据，而非仅当前可见行。

原代码如下：

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
const INV = workbook.getWorksheet("INV");


// Adding function formatDate which is converting date to format: DD/MM/YYYY
function formatDate(date: Date) {
    var year: string = date.getFullYear().toString();
    var month: string = (date.getMonth() + 101).toString().substring(1);
    var day: string = (date.getDate() + 100).toString().substring(1);
    return day + "/" + month + "/" + year;
}


// Deleting unnecessary columns
// Delete range A:C on selectedSheet
selectedSheet.getRange("A:C").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range B:C on selectedSheet
selectedSheet.getRange("B:C").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range D:E on selectedSheet
selectedSheet.getRange("D:E").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range E:H on selectedSheet
selectedSheet.getRange("E:H").delete(ExcelScript.DeleteShiftDirection.left);

// Create a new temporary sheet view
selectedSheet.enterTemporaryNamedSheetView();
// Clear auto filter on selectedSheet
selectedSheet.getAutoFilter().clearCriteria();
// Toggle auto filter on selectedSheet
selectedSheet.getAutoFilter().remove();


// 
const usedRange = INV.getUsedRange();
const lastRowRange = usedRange.getLastRow();
const lastRow = lastRowRange.getRowIndex();
const tableRange = INV.getRange("A1:C" + lastRow.toString());
let newTable = INV.addTable(tableRange, true);



// Creating an array, that contains current date and last 6 days

let daysArray: Date[] = [];
let now = new Date();
for (let i = 0; i < 6; i++) {
    daysArray.push(new Date(now.setDate(now.getDate() - 1)));
}
daysArray.unshift(new Date());

let formattedDaysArray = daysArray.map((date) => formatDate(date));


// Create an array that contains unique values from "Calendar day" column
const keyDateColumnValues: string[] = newTable.getColumnByName("Calendar                 day").getRangeBetweenHeaderAndTotal().getValues().map(v => v[0] as string);
const uniqueDates = keyDateColumnValues.filter((v, i, a) => a.indexOf(v) === i);

// Create an array that contains unique values from "Distributor Name" column
const keyColumnValues: string[] = newTable.getColumnByName("Calendar day").getRangeBetweenHeaderAndTotal().getValues().map(v => v[0] as string);
const uniqueDist = keyColumnValues.filter((v, i, a) => a.indexOf(v) === i);


// Convering array of strngs: uniqueDates to array of dates
let dates: Date[] = [];
for (let i = 0; i < uniqueDates.length; i++) {
  //let nowaData = new Date(uniqueDates[i]);

var dateParts = uniqueDates[i].split("/");

// month is 0-based, that's why we need dataParts[1] - 1
var nowaData = new Date(+parseInt(dateParts[2]), parseInt(dateParts[1]) - 1, +parseInt(dateParts[0])); 

  dates.push(nowaData);
}

// Sorting an array 
dates.sort((a, b) => a.getTime() - b.getTime());

// Convering to date format: dd/mm/yyyy
let formattedDatesArray = dates.map((date) => formatDate(date));
console.log(formattedDatesArray);

console.log(formattedDatesArray[0]);

// Apply custom filter on table tabela1 column "Distributor Name"
newTable.getColumnByName("Distributor Name").getFilter().applyCustomFilter("=Exclusive*");

// Remove filtered range
let visibleRows = newTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows();
let firstVisibleRow = visibleRows[0].getRange().getRowIndex() + 1;
let lastVisibleRow = visibleRows[visibleRows.length - 1].getRange().getRowIndex() + 1;

                INV.getRange(`${firstVisibleRow}:${lastVisibleRow}`).delete(ExcelScript.DeleteShiftDirection.up);

newTable.getColumnByName("Distributor Name").getFilter().clear();

newTable.getColumnByName("Calendar day").getFilter().applyValuesFilter([formattedDatesArray[0]]);



let visibleRows2 = newTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows();
let firstVisibleRow2 = visibleRows2[0].getRange().getRowIndex() + 1;
let lastVisibleRow2 = visibleRows2[visibleRows2.length - 1].getRange().getRowIndex() + 1;

let firstColumn = INV.getRange('A'+ firstVisibleRow2 + ':'+ 'A' + lastVisibleRow2);
let firstColumnValues = firstColumn.getValues();
//console.log(firstColumnValues);

const containsXYZ = firstColumnValues.some(str => str.includes('AB S.A.')&&('Ingram Micro spółka  z o.o.')&&('ALSO Polska Sp. z o.o.'));
console.log(containsXYZ);
}

解决方案 #

问题根源在于直接使用工作表的getRange方法获取范围时，该方法会忽略过滤状态，返回指定行号的完整范围，不管行是否被隐藏。正确的做法是从可见行集合visibleRows2中逐个提取第一列的单元格值。

同时，原代码中检查经销商的逻辑存在错误：使用&&会要求单个单元格同时包含三个经销商名称，这显然不符合需求，应该用||判断单元格是否包含任意一个目标经销商。

修改后的代码片段 #

替换原代码中最后一段获取firstColumnValues和判断的逻辑：

let visibleRows2 = newTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows();

// 收集可见行的第一列数据
let firstColumnValues: string[] = [];
for (let row of visibleRows2) {
  // 获取当前可见行的第一列（A列，索引为0）单元格值
  const cellValue = row.getRange().getColumn(0).getValue() as string;
  firstColumnValues.push(cellValue);
}

// 检查是否存在任意一个目标经销商
const containsTargetDistributors = firstColumnValues.some(str => 
  str.includes('AB S.A.') || str.includes('Ingram Micro spółka z o.o.') || str.includes('ALSO Polska Sp. z o.o.')
);
console.log(containsTargetDistributors);

说明 #

• 通过遍历visibleRows2（过滤后的可见行集合），每一行调用getColumn(0)获取第一列，再通过getValue()提取单元格内容，确保只获取可见行的数据。
• 将&&替换为||，实现"只要存在任意一个目标经销商即返回true"的逻辑，符合你的需求。

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