As a newcomer to TypeScript, I have set a goal for today - to calculate the total sum of cell values in one column of an Excel file based on values from another column.
In my Excel spreadsheet, the calendar weeks are listed in column U and their corresponding values are in column R. My aim is to add up all the values for each week (in columns R and U), and store the overall sum for each week within the Excel file.
I keep my Excel file on OneDrive online, with all my data securely stored there. By accessing the "Automate" Tab in Excel, I am able to utilize Office Scripts - which functions similarly to Excel VBA but employs the TypeScript programming language instead.
Below you will find screenshots illustrating my attempted approach and what progress I have made so far.
- This is how my data appears in the Excel file:
https://i.stack.imgur.com/bwT6r.png
- Here is my attempt at solving the issue using OfficeScripts. I explored utilizing 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.);
}
For problem-solving reference, I consulted this StackOverflow post: VBA Code to sum values in one column based on the value in another column in excel
If you have any insights or suggestions on how to effectively tackle this issue, your input would be greatly appreciated. Thank you!