Filtering and Adding Values to Specific Rows in Excel Using Office Scripts

Question

I'm working with Microsoft Excel and trying to use Office Scripts to filter rows based on a specific year and months, and then add a value to a "Quarter 2" column for those filtered rows. However, I'm encountering issues with my current script.

Here's the script I've tried so far:

function main(workbook: ExcelScript.Workbook) {
  // Define the table name and column name you're interested in
  let tableName = "Table1";
  let columnName = "Date";

  // Get the table by name
  let table = workbook.getTable(tableName);

  // Get the column by name
  let column = table.getColumnByName(columnName);
  column.getFilter().applyDynamicFilter(ExcelScript.DynamicFilterCriteria.thisYear);

  // column.getFilter().applyValuesFilter(["SALESMAN SAMPLE"]);
  column.getFilter().applyDynamicFilter(ExcelScript.DynamicFilterCriteria.allDatesInPeriodMarch);

  
  // Get the target column by name
  let targetColumnName = "Quarter 2";
  let targetColumn = table.getColumnByName(targetColumnName);

  // Get the data you want to add (adjust this based on your data source)
  let newData = ["Y"];

  // Get the range of the target column
  let targetColumnRange = targetColumn.getRange();

  // Get the values from the target range
  let targetValues = targetColumnRange.getValues();

  // Get the values from the filter column
  let filterValues = column.getRange().getValues();

  // Loop through the values and update where necessary
  for (let i = 0; i < targetValues.length; i++) {
    // Check if the row meets the filter criteria
    if (filterValues[i][0] === true) {
      targetValues[i][0] = newData[0];
    }
  }

  // Set the modified values back to the target range
  targetColumnRange.setValues(targetValues);

  // Clear the filter
  // column.getFilter().clear();

}

Here's what I'm trying to achieve:

Filter rows based on a specific year (e.g., 2023) and specific months (e.g., March and April). Add a value (e.g. "Y") to the "Quarter 2" column for the filtered rows only, not for the entire column.

The script partially works, but only one month is filtered, and data is not inserted into the "Quarter 2" column.

I would appreciate any guidance on how to modify the script to achieve the desired filtering and value insertion for specific rows. Thank you!

Answer 1

Your code is nearly complete. Looping through all cells is slower. For efficiency, set values on the visible range post-filtering.

function main(workbook: ExcelScript.Workbook) {
    // Define the table name and column name you're interested in
    let tableName = "Table1";
    let columnName = "Date";

    // Get the table by name
    let table = workbook.getTable(tableName);

    // Get the column by name
    let column = table.getColumnByName(columnName);
    column.getFilter().applyDynamicFilter(ExcelScript.DynamicFilterCriteria.thisYear);

    // column.getFilter().applyValuesFilter(["SALESMAN SAMPLE"]);
    column.getFilter().applyDynamicFilter(ExcelScript.DynamicFilterCriteria.allDatesInPeriodMarch);

    // Get the target column by name
    let targetColumnName = "Quarter 2";
    let targetColumn = table.getColumnByName(targetColumnName);

    // Get the data you want to add (adjust this based on your data source)
    let newData = "Y";

    // Get the range of the target column
    let targetColumnRange = targetColumn.getRangeBetweenHeaderAndTotal().getSpecialCells(ExcelScript.SpecialCellType.visible);
    let targetAreas = targetColumnRange.getAreas();
    for(let i=0; i<targetAreas.length; i++){
        targetAreas[i].setValue(newData);
    }
}

Microsoft reference document:

getSpecialCells(cellType, cellValueType)

---

Updat

Question: I need to filter for both March and April and insert data accordingly.

    column.getFilter().applyValuesFilter([{ date: "2023-03", specificity: ExcelScript.FilterDatetimeSpecificity.month }, { date: "2023-04", specificity: ExcelScript.FilterDatetimeSpecificity.month }]

More flexible approach to filter multiple months

    let Yr = 2023;
    let startMth = 3;
    let endMth = 4;
    let listFilter = [] = [];
    for(let iMth=startMth; iMth<=endMth; iMth++){
        listFilter.push({ date: `${Yr}-${iMth.toString().padStart(2, '0')}`, specificity: ExcelScript.FilterDatetimeSpecificity.month });
    }
    column.getFilter().applyValuesFilter(listFilter);