Populating a gallery using Flows

[Anonymous]

I want to populate a gallery using a Flow process connected to an Excel spreadsheet in OneDrive when the 'Search' button is pressed. Is there a way to do this? I have a Flow process that can retrieve the Excel file but can not find out how to populate the gallery then. Any help is appreciated!

[R3dKap]

Hi @Anonymous,

Why not using your Excel file on OneDrive directly as a datasource for you PowerApps app?

Can you tell us more about your final goal (be as precise as possible)?

Emmanuel

[Anonymous]

HI @R3dKap,

I was originally connecting directly to a spreadsheet on OneDrive, but I was not able to update the Excel by adding a new row of data. I want to be able to add a row to the directly connected Excel spreadsheet. I originally had a Text Input for each piece of data to be added as a new row. 

Would you know how to this? 

Knowing how to do this would make my original question about Flows irrelevant! 

Thanks. 

[R3dKap]

Hi @Anonymous,

It is absolutely possible to insert rows in an Excel file located on a OneDrive and used as a datasource.

I haven't used Excel files as datasources yet, but I'm 99% sure it's just like any other datasource.

So, there are two ways to add new rows to your datasource (i.e. your Excel file):

• use an Edit Form control and connect it to your Excel file datasource, set its DefaultMode to FormMode.New and then use the SubmitForm() function to add a new row
• use the Patch() function

Emmanuel

[Anonymous]

Thank you I will try and do this now and will let you know if it works, I appreciate your help! 

Best regards

[v-xida-msft]

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to add a new record into your Excel table? Do you have no edit permission to your Excel table?

If you have enough permission to your Excel table, I think it is not necessary to fire a flow from your app, then get the returned data from the flow in your app. You could update your Excel table within your app directly.

You could consider generate an app based on your Excel table data source, then you could use the generated app to make changes to your Excel table.

More details about generating an app based on Excel table data source, please check the following article or video:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/get-started-create-from-data

https://www.youtube.com/watch?v=-Z_13J02RPU

In addition, you could also consider take a try to create a blank app using PowerApps, then add your Excel table as data source within it. Then if you want to add new data into your Excel data source, I think the Patch function could achieve your needs.

The standard formula format of Patch function as below (set the OnSelect property of a Button to following😞

Patch(
          'YourExcelTable',
          Defaults('YourExcelTable'),
          {
            Column1: "xxx",
            Column2: "xxx",
            Column3: "xxx",
            ...
          }
)

More details about Patch function in PowerApps, please check the following article:

Patch function

More details about adding a data source within an app, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/add-data-connection

Best regards,

[Anonymous]

Hi @v-xida-msft,

Thank you very much for your detailed response. So for more details, I have an Excel sheet connected to the application. This sheet has two tables: Records and Users. When a user logs in and checks the Remember Me box, the username and password are to be added to their associated columns in the table. I have each value in the text fields assigned to a variable and would like to add these values as a new row. Is this possible using the Patch function? 

Thank you kindly!

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Based on the needs that you mentioned, I think the Patch function could achieve your needs.

On your side, please consider take a try with the following formula:

Patch(
          'YourExcelTable',
           Defaults('YourExcelTable'),
           {
               Username: UserNameTextBox.Text,
               Password: PasswordTextBox.Text
           }
)

Note: The Username and Password represents the columns in your Excel table.

Best regards,

[Anonymous]

Thank you very much, this is exactly what I was looking for!