Office Scripts: Code to sum values in one column based on the value in another column in Excel

Question

I am new to TypeScript and my goal today would be to sum up all the cell values in a column in an Excel file, based on the cell values in another column.

In my Excel file, I have the calendar weeks, stored in column U and the corresponding values for those calendar weeks in column R. As I mentioned at the beginning, my goal would be to sum up all the values for each calendar week (column R and corresponding column U), and save the overall sum for each week anywhere in an Excel file.

I have my Excel file on OneDrive online, with my data saved there. I open the Excel file and go into the "Automate Tab", which lets to have access to Office Scripts which is an equivalent of Excel VBA(but uses TypeScript programming language instead).

I attach the screens with what I was trying to do & what I have already done below.

1. Here is how my data looks in my Excel file:

2. Here is how I tried to solve the problem with the use of OfficeScripts. I was trying to solve the problem with a do-while loop.

function main(workbook: ExcelScript.Workbook)
{
  const sheet = workbook.getWorksheet('Sheet1'); 
  const range = sheet.getRange("A2:A6");
  const x: number = 1;
  let sum: number = 0;

  do 
  {
    if (sheet.getRange("A1").getOffsetRange(x,0) = '1')
    {
         sum = sum + sheet.getRange("A1").getOffsetRange(x,0)
    }
  } while(sheet.getRange("A").getColumn.);
}

I was making use of this SO post: VBA Code to sum values in one column based on the value in another column in excel when trying to solve the problem

If you have any suggestions as to how to solve the problem, your solutions are more than welcome. Thank you very much.

Answer 1

You can give the code below a try. It starts by getting the ranges for column U and column R for a specific sheet. Once it has the ranges, it gets the values associated with those ranges. After we have the values for your calendar column (column U), we get the unique values for that column. Those unique values are stored in an array. Once you have the unique values in an array, you iterate through the array. As you iterate through the array, you compare the current array element to the original calendar values. If the elements of both arrays match, you add the corresponding value in the sum column to a variable for that row. Once you've finished iterating through the full calendar column, the unique values and total are added to a Map object. This process continues repeats until the iteration of the unique calendar values array is completed. After that's done, the map object is returned by the function.

function main(workbook: ExcelScript.Workbook) {
    let ws: ExcelScript.Worksheet = workbook.getWorksheet("Sheet1")
    let criteriaStart: string = "U2"
    let sumStart: string = "R2"
    let map: Map<string, number> = getColumnTotals(ws, criteriaStart, sumStart);

    //After you have the map you can access specific elements like this

    console.log(map.get('2022 CW12'));

    //Or you can access all the elements in the map like this

    map.forEach(e => console.log(e))
}

function getColumnTotals(worksheet: ExcelScript.Worksheet, criteriaStartAddress: string, sumStartAddress: string): Map<string, number> {
    let criteriaRange: ExcelScript.Range = worksheet.getRange(criteriaStartAddress).getExtendedRange(ExcelScript.KeyboardDirection.down);
    let criteriaVals: string[][] = criteriaRange.getValues() as string[][];
    let sumRange: ExcelScript.Range = worksheet.getRange(sumStartAddress).getExtendedRange(ExcelScript.KeyboardDirection.down);
    let sumVals: number[][] = sumRange.getValues() as number[][];
    let map: Map<string, number> = new Map<string, number>();
    let tempArr: string[] = criteriaVals.map(e => e[0]);
    let uniqueCalendarVals: string[] = Array.from(new Set(tempArr));

    uniqueCalendarVals.forEach(uniqueCalVal => {
        let tempTotal: number = 0;
        criteriaVals.forEach((criVal, index) => {
            if (criVal[0] === uniqueCalVal) {
                tempTotal += sumVals[index][0];
            }
        })
        map.set(uniqueCalVal, tempTotal);
    });
    return map;
}