TypeScript/Office Scripts - Sum the Values of A Cell Based on The Values of An Another cell

Question

I am recently experimenting with Office Scripts and TypeScript and I have encountered the following problem.

I have an Excel file that contains the following data:

• calendar weeks -> eg. CW1 = calendar week1, CW2 = calendar week 2 etc
• the values for each of the calendar weeks: eg. 3 for CW1, 2 for CW4 etc.

What I want to achieve is sum all the values for each of the calendar weeks.

So for example I have all calendar weeks listed in column D and I have corresponding calendar week values present in column B and their values in column A.

If there is a match between column D and column B I want to create a new entry in column E based on the calendar week value in column A.

I attach the screenshots of my Excel input below:

I attach my desired output below:

Example:

I am having a look at cell D1. The value stored in that cell is CW1. I am then having a look at column B. I can see that I have two corresponding calendar weeks there in cells B1 and B2 and their respective values in columns A1 and A2. The overall sum for CW1 would be 3 and I would like to store that value in cell E1(right next to column D so that I have good visibility of the data).

CW2 - would not have a value as it does not have a corresponding week present in column B. CW3 - the value of CW3 would be 3 CW3 - the value of CW3 would be 4 and so on.

This action would be repeated for all of the calendar weeks in column D.

What is more, below I attach the code by which I was trying to solve the whole issue.

{
  // Get the worksheet named "Sheet1".
  const sheet = workbook.getWorksheet('Sheet1');

  //get the data from column A
  const valuesForColumn1 = sheet.getCell(0, 0).getEntireColumn().getUsedRange().getValues();

  //get the data from column B
  const valuesForColumn2 = sheet.getCell(0, 1).getEntireColumn().getUsedRange().getValues();

  //get the data from column E
  const valuesForColumn4 = sheet.getCell(0, 3).getEntireColumn().getUsedRange().getValues();

  
  //initialize local variables
  var data1:number = 0;
  var data2:string = '';
  var data3:string = '';
  var data4:number = 0;
  var data5:number = 0;

  //loop through each calendar week in column D
  for (let x = 0; x < valuesForColumn4.length; x++)
  {

    //console.log(sheet.getCell(x,3).getValue())

    //loop through each calendar week in column 2
    for (let y = 0; y < valuesForColumn2.length; y++)
    {
      //check if there is a calendar week value match between column 4 and column 2
      if(sheet.getCell(x,3).getValue() == sheet.getCell(y,1).getValue())
      { 
        
      
      //check if there are more than 1 occurences of a calendar week in column B
      if(sheet.getCell(y,1).getValue() == sheet.getCell(y+1,1).getValue())
      {

        //sum the calendar week values from column A based =on the values in column B
        data2 = sheet.getCell(y, 0).getValue().toString();

        data3 = sheet.getCell(y+1,0).getValue().toString();

        data4 = +data2;

        data5 = +data3;

        data1 = data4 + data5;

        sheet.getCell(x,4).setValue(data1);

      }
      else
      {
        //sum the values in column A based on the values in column B, for a single calendar week
        sheet.getCell(x,4).setValue(sheet.getCell(y,0).getValue());

      }

      }
      else
       {//console.log("false")}
      }
   }
  }
}

However when I run the code the output is as follows:

The data was summed but unfortunately the output is incorrect as the sum for CW1 should be 3.

The rest of the output is correct.

Has anybody got an idea what would the best way to fix the issue that exists within my code?

Many Thanks.

Answer 1

Your issue is with one of the if statements in your for loop. Please see below:

 //check if there are more than 1 occurences of a calendar week in column B
  if(sheet.getCell(y,1).getValue() == sheet.getCell(y+1,1).getValue())
  {

    //sum the calendar week values from column A based =on the values in column B
    data2 = sheet.getCell(y, 0).getValue().toString();

    data3 = sheet.getCell(y+1,0).getValue().toString();

    data4 = +data2;

    data5 = +data3;

    data1 = data4 + data5;

    sheet.getCell(x,4).setValue(data1);

  }
  else
  {
    //sum the values in column A based on the values in column B, for a single calendar week
    sheet.getCell(x,4).setValue(sheet.getCell(y,0).getValue());

  }

For the first CW1 value in cell B1, the if statement evaluates as true. This is because the second CW1 is right below it. The Data1 variable ends up with the correct value of 3. And that value is written to cell E1. When the second CW1 is found in cell B2, the else condition of the if statement is evaluated to true. This is because there isn't another CW1 below it. So the value adjacent to the second BW1 in cell A2 is written to cell E1. And this value overwrites the previous value of 3 that was written to the cell.

The approach I would take is to use a temporary variable in the first loop. In that loop set the temporary variable to zero. If you find a match between values in columns B & D, add the corresponding value in column A to the temporary variable. Once you've finished iterating through all the values in column B in your second loop, write the value in the temporary variable to the cell. That is a simplified breakdown of the approach I took here.