How do I select an entire column from a range?

Question

I am trying to identify all the cells in a column that have the following and change the font color to red.

• a space
• a hyphen
• an underscore
• more than 16 characters in a cell

This is my code

function main(workbook: ExcelScript.Workbook) {
   // Get the active worksheet 


  let sheet : ExcelScript.Worksheet = workbook.getActiveWorksheet();
  // Get the range for column A
  let range = sheet.getRange("A:A");  

  // Get the values for the range
  let values = range.getValues();

  // Check for null values
  if (values.length !== null) 
  {    // Loop through the values and check for conditions
    for (let row = 0; row < values.length; row++) 
    {
      let cellValue = values[row][0].toString();
      if(cellValue.length>16)
      {
        sheet.getRange(`A${row + 1}`).getFormat().getFont().setColor("Red");        
      }
      if(cellValue.includes(" "))
      {
        sheet.getRange(`A${row + 1}`).getFormat().getFont().setColor("Red");
      }
      if (cellValue.includes("-")) 
      {
        sheet.getRange(`A${row + 1}`).getFormat().getFont().setColor("Red");
      }
      if (cellValue.includes("_")) 
      {
        sheet.getRange(`A${row + 1}`).getFormat().getFont().setColor("Red");
      }
    }
    }
  }

When I run this script I get the following error.

Line 13: Can't read properties of null (reading 'length');

My Excel : Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20378) 64-bit

I didn't understand where I made a mistake as I used the getRange line from the row and column visibility example in this link. Looking for an answer I came across another way to select an entire column and made the the following changes. This got the script running perfectly.

let range = sheet.getUsedRange();
range.getColumn(0);  

I still haven't found an answer to my initial error. What am I doing wrong?

Answer 1

It looks like when you use a whole column reference (e.g. A:A) and try to get the valuesof that range, it returns null. e.g.:

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet 

    let sheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();
    // Get the range for column A
    let range = sheet.getRange("A:A")

    // Get the values for the range
    let values = range.getValues();
    console.log(values) //returns null
}

One alternative is to try to select the getExtendedRange method with the down argument to get the whole range of the column. So you could update your range variable to look like this:

let range = sheet.getRange("A1").getExtendedRange(ExcelScript.KeyboardDirection.down)

Another is to add getUsedRange to your original A:A range reference like so:

let range = sheet.getRange("A:A").getUsedRange();