How to create pivot table in excel using power automation

(0) Share

Report

Posted on by neerajpokh

159

How to create pivot table in excel using power automation

Categories:

General topics

All responses (4)

Answers (0)

Sort by

Rhiassuring 8,692 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Is the table going to be the same every time?
You could use Power Automate Desktop, and use mostly hot keys in Excel to do this. You will need to ensure you have premium licensing if it will be unattended.
jiwhite 500 on at

Like
a
(1)

Report
Copy link

Link copied!

Write an Office Script to create the Pivot table from the Automate menu, for example:

function main(workbook: ExcelScript.Workbook) {
    let wsData = workbook.getWorksheet("Details");
    let tblDetails = wsData.getTable("Details");
    let wsPivot = workbook.getWorksheet("Sheet1");
    let rngPivot = wsPivot.getRange("A1:B10");
    wsPivot.addPivotTable("PivotTest",tblDetails,rngPivot);
}

Then, call the script in your flow using a Run Script connector.
neerajpokh 159 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @jiwhite
can you please tell me all steps which need to follow.
means like where to write Office Script
jiwhite 500 on at

Like
a
(0)

Report
Copy link

Link copied!

In Excel 365 online, click on the Automate tab, then New Script to write Office Script code. It's language is TypeScript, which is a strongly typed version of JavaScript. After writing the Office Script, create a flow that uses the Excel Online (Business) Run Script connector to run the Office Script.
In the Run script connector, you specify the workbook that is running the script and which script to run, for example:

You can pass additional text information to and from scripts by adding additional properties in the main function, then there will additional boxes to complete in the connector and you'll be able to use what is returned in following connectors, for example:

function main(workbook: ExcelScript.Workbook, tablename:string):string[] {
// function content using input variable, i.e., tablename string
let values = workbook.getTable(tablename).getHeaderRowRange().getValues0 as string[][];
// return value matching type of main return, i.e., string array of header row
return values[0];
}