Excel Office Scripts - IF statement to handle exceptions-CodePudding

Summary: I have a multi-step script where I need an IF statement to handle a scenario when the range I am copying from could be empty. I need the script to move onto the next step even if it "skips" the immediate next step.

Scenario: A step in the middle of the script is to set several column filters and then copy the filtered results to another table. If the filtered results are blank/empty ,I want the script to skip the "copyfrom" step and move onto the next step which releases the previous filters that were set.

I attached a screenshot of the excel sample showing the filtered results are blank. The shown filters need to be released/cleared but the script is just stopping because the results are blank and it can't complete the immediate next step (copyfrom).

Script: I had help from another user on here with an IF statement in a different scenario, I tried to use that logic as my base for my current use case...but wasn't successful.

Here's the current script:

function main(workbook: ExcelScript.Workbook)
{
let target = workbook.getWorksheet('Target');
let source = workbook.getWorksheet('Source');
let targetTable = workbook.getTable('Target');
let sourceTable = workbook.getTable('Source');
const visibleRange = source.getUsedRange().getColumn(0);
let statusColumn = sourceTable.getColumnByName("Status");
let statusColumnRange = statusColumn.getRangeBetweenHeaderAndTotal();

  
//Identify last used row in Target sheet
const usedRange = target.getUsedRange();
console.log(usedRange.getAddress());

//Insert new row after last used row in Target sheet
const startCell = usedRange.getLastRow().getCell(0,0).getOffsetRange(1,0);
console.log(startCell.getAddress());
const targetRange = startCell.getResizedRange(0,0);

// Clear all filters on the table so that script filters can be applied 
sourceTable.getAutoFilter().clearCriteria();

//Replace blanks with "null"
let emptyStatusCells = statusColumnRange.getSpecialCells(ExcelScript.SpecialCellType.blanks);

if (emptyStatusCells != undefined) {
let rangeAreas = emptyStatusCells.getAreas();

rangeAreas.forEach(range => {
let values = range.getValues();

values.forEach(cellValue => {
cellValue[0]= "null";
})
range.setValues(values);
})

//Clear Occurrence seq formula and re-apply
let sourceShiftedVisibleRangeFormula = visibleRange.getOffsetRange(1, 2);
let C2 = source.getRange('C2');

sourceShiftedVisibleRangeFormula.getUsedRange().clear();

C2.setFormula("=COUNTIF($A$2:A2,A2)");


//Filter Sources
const DuplicateFilter = 'Duplicate';
const ValueOfDuplicateFilter = 'Duplicate';
const OccurrenceFilter = 'Occurrence';
const ValueOfOccurrenceFilter = '1';
const IncludeInDupFilter = 'Include Dup Filter';
const ValueOfIncDupFilter = 'Yes';

//Find columns to filter
const duplicateFilter = sourceTable.getColumnByName(DuplicateFilter);
const occurrenceFilter = sourceTable.getColumnByName(OccurrenceFilter);
const includeDupFilter = sourceTable.getColumnByName(IncludeInDupFilter);

//Select values to filter by
duplicateFilter.getFilter().applyValuesFilter([ValueOfDuplicateFilter]);
occurrenceFilter.getFilter().applyValuesFilter([ValueOfOccurrenceFilter]);
includeDupFilter.getFilter().applyValuesFilter([ValueOfIncDupFilter]);

//Set source visible range to copy from
console.log(visibleRange.getAddress());
//const shiftedVisibleRange = visibleRange.getOffsetRange(1,0);
const sourceShiftedVisibleRange= visibleRange.getOffsetRange(1,4)
console.log(sourceShiftedVisibleRange.getAddress());
let sh = workbook.getActiveWorksheet();
let visTble = sh.getTable('Source');
let rv = visTble.getRangeBetweenHeaderAndTotal().getVisibleView();

  if (rv.getRowCount() > 0){
  let shiftedVisibleRange = visibleRange.getOffsetRange(1,0);
      
//Paste into Target table
targetRange.copyFrom(shiftedVisibleRange.getUsedRange(), ExcelScript.RangeCopyType.all, false, 
false);

//Clear Occurrence filter to show all duplicate rows
  occurrenceFilter.getFilter().clear();

//Set the Include Dup Filter string values to logged
let stringValue= "logged"

//Update include Dup Filter to logged for duplicate rows moved to target table during this 
process
 sourceShiftedVisibleRange.getUsedRange().setValue(stringValue);

//Clear all other filters setby script
sourceTable.getAutoFilter().clearCriteria();

}
}

This is the section of the script that I am struggling with, if empty it should skip the "Paste into Target table step" and move onto the next step called "Clear Occurrence filter to show all duplicate rows"

let sh = workbook.getActiveWorksheet();
let visTble = sh.getTable('Source');
let rv = visTble.getRangeBetweenHeaderAndTotal().getVisibleView();

if (rv.getRowCount() > 0){
let shiftedVisibleRange = visibleRange.getOffsetRange(1,0);
      
//Paste into Target table
targetRange.copyFrom(shiftedVisibleRange.getUsedRange(), 
ExcelScript.RangeCopyType.all, false, 
false);

//Clear Occurrence filter to show all duplicate rows
  occurrenceFilter.getFilter().clear();

CodePudding user response：

I think you can do this with your source table using the getVisibleView() method. Once you used that method, you could get the row count. Code that did that would look something like this:

  function main(workbook: ExcelScript.Workbook){  
    let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();
    let tbl: ExcelScript.Table = sh.getTable("Table1");
    let rv: ExcelScript.RangeView = tbl.getRangeBetweenHeaderAndTotal().getVisibleView()
    if (rv.getRowCount() > 0){
      //more code here
    }
  }