Power Platform Community Forum Thread Details

I am working on a project with power automate.

And there is a related online excel which is not in a table as below structure:

Invoice Number	Product ID	Amount
106	P1	100
106	P1	200
106	P2	200
107	P3	300
107	P3	150

I want to add two rows, one is a subtotal row for the same Invoice Number and same product ID, the other one is an empty row.

The result should be like:

Invoice Number	Product ID	Amount
106	P1	100
106	P1	200
	Total:	300

106	P2	200
	Total:	200

107	P3	300
107	P3	150
	Total:	450

How can I achieve the goal with Office Scripts?

Categories:

General topics

Hi @Anya0601 ,

I've made a test for your reference:

1\My Excel table:

2\Office Script

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	let getAmmounts = Number(selectedSheet.getRange('C2').getValues());
	for(let i=2;i<1000;i++)
	{
		if (selectedSheet.getRange('B' + (i)).getValue() == "") { break };
		let ProductID1 = String(selectedSheet.getRange('B' + i).getValues());
		let ProductID2 = String(selectedSheet.getRange('B' + (i+1)).getValues());
		if (ProductID1 == ProductID2) 
		 {
			getAmmounts = getAmmounts + Number(selectedSheet.getRange('C' + (i + 1)).getValues())
		 }else{
			selectedSheet.getRange((i + 1) + ':' + (i + 1)).insert(ExcelScript.InsertShiftDirection.down);
			selectedSheet.getRange((i + 1) + ':' + (i + 1)).insert(ExcelScript.InsertShiftDirection.down);
			selectedSheet.getRange('B' + (i + 1)).setValue("Total:");
			selectedSheet.getRange('C' + (i + 1)).setValue(getAmmounts);
			getAmmounts = Number(selectedSheet.getRange('C' + (i + 3)).getValue());
			i=i+2
		 }

	}
}

Result: