TypeScript Code

Get Column Number

var datasheet = context.workbook.worksheets.getItem('Sheet1');  
var startRange = datasheet.getRange('Sheet1').Range("A1");  
startRange.load('columnIndex');  
context.sync().then( function() { 
   console.log(startRange.columnIndex); 
}); 

var _range = context.workbook.names.getItem("myNamedRange").getRange(); 
var _rangeValues = _range.getRow(1).getBoundingRect(_range.getLastCell()); 
var _myCol = _range.getColumn(0); 

Count blank rows on a given sheet

function main(workbook: ExcelScript.Workbook): number 
{ 
  // Get the worksheet named "Sheet1". 
  const sheet = workbook.getWorksheet('Sheet1');  
  
  // Get the entire data range. 
  const range = sheet.getUsedRange(true); 

  // If the used range is empty, end the script. 
  if (!range) { 
    console.log(`No data on this sheet.`); 
    return; 
  } 
  
  // Log the address of the used range. 
  console.log(`Used range for the worksheet: ${range.getAddress()}`); 
    
  // Look through the values in the range for blank rows. 
  const values = range.getValues(); 
  let emptyRows = 0; 
  for (let row of values) { 
    let emptyRow = true; 
    
    // Look at every cell in the row for one with a value. 
    for (let cell of row) { 
      if (cell.toString().length > 0) { 
        emptyRow = false 
      } 
    } 

    // If no cell had a value, the row is empty. 
    if (emptyRow) { 
      emptyRows++; 
    } 
  } 

  // Log the number of empty rows. 
  console.log(`Total empty rows: ${emptyRows}`); 

  // Return the number of empty rows for use in a Power Automate flow. 
  return emptyRows; 
} 

Count blank rows on all sheets

function main(workbook: ExcelScript.Workbook): number 
{ 
  // Loop through every worksheet in the workbook. 
  const sheets = workbook.getWorksheets(); 
  let emptyRows = 0; 
  for (let sheet of sheets) {     
    // Get the entire data range. 
    const range = sheet.getUsedRange(true); 
  
    // If the used range is empty, skip to the next worksheet. 
    if (!range) { 
      console.log(`No data on this sheet.`); 
      continue; 
    } 
    
    // Log the address of the used range. 
    console.log(`Used range for the worksheet: ${range.getAddress()}`); 
      
    // Look through the values in the range for blank rows. 
    const values = range.getValues(); 
    for (let row of values) { 
      let emptyRow = true; 
      
      // Look at every cell in the row for one with a value. 
      for (let cell of row) { 
        if (cell.toString().length > 0) { 
          emptyRow = false 
        } 
      } 
  
      // If no cell had a value, the row is empty. 
      if (emptyRow) { 
        emptyRows++; 
      } 
    } 
  } 

  // Log the number of empty rows. 
  console.log(`Total empty rows: ${emptyRows}`); 

  // Return the number of empty rows for use in a Power Automate flow. 
  return emptyRows; 
} 

Move rows using range values

function main(workbook: ExcelScript.Workbook) { 

  // You can change these names to match the data in your workbook. 
  const TARGET_TABLE_NAME = 'Table1';  
  const SOURCE_TABLE_NAME = 'Table2';  

  // Select what will be moved between tables. 
  const FILTER_COLUMN_INDEX = 1; 
  const FILTER_VALUE = 'Clothing';  

  // Get the Table objects. 
  let targetTable = workbook.getTable(TARGET_TABLE_NAME); 
  let sourceTable = workbook.getTable(SOURCE_TABLE_NAME); 

  // If either table is missing, report that information and stop the script. 
  if (!targetTable || !sourceTable) { 
    console.log(`Tables missing - Check to make sure both source (${TARGET_TABLE_NAME}) and target table (${SOURCE_TABLE_NAME}) are present before running the script. `); 
    return; 
  } 

  // Save the filter criteria currently on the source table. 
  const originalTableFilters = {}; 
  // For each table column, collect the filter criteria on that column. 
  sourceTable.getColumns().forEach((column) => { 
    let originalColumnFilter = column.getFilter().getCriteria(); 
    if (originalColumnFilter) { 
      originalTableFilters[column.getName()] = originalColumnFilter; 
    } 
  }); 

  // Get all the data from the table. 
  const sourceRange = sourceTable.getRangeBetweenHeaderAndTotal(); 
  const dataRows: (number | string | boolean)[][] = sourceTable.getRangeBetweenHeaderAndTotal().getValues(); 

  // Create variables to hold the rows to be moved and their addresses. 
  let rowsToMoveValues: (number | string | boolean)[][] = []; 
  let rowAddressToRemove: string[] = []; 

  // Get the data values from the source table. 
  for (let i = 0; i < dataRows.length; i++) { 
    if (dataRows[i][FILTER_COLUMN_INDEX] === FILTER_VALUE) { 
      rowsToMoveValues.push(dataRows[i]); 

      // Get the intersection between table address and the entire row where we found the match. This provides the address of the range to remove. 
      let address = sourceRange.getIntersection(sourceRange.getCell(i,0).getEntireRow()).getAddress(); 
      rowAddressToRemove.push(address); 
    } 
  } 

  // If there are no data rows to process, end the script. 
  if (rowsToMoveValues.length < 1) { 
    console.log('No rows selected from the source table match the filter criteria.'); 
    return; 
  } 

  console.log(`Adding ${rowsToMoveValues.length} rows to target table.`); 

  // Insert rows at the end of target table. 
  targetTable.addRows(-1, rowsToMoveValues) 

  // Remove the rows from the source table. 
  const sheet = sourceTable.getWorksheet(); 

  // Remove all filters before removing rows. 
  sourceTable.getAutoFilter().clearCriteria(); 

  // Important: Remove the rows starting at the bottom of the table. 
  // Otherwise, the lower rows change position before they are deleted. 
  console.log(`Removing ${rowAddressToRemove.length} rows from the source table.`); 
  rowAddressToRemove.reverse().forEach((address) => { 
    sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up); 
  }); 

  // Reapply the original filters. 
  Object.keys(originalTableFilters).forEach((columnName) => { 
      sourceTable.getColumnByName(columnName).getFilter().apply(originalTableFilters[columnName]); 
    }); 
}