Formula in TableSheet and Table filter

Formula in TableSheet and Table filter

Posted by: Jean.s on 12 June 2026, 9:12 am EST

• 

  Jean.s

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 12 June 2026, 9:12 am EST

  Hi,
  
  I’d like to set up a filter in a table so that it’s based on a formula. When I change the values in the cells that the formula depends on, the filter should update and narrow down the results.
  
  I’d like to do the same thing for the filters in a TableSheet.
  
  How can I do this?
  
  Thank you very much.

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 15 June 2026, 6:06 am EST

  Hi,

  We are not entirely sure we understand your exact use case. Could you please elaborate further and provide some examples of what you are trying to achieve? This will help us better understand your requirements and assist you more effectively.

  Regards,
  
  Priyam

• 

  Jean.s

  • Post Options:
  • Link
    Copy

  Posted 15 June 2026, 6:33 am EST

  Hi,
  
  It would be very helpful to be able to control the filters for a table and a tablesheet from other cells on the sheet. In the examples on table slicers, for example this one https://developer.mescius.com/spreadjs/demos/features/tables/table-slicer/table-slicer-data/purejs
  
  you can see how to link a table’s filters to checkboxes in an HTML div outside the sheet. It would be useful to be able to do the same thing with, for example, cells on the sheet formatted as checkboxes.
  
  I hope this clarifies things.
  
  Best regards

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 16 June 2026, 3:26 am EST

  Hi,

  You can achieve this using checkbox cell types on the sheet combined with the ValueChanged event to drive the table’s row filter. Here’s how the key pieces fit together.

  The filter panel

  Set a CheckBox cell type on a column of cells and give each one a label in the adjacent column. All boxes start as true (checked = show all).

  const cbType = new GC.Spread.Sheets.CellTypes.CheckBox();
  
  function addCheckboxRow(row, label) {
      sheet.setValue(row, LABEL_COL, label);
      sheet.setCellType(row, CHECK_COL, cbType);
      sheet.setValue(row, CHECK_COL, true);
  }

  Building the filter conditions

  Use an array of Condition objects — SpreadJS automatically ORs them within the same column:

  const { Condition, ConditionType, TextCompareType } =
      GC.Spread.Sheets.ConditionalFormatting;
  
  function buildConditions(options, rows) {
      const checked = options.filter((_, i) => sheet.getValue(rows[i], CHECK_COL));
      // null = "show all" (all checked or none checked)
      if (checked.length === 0 || checked.length === options.length) return null;
      return checked.map(v =>
          new Condition(ConditionType.textCondition, {
              compareType: TextCompareType.equalsTo,
              expected: v
          })
      );
  }

  Applying the filters

  Call table.rowFilter() and use the documented addFilterItem / filter / removeFilterItems / unfilter methods:

  function applyFilters() {
      const rowFilter = table.rowFilter();
  
      // Sex column
      rowFilter.removeFilterItems(SEX_COL);
      const sexConditions = buildConditions(SEX_OPTIONS, SEX_ROWS);
      if (sexConditions) {
          rowFilter.addFilterItem(SEX_COL, sexConditions);
          rowFilter.filter(SEX_COL);
      } else {
          rowFilter.unfilter(SEX_COL);
      }
  
      // City column
      rowFilter.removeFilterItems(CITY_COL);
      const cityConditions = buildConditions(CITY_OPTIONS, CITY_ROWS);
      if (cityConditions) {
          rowFilter.addFilterItem(CITY_COL, cityConditions);
          rowFilter.filter(CITY_COL);
      } else {
          rowFilter.unfilter(CITY_COL);
      }
  }

  Filtering across multiple columns compounds as AND automatically — so Sex and City selections work together as expected.

  Reacting to checkbox changes

  const ALL_CONTROL_ROWS = [...SEX_ROWS, ...CITY_ROWS];
  
  sheet.bind(GC.Spread.Sheets.Events.ValueChanged, (e, args) => {
      if (args.col === CHECK_COL && ALL_CONTROL_ROWS.includes(args.row)) {
          applyFilters();
      }
  });

  Every time a checkbox is toggled, applyFilters runs and both column filters are re-evaluated from scratch.

  Refer to the attached sample: https://jscodemine.mescius.io/share/1sr8mxKvkEumD6cUCVh3iQ/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Regarding TableSheet

  TableSheet uses a different filter architecture than a regular sheet table and does not support such use case.

  Regards,
  
  Priyam