Answered

Write and append to an Excel file.

(1) Share

Report

Posted on by Luminaire

Hi everyone,

We just have been deployed to O365 in my organisation and I found the possibility of PowerApps. So please be kind regarding my question as I'm (still) very new to this new app creation system.

I'm currently creating an app that allows our Users to click on smileys to give us their satisfaction.

On the second screen, they have to enter their name and click on the submit button.

The thing is that I would like to record their inputs in an excel file (already created and linked with table)

I've looked around the web and found Update & UpdateIf functions but I'm unable to make them work, especially to fill the Excel file and append to it whenever a User fills the survey.

Thanks a lot for your help in advance.

Let me know if you need more.

Thanks,

Luminaire.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

RusselThomas 4,014 on at

Like (5)

Report
Hi Luminaire,

I think you're looking for the Patch function?
Word of advice though avoid spaces in column names if you can - but assuming your text input is called "TextInput" and your name column in Excel is called "NameColumn";

For a new capture you want;

Patch(ExcelSource, Defaults(ExcelSource), {NameColumn: TextInput.Text, SurveyResultColumn: Value})
where ExcelSource is the name of your Excel connection, and Value is whatever you're using to store the smiley selection.
"Defaults" tells patch you want to create a new record and to use the Default data properties for each column in the table - this record will automatically be appended to the end of the Excel table.

If you wanted to update an existing record, you'd need to tell it which record to update first - so assuming that the name is unique, you could do the following;

Patch(ExcelSource, LookUp(ExcelSource, NameColumn=TextInput.Text), {NameColumn: TextInput.Text, SurveyResultColumn: Value})

Hope this helps,

RT

Was this reply helpful? Yes No
Luminaire 7 on at

Like (0)

Report
Hi Russel Thomas,

Many thanks for your help, this is working now as expected.
But, I was not aware of two things:

When I write in my Excel file, it's automatically adding a _PowerAppsID_ column. I've hiden it, no worries.
The App cannot write in the Excel file if it's currently open. Any way to prevent this?

Thanks 🙂

Luminaire.

Was this reply helpful? Yes No
RusselThomas 4,014 on at

Like (1)

Report

Hi Luminaire,

The _PowerAppsID_ column is needed by PowerApps to uniqely identify and index the rows of the Excel table, so you can hide it from a view as long as it stays in the table.

Excel files are always locked for other users when opened in the Excel client - but I think if you open the file using the Excel web app it doesn't lock. That said, it could start getting troublesome with lots of people connecting via the PowerApp while others are viewing the file and/or editing rows via the Excel web app.

This is unfortunately the nature of Excel, not something PowerApps can control.

To avoid issues, you may want to use the CDS or a SharePoint list as your data source instead - much more versatile and no locking issues.

Kind regards,

RT

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@Luminaire Can you make your app publicly available? I've looked all around for such an app, and have not been able to find much! Thanks!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello,

I'm trying to do a similar thing.

Patch(Table2, Defaults(Table2),{Region:Dropdown1,Country:Dropdown1_1,Metro:Dropdown3,IBX:Dropdown4,Location:Dropdown2,UtilityLoad:TextInput2,CustomerLoad:TextInput2_1});

I keep getting an error message when I press the save button: Am unexpected error occurred.

Was this reply helpful? Yes No
SBS 80 on at

Like (0)

Report

To get the value of a dropdown you should use: Dropdown1.Selected.Value where Dropdown1 is the name of your dropdown control. If you put a dot(.) after the controls name you should see the different value options.

Was this reply helpful? Yes No
Marc_Jones 2 on at

Like (0)

Report

Just wanted to add my thanks. I've had the same issue for a couple of hours and this really helped me too.

Was this reply helpful? Yes No
Shahin1 42 on at

Like (0)

Report

........

Was this reply helpful? Yes No