cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Write and append to an Excel file.

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.

 

2018-07-02_10-20-11.png

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

 

2018-07-02_10-25-10.png

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

 

2018-07-02_10-26-56.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

 

View solution in original post

7 REPLIES 7
Highlighted
Microsoft
Microsoft

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

 

View solution in original post

Hi Russel Thomas, 

 

 

Many thanks for your help, this is working now as expected. 

But, I was not aware of two things:

 

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

 

Thanks 🙂

 

Luminaire.

Highlighted

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

Highlighted
New Member

@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!

Highlighted

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. 

Highlighted
Frequent Visitor

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. 

Highlighted

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

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (12,844)