Power Apps / Automate – Get a collection and write it to Excel (Office Scripts)

Introduction:

This is the blueprint on how to use Office Scripts to transfer a collection from your Power App to an Excel file. We will create a sample collection from Power Apps and transfer that data to Excel using Office Scripts.

Prerequisites:

Make sure that you can see the Automate tab in either your Excel Desktop app or in Excel Online. Organization settings and licenses enables this one. You will also use your OneDrive account for tests.

Step 1:

From your browser, go to OneDrive. From the home page of OneDrive, do the following:

1. Click the “Add New” button
2. Click on “Excel workbook”

Step 2:

A new workbook (Excel file) will be opened in a new tab. Rename this workbook into My Excel Template.

Step 3:

From this Excel file:

1. Click the “Automate” tab on the ribbon
2. Click the “New Script” button. A new pane on the left side will appear.

Step 4:

On the script pane. You will see a pre-made script that is available by default. Replace that script with the one below:

function main(
    workbook: ExcelScript.Workbook,
    CollectionFromPowerApps: object[]
) {
    const mySheet = workbook.getWorksheet("Sheet1");
    mySheet.activate();

    // Determine the data's shape by getting the properties in one object.
    // This assumes all the JSON objects have the same properties.
  const columnNames = getPropertiesFromJson(CollectionFromPowerApps[0]);

    // Create the table headers using the property names.
    const headerRange = mySheet.getRangeByIndexes(2, 2, 1, columnNames.length);
    headerRange.setValues([columnNames]);

    // Create a new table with the headers. Remove this block if you don't want it to be an Excel table.
    const newTable = mySheet.addTable(headerRange, true);

    // Add each object in the array of JSON objects to the table.
  const tableValues = CollectionFromPowerApps.map(row => convertJsonToRow(row));
    //newTable.addRows(-1, tableValues);
    mySheet.getRangeByIndexes(3, 2, tableValues.length, columnNames.length).setValues(tableValues);
}

/**
 * This function turns a JSON object into an array to be used as a table row.
 */
function convertJsonToRow(obj: object) {
    const array: (string | number)[] = [];

    // Loop over each property and get the value. Their order will be the same as the column headers.
    for (let value in obj) {
        array.push(obj[value]);
    }
    return array;
}

/**
 * This function gets the property names from a single JSON object.
 */
function getPropertiesFromJson(obj: object) {
    const propertyArray: string[] = [];

    // Loop over each property in the object and store the property name in an array.
    for (let property in obj) {
        propertyArray.push(property);
    }

    return propertyArray;
}

Step 5:

Rename the script title as Power Apps Demo. Save the script. Close the browser tab of the Excel file.

Step 6:

Create a new app using a blank canvas in Power Apps. From a blank canvas, do the following:

1. Click the Power Automate icon on the left pane
2. Click “Create new flow”

Step 7:

Click ‘Create from blank’ button. You will be directed to the flow editor.

Step 8:

In the PowerApps (V2) trigger. Add the following inputs:

My Collection

Step 9:

Add the action below:

Get file content

File (pick from file picker)

Step 10:

Add the action below:

Create file

Folder Path /

File Name (paste this in the Expression tab)

concat('test',formatDateTime(utcNow(),'yyyyMMdd-HHmmss'),'.xlsx')

File Content

Step 11:

Go to settings of Create file. Turn Allow Chunking to Off so that the file will overwrite existing ones.

Step 12:

Add the action below:

Parse JSON

Content

Schema

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Available": {
                "type": "string"
            },
            "Item": {
                "type": "string"
            },
            "Price": {
                "type": "integer"
            }
        },
        "required": [
            "Available",
            "Item",
            "Price"
        ]
    }
}

Note: The schema that we copied and pasted is compatible with the sample collection that we are using. To find out the schema of another collection, please see this article: https://automations365.com/power-automate-get-schema-of-a-power-app-collection-for-parse-json-action/

Step 13:

Add the action below:

Run Script

Location OneDrive for Business

Document library OneDrive

File

Script Power Apps Demo

CollectionFromPowerApps (switch to input entire array)

Note: the CollectionFromPowerApps field will not appear until the Script field is filled up correctly as Power Apps Demo. It may take a few seconds to load.

In the CollectionFromPowerApps, make sure to switch input to entire array (small button on the right side) before putting the value.

Step 14:

Add the action below:

Create share link

File

Link Type Edit

Link scope Organization

Step 15:

Add the action below:

Respond to a Power App or flow

link =

Checking:

Rename the flow as Run Excel Script. Save the flow and exit. You should return to the Power App studio.

The flow named Run Excel Script should be in your Power Automate pane.

Step 16:

On the canvas of your Power app, insert a button named Button1. Change the following properties:

Text property

"Create Collection"

OnSelect property

ClearCollect(collInventory,
Table(
{Item:"Headphones",Price:500,Available:"N"},
{Item:"Speakers",Price:300,Available:"N"},
{Item:"Mouse",Price:100,Available:"Y"},
{Item:"Laptop",Price:4000,Available:"N"})
)

Step 17:

Insert a data table named DataTable1. Change the following properties:

Items property

collInventory

Step 18:

Change the Fields property of DataTable1 by doing the following steps in the properties pane.

1. Click ‘Edit fields’ button on the Fields property.
2. Click ‘Add field’
3. Check all three fields (Available, Item, Price)
4. Click the ‘Add’ button

You should be able to see the columns show up in the table.

Step 19:

Insert another button named Button2. Change the following properties:

Text property

"Export To Excel"

OnSelect property

Set(varJSON,JSON(collInventory,JSONFormat.Compact));
Launch(RunExcelScript.Run(varJSON).link);

Conclusion:

To test, hit play mode. Click the ‘Create Collection’ button. You will notice the data table will show our sample data. Then click the ‘Export to Excel’ button. Once the loading is finished, the Excel file will be opened in a new tab. You should see the Excel table with the data of our sample collection.

Note: Every time you push the button, a new file will be created in your OneDrive. Make sure to delete them after you are done testing.