Trying to search for column by column value in Office Scripts

Question

Hi everyone,

I am trying to create an office script that will calculate the percentage of Y's and Y+N's in a certain column, and searching for the column name in row 4 rather than hard-coding the index. Below is my code, but I keep getting the issue with percentValues.length, where it's saying the length is 0 so i'm not pulling anything in. I know for a fact that the string i'm searching for exists and its in the range A4:DK4 so I was wondering if anyone sees an issue with my code here?
Thanks!

// Function to calculate the percentage of 'Y' values in a range
function calculatePercentage(sheet: ExcelScript.Worksheet, value: string): number {
    let column = sheet.getRange("A4:DK4").getValues()[0].indexOf(value) + 1;
    let percentRange = sheet.getRange(column + "5:300");
    let percentValues = percentRange.getValues();
    let yCount = 0;
    let nCount = 0;
    for (let row = 0; row < percentValues.length; row++) {
        if (percentValues[row][0] === 'Y') {
            yCount++;
        } else if (percentValues[row][0] === 'N') {
            nCount++;
        }
    }
    return yCount / (yCount + nCount);
}

Answer 1

Your code has a few issues that need to be addressed:

Range Addressing Issue: column + "5:300" is not a valid way to address the range dynamically. We need to convert the column index to the corresponding letter.

Range Size Mismatch: Ensure the range percentRange covers the correct cells.

Error Handling: Handle cases where the column name might not be found.

Here's an updated version of your script with these issues addressed:

typescript

function calculatePercentage(sheet: ExcelScript.Worksheet, value: string): number {

*// Get the index of the column header*

*const columnIndex = sheet.getRange("A4:DK4").getValues()[0].indexOf(value);*



*// Check if the column index is found*

*if (columnIndex === -1) {*

    *throw new Error(`Column header "${value}" not found in row 4.`);*

*}*

*// Convert column index to a letter (A = 1, B = 2, etc.)*

*const columnLetter = String.fromCharCode(65 + columnIndex);*

*const percentRange = sheet.getRange(`${columnLetter}5:${columnLetter}300`);*

*const percentValues = percentRange.getValues();*



*let yCount = 0;*

*let nCount = 0;*

*// Iterate over the values to count 'Y' and 'N'*

*for (let row = 0; row < percentValues.length; row++) {*

    *if (percentValues[row][0] === 'Y') {*

        *yCount++;*

    *} else if (percentValues[row][0] === 'N') {*

        *nCount++;*

    *}*

*}*

*// Check to avoid division by zero*

*if (yCount + nCount === 0) {*

    *return 0;*

*}*

*// Return the percentage of 'Y' values*

*return yCount / (yCount + nCount);*

}

Explanation of Changes:

• Finding the Column Index:

const columnIndex = sheet.getRange("A4:DK4").getValues()[0].indexOf(value); retrieves the index of the column header.

We then check if the column index is found. If not, an error is thrown.

Converting Column Index to Letter:

const columnLetter = String.fromCharCode(65 + columnIndex); converts the column index to the corresponding letter (assuming columns start from A).

Accessing the Range:

const percentRange = sheet.getRange(${columnLetter}5:${columnLetter}300); dynamically creates the range based on the found column letter.

Counting 'Y' and 'N' Values:

We iterate through percentValues to count occurrences of 'Y' and 'N'.

We check to avoid division by zero before calculating the percentage.

By addressing these points, the script should work as expected, calculating the percentage of 'Y' values in the specified column.