Hi @
MK-20061705-0 , I believe I have what you need.
Step 1: Test an Office Script
-
In your sample Excel workbook, you have a worksheet named src that contains a table named Source.
-
To test the Office Script, first save the Excel file to OneDrive or SharePoint. Then:
- Open the workbook in Excel.
- Select the Automate tab.
- Click New Script and choose Create in Code Editor.
- A sample script will appear in the editor on the right side of the screen. Replace it with the following code:
function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet
const sourceSheet = workbook.getActiveWorksheet();
// Get the used range and its values
const usedRange = sourceSheet.getUsedRange();
const sourceValues = usedRange.getValues();
// Transpose the data
const transposedValues = sourceValues[0].map((_, colIndex) =>
sourceValues.map(row => row[colIndex])
);
// Create or get the destination worksheet
let destinationSheet = workbook.getWorksheet("Transposed");
if (!destinationSheet) {
destinationSheet = workbook.addWorksheet("Transposed");
}
// Define the starting cell
const destinationStartCell = destinationSheet.getRange("A1");
// Calculate the destination range size
const rowCount = transposedValues.length;
const columnCount = transposedValues[0].length;
// Resize the destination range
const destinationRange = destinationStartCell.getResizedRange(
rowCount - 1,
columnCount - 1
);
// Write the transposed values to the destination worksheet
destinationRange.setValues(transposedValues);
}
-
Save the script by clicking the pencil icon, entering a meaningful name, and selecting Save.
Note: Office Scripts are stored in your OneDrive. The default location is typically the Office Scripts folder.
- To test the script, click Run.
-
The script will:
- Read the data from the active worksheet.
- Create a new worksheet named Transposed (if it does not already exist).
- Transpose the source data (swap rows and columns).
- Write the transposed data starting at cell A1 on the Transposed worksheet.
After the script completes, you should see the transposed version of your source data on the Transposed sheet.
Step 2: Create a Power Automate Flow to Run the Office Script
After testing and saving the Office Script, the next step is to create a Power Automate flow that runs the script automatically.
Scenario
In this example, the flow will:
Trigger manually.
Open the Excel workbook stored in OneDrive or SharePoint.
Run the Office Script.
Create or update the Transposed worksheet with the transposed data.
Create the Flow
1. Open Power Automate
Navigate to https://make.powerautomate.com.
Sign in with your Microsoft 365 account.
Select Create from the left navigation menu.
2. Create an Instant Cloud Flow
Select Instant Cloud Flow.
Enter a name, for example:
Transpose Excel Data
Choose the trigger:
Manually trigger a flow
Click Create.
3. Add the “Run Script” Action
Click + New Step.
Search for Excel Online (Business).
Select the action:
Run script
4. Configure the Excel Workbook
Populate the required fields:
Location: OneDrive for Business or SharePoint Site
Document Library: Documents (if using SharePoint)
File: Select the Excel workbook containing the Office Script
Script: Select the script you saved earlier
Example:
Location: OneDrive for Business
File: SourceFile.xlsx
Script: Select the Script you've created and tested e.g. Transpose Source Data
5. Save the Flow
Click Save in the upper-right corner.
Wait for the flow to be successfully saved.
6. Test the Flow
Click Test.
Select Manually.
Click Run Flow.
Select Done.
Power Automate will:
Open the workbook.
Execute the Office Script.
Create the Transposed worksheet if it does not exist.
Write the transposed data into the worksheet.
See the attached image as the reference of the expected outcome
Let me know if this works for you
