Trying to search for column by column value in Office Scripts

Lai, Ian 20 Reputation points
2024-06-27T16:56:38.69+00:00

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);
}
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,685 questions
JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
942 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,715 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Mansoob e Zahra 235 Reputation points
    2024-07-06T08:23:50.6533333+00:00

    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.

    0 comments No comments