Excel Online - Office Script to filter table for non-blank rows

Question

I have a table (Contact_List). I want to hide some columns (works well), then filter the Date column for all non-blank rows. Then I want to copy and paste results to another table, and unfilter the original table.

I recorded all the steps. The problem is with the filter. I can't seem to get the script to recognize I want non-blank rows, it instead always does a filter of including what's left, not excluding what I took out. I've seen a few posts that explain about criteria, but I just don't get the structure within the script I have. Can someone help me re-write the line(s) that do the filter?

The script seems to reference columns by letter, instead of the table names. And the copy range seem's suss, based on my VBA experience.

I saw a couple of other posts similar to this here, but the code they provided didn't work in my code. This one is the closest to my problem, but I have a table, and my filter is already applied. The code in this wouldn't work for me.

How do I filter out blank rows in Office Script

Here is my script: The problem child is the contact_list row

function main(workbook: ExcelScript.Workbook) { 
    
    let selectedSheet = workbook.getActiveWorksheet(); 

    //Set visibility of column(s) at range B:K on selectedSheet to true 
    selectedSheet.getRange(**"B:K")**.setColumnHidden(true); 

    let contact\_List = workbook.getTable("Contact\_List"); 
    
    //Apply checked items filter on table contact\_List column Date 
    contact_List.getColumnByName("Date").getFilter().applyValuesFilter([{ 
        date: "2023", 
        specificity: ExcelScript.FilterDatetimeSpecificity.year 
    }]);

    let collection\_Notes = workbook.getWorksheet("Collection Notes"); 
    
    //Paste to range B10 on collection\_Notes from range A5 on selectedSheet  
    collection_Notes.getRange("B10").copyFrom(selectedSheet.getRange("A5"), 
    ExcelScript.RangeCopyType.all, false, false); 

    //Clear ExcelScript.ClearApplyTo.contents from range L5:R5 on selectedSheet  
    selectedSheet.getRange("L5:R5").clear(ExcelScript.ClearApplyTo.contents);
 
    //Set visibility of column(s) at range A:L on selectedSheet to false 
    selectedSheet.getRange("A:L").setColumnHidden(false); 
    
    //Clear filter on table contact\_List column "Date" 
    contact\_List.getColumnByName("Date").getFilter().clear();  
}

I tried modifying the script several ways, for null, for '<>'. It either would error out, or the filter wouldn't find anything.

Here is the Contact_List table. Goal is to copy column A and L-R to Notes table on Collection Note sheet. Then clear columns L-R on Contact_List

This is the Notes table (destination). Notes from Contact_List should append to the bottom of this:

Answer 1

• Assume the data layout as screenshot.
• Please provide more details about the data layout of contact_List and Collection Notes sheet.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set visibility of column(s) at range B: K on selectedSheet to true
  selectedSheet.getRange("B:K").setColumnHidden(true);
  let contact_List = workbook.getTable("Contact_List");
  // Filter non-blank row on table contact_List column [Date]
  let dateColIndex = contact_List.getColumnByName("Date").getIndex()
  let tabFitler = contact_List.getAutoFilter();
  tabFitler.apply(tabFitler.getRange(), dateColIndex, {
    filterOn: ExcelScript.FilterOn.custom,
    criterion1: '<>'
  });
  // Visible source table range
  let sourceRange = contact_List.getRangeBetweenHeaderAndTotal()
    .getSpecialCells(ExcelScript.SpecialCellType.visible);
  let collection_Notes = workbook.getWorksheet("Collection Notes");
  // Paste to range B10 on collection_Notes from range A5 on selectedSheet
  collection_Notes.getRange("B10").copyFrom(sourceRange,
    ExcelScript.RangeCopyType.all, false, false);
  // Clear ExcelScript.ClearApplyTo.contents from range L5: R5 on selectedSheet
  selectedSheet.getRange("L5:R5").clear(ExcelScript.ClearApplyTo.contents);
  // Set visibility of column(s) at range A: L on selectedSheet to false
  selectedSheet.getRange("A:L").setColumnHidden(false);
  // Clear filter on table contact_List column "Date"
  contact_List.getColumnByName("Date").getFilter().clear();
}

---

Update:

Question (comment): I think I need a bit more help so that the past is made as next row in Notes table, rather than a specific column/row.

  let lastRow = collection_Notes.getUsedRange(true).getLastRow().getRowIndex();
  collection_Notes.getCell(lastRow + 1, 1).copyFrom(sourceRange,
    ExcelScript.RangeCopyType.all, false, false);