Glad find useful resource where can share my best practices , in my case I had data present sr number , emp name , sales , order date , month, week and email id along with other fields untill BB column of excel now wish to see the data in pivot normally we use pivotable manually do a pivot in our excel for me find this call Automate ( Script ) loved to use this used below code find useful result
function main(workbook: ExcelScript.Workbook) {
// Define the range
let dataSheet = workbook.getActiveWorksheet();
let dataRange = dataSheet.getRange("A1:BB50");
// Read data into a 2D array
let dataArray = dataRange.getValues();
// Initialize variables for summary
let summary = {};
// Loop through the data to calculate sales by month
dataArray.forEach(row => {
let month = row[0]; // Assuming the month is in the first column
let sales = row[1]; // Assuming sales data is in the second column
if (!summary[month]) {
summary[month] = sales;
} else {
summary[month] += sales;
}
});
// Prepare the summary data for output
let summaryArray = [];
for (let month in summary) {
summaryArray.push([month, summary[month]]);
}
// Output the summary to a new worksheet
let summarySheet = workbook.addWorksheet("Summary");
let summaryRange = summarySheet.getRange("A1:B" + (summaryArray.length + 1));
summary
Range.setValues(summaryArray);
}
If you want to leverage this automate script you can do this is available in my premium account of office 365 you can check :white_heavy_check_mark: yours if it's available in ribbon :ribbon:
Please note - it's not macro
