Entering a Value from PowerApps into a Specific Excel Range (Cell Name) using a Variable Excel File Name

(0) Share

Report

Posted on by AdamGill1965

311

I am part-way complete with a functioning Flow, which currently does the following (tested and confirmed):

Extracts a Variable String from PowerApps ("JobID").

Gets a copy of an Excel File from a "Template" Location.

Adds "JobID" to the front of the Filename of the Excel File.

Stores the "JobID" Excel File to a "Documents" Location.

Now, in that new Excel File in the "Document" Location, I need to insert the Variable String ("JobID") into a specific cell location, which I have named "JOBID" as a Range in the Excel File. Much like having a Table Name to work with, I believe this is possible but I am stumped when it comes to the parameters I need to use in "Update a Row".

I'd really appreciate some help - thanks!

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (1)

AdamGill1965 311 on at

Like (0)

Report
Copy link

Link copied!

Further to my question - this is where I am struggling:

What to use for the Key Column, where I maybe need to identify a RANGE called JOBID?
What to enter in the Key Value, where I maybe need to insert the Variable I collected earlier in the Flow?
...and what to enter into Item Properties - is this where my Variable goes that I collected earlier in the Flow so that the Range I defined will contain the Variable for the JOBID?

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (0)

Report
Copy link

Link copied!

Hi @AdamGill1965 ,

Do you want to insert a value into a certain cell in an excel file?

If so , It can be done by running Office Script (Only work for web):

1\Create new script

function main(workbook: ExcelScript.Workbook, location: string,
value: string) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range A1 on selectedSheet
selectedSheet.getRange(location).setValue(value);
}

2\My flow

Result

Best Regards,

Bof

Was this reply helpful? Yes No
AdamGill1965 311 on at

Like (0)

Report
Copy link

Link copied!

(deleted by user)

Was this reply helpful? Yes No
AdamGill1965 311 on at

Like (0)

Report
Copy link

Link copied!

I think it might be me but this is not really working. I used the script you provided in Excel Online, saved it so that it is available in my Flow and selected it within the Run Script dialogue in the Flow. However, the "location" and "value" elements do not appear after I have selected the Script Name in the Run Script dialogue. Any pointers would be appreciated - thanks!

Was this reply helpful? Yes No
AdamGill1965 311 on at

Like (0)

Report
Copy link

Link copied!

@v-bofeng-msft - thanks for the concept - I actually created a workaround for this - and it was a lot simpler than I first imagined! I can use the Excel "Update a Row" function on a Table which I have created on a separate Worksheet in my Excel file, then put in references to those Values where I want them on my Form on a different Worksheet. This, however, leads to a subsequent issue for my Flow which I hope you might be able to help with:

The Excel file name -where I need to "Update a Row" - is being dynamically created through a Variable and is therefore not available when I try to formulate the "Update a Row" elements. By that, I mean it appears that you have to enter a full path and file name for Flow to "check" the Excel file and present you with the Table Name, Key Column, Key Value and the Column Names that I am using in my Table. I tried updating with an actual file and then replacing it with the Variable name but it throws errors:

Is there a way that I can "manually" define these parameters, instead of Flow needing to "Find" them in a physical file - which does not exist?

Thanks!

Was this reply helpful? Yes No
AdamGill1965 311 on at

Like (0)

Report
Copy link

Link copied!

@v-bofeng-msft
I made progress and added JSON with variables to the Function, to the point where the Flow can run:

However, now I get a different error (progress???) - that the Table Name is not found - even though it is clearly set up in the Excel File:

Was this reply helpful? Yes No
Verified answer

AdamGill1965 311 on at

Like (0)

Report
Copy link

Link copied!

The solution - after much research:
Create a new Worksheet and put your Table in there. Use it in the standard manner with Update Excel Table. Refer the Cells in your Table from the Worksheet where you have your "Forms".

Was this reply helpful? Yes No
Davefin 24 on at

Like (0)

Report
Copy link

Link copied!

This worked for me. I wound up emptying the script out then copied V-bofeng-msft's script text from above and saved it. when I opened run script on power automate and selected my script it showed up with location and value.
Thanks man. Just incase anyone else winds up looking this up.

Was this reply helpful? Yes No