Populating excel-file -to-be-created with dynamic values

(0) Share

Report

Posted on by HK-11070012-0

I am trying to build a Power Automate flow that gets store list and item list (from a metadata excel file) for each manager, create a file (copy of a template), and write the values into this newly created file.

Below is my current flow.

The metadata file has two tables (assignment and item list).

So, first it'll get the assignment table

Then, select only managers column

And get a list of unique managers (this is Compose step using union: union(body('Select_Managers'),body('Select_Managers'))

it will also get the item list.

It will loop through each Manager.

- It will filter the list of stores that is assigned to the manager (Filter array).

- It will Get the region that the manager's stores belong to. (Compose)

- It will filter the list of items for the manager (Filter array)

-Get file content of template file

My template looks like this: It has two sheets. Sheet1 has two tables. A1 to C2 is HeaderTable, and D5 to P27 is ItemCount table

Metadata sheet has one table: Metadata table

-after getting the template file, it will Create file for the manager, using dynamic file name like this: formatDateTime(utcNow(), 'yyyy-MM-dd')_item_count_items('Apply_to_each').xlsx
File Content is body('Get_file_content')

So far, it works. it is able to create files using the dynamic file name and it copies the template file.

I am now trying to write into the HeaderTable. So, I added Update a row, selected Site Address and Folder Path, and put outputs('Create_file')?['body/Id'] in File. As I don't have thie file or table yet, I manually typed in "HeaderTable" into Table.

I typed Row_ID into Key_Column and 1 into Key Value. I don't know what to put in "Provide the item properties". I want to add formatDateTime(utcNow(), 'yyyy-MM-dd') into cell B2 and the name of the manager, which is items('Apply_to_each'), into cell C2.

What is the best way to do this? I don't exactly understand what item properties mean.

I am very new to power automate. can someone help me? Thank you!

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Suggested answer

Riyaz_riz11 4,150 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi,

Solution for HeaderTable Update

Step 1: Configure the Update a row action properly

Action Settings:

Location: Site Address and Folder Path (as you have)

Document Library: Your document library

File: outputs('Create_file')?['body/Id'] (correct as you have)

Table: HeaderTable (correct as you have)

Key Column: This should be the first column of your HeaderTable (likely Column A)

Key Value: 1 (to target the first row of data)

Step 2: Provide the item properties

In the "Provide the item properties" section, you need to map the Excel table columns to your values. Based on your description that you want to update cells B2 and C2, your table structure likely looks like this:

Column Mappings:

Column A (Key Column): Keep existing value or use a unique identifier

Column B (Date): formatDateTime(utcNow(), 'yyyy-MM-dd')

Column C (Manager): items('Apply_to_each')

Step 3: Alternative Approach - Update individual cells

If the table approach doesn't work as expected, you can use "Update a cell" action instead:

For Cell B2 (Date):

Location: Site Address and Folder Path

Document Library: Your document library

File: outputs('Create_file')?['body/Id']

Worksheet: Sheet1

Cell: B2

Value: formatDateTime(utcNow(), 'yyyy-MM-dd')

For Cell C2 (Manager):

Location: Site Address and Folder Path

Document Library: Your document library

File: outputs('Create_file')?['body/Id']

Worksheet: Sheet1

Cell: C2

Value: items('Apply_to_each')

Recommended Approach

I recommend using the "Update a cell" approach for the following reasons:

More Direct: You're directly targeting specific cells (B2, C2) rather than working with table rows

Simpler Configuration: No need to worry about table structure or key columns

More Reliable: Less prone to errors related to table formatting

Complete Flow Structure

Apply to each (Manager) ├── Filter Array (Stores for Manager) ├── Compose (Get Region) ├── Filter Array (Items for Manager) ├── Get file content (Template) ├── Create file (Dynamic name) ├── Update a cell (B2 - Date) ├── Update a cell (C2 - Manager) └── [Continue with ItemCount table updates]

Important Notes

File ID: Make sure you're using the correct file reference: outputs('Create_file')?['body/Id']

Worksheet Name: Ensure the worksheet name matches exactly (case-sensitive)

Cell References: Use standard Excel cell references (B2, C2, etc.)

Date Format: The formatDateTime(utcNow(), 'yyyy-MM-dd') will give you format like "2024-07-14"

Next Steps

After successfully updating the HeaderTable, you can proceed to update the ItemCount table (D5 to P27) using similar "Update a cell" actions or by using "Add a row to a table" if you need to add multiple rows of item data.

If I have answered your question, please mark it as the preferred solution ✅ . If you like my response, please give it a Thumbs Up 👍.
Regards,
Riyaz

Was this reply helpful? Yes No
HK-11070012-0 12 on at

Like (0)

Report
Copy link

Link copied!
Riyaz_riz11

Because this file does not exist at the moment (it will be created in Create file step), when I input the below, nothing pops out under 'Provide the item properties'. It just shows a box. see this screenshot. So I don't know what to do from here. I tried to input expressions but can't figure out correct one.

As for 'update a cell' action, I couldn't find such action.

Step 1: Configure the Update a row action properly

Action Settings:

Location: Site Address and Folder Path (as you have)

Document Library: Your document library

File: outputs('Create_file')?['body/Id'] (correct as you have)

Table: HeaderTable (correct as you have)

Key Column: This should be the first column of your HeaderTable (likely Column A)

Key Value: 1 (to target the first row of data)

Was this reply helpful? Yes No