SharePoint Temporary List from Excel

Like (0) Share

Report

Posted on 8 Jun 2023 22:10:58 by JE_Forero

good morning group

I would like to know if in powerapps I can feed - delete a list periodically with the following:

1-. I have an excel file 01WMInforme on a SharePoint site that contains a sheet called Data and on the Data sheet, a Table "Table1"

2-. I have a SharePoint list "WMInforme" in which it contains some columns that I must bring from Excel - Data - Table1

Example:

-. Excel Column NombreProveedor = Proveedor Column List

-. Excel Column FechaFactura = List Column FechaFactura (in the list it is already type date)

-. Excel Column Cod_Barras = List Column RadicadoWM

-. Excel Column Usuario = List Column RadicadoPor

Among other

I already linked the table from the excel file as data source

I have an icon (button) where the idea is that:

3-. Clean the data from the WMInforme list (the ones that are there at that moment) and load the ones that are in the Excel file – Data – Table1

This need is due to the fact that the Excel file is the result of a weekly report that is extracted from accounting software and of course... this file changes every week. So the idea is that the button cleans the existing and loads the new, that is, that week's. Then with that new load, carry out other processes.

I have tried to create the button code, but I always get an error..

For example

ClearCollect(

WMInformeCollection,

ImportExcel.Table1

);

ForAll(

WMInformeCollection,

Patch(

WMInforme,

Defaults(WMInforme),

{

Proveedor: ThisRecord. NombreProveedor;

FechaFactura: ThisRecord. FechaFactura;

RadicadoWM: ThisRecord. Cod_Barras;

}

)

);

UpdateContext({ Imported: true })

In advance thank you very much for the help

Categories:

Using Apps Using Formulas

All responses (2)

Answers (1)

JE_Forero 83 on 12 Jun 2023 at 12:29:12

Like (0)

Report

Re: SharePoint Temporary List from Excel

Hi Shaheer Ahmad
Thank you very much for your answer and time dedicated to the query.
I am going to review the flow since, the premium connector, only I have as solution manager, but the users who would use the solution do not have it.
Verified answer

Shaheer Ahmad 2,194 Moderator on 11 Jun 2023 at 08:15:43

Like (0)

Report
Re: SharePoint Temporary List from Excel

To periodically feed and delete a SharePoint list in PowerApps based on an Excel file, you can follow these steps:
Create a button in PowerApps that triggers the data import process.
In the OnSelect property of the button, write the code to perform the import and update operations. Here's an example:

ClearCollect( WMInformeCollection, ImportExcel.Table1 ); ClearCollect( WMInformeToDelete, WMInforme ); ForAll( WMInformeToDelete, Remove(WMInforme, LookUp(WMInforme, ID = ThisRecord.ID)) ); ForAll( WMInformeCollection, Patch( WMInforme, Defaults(WMInforme), { Proveedor: ThisRecord.NombreProveedor, FechaFactura: ThisRecord.FechaFactura, RadicadoWM: ThisRecord.Cod_Barras, // Add other columns mapping as needed } ) ); Notify("Data imported successfully.", NotificationType.Success)

This code uses the ImportExcel function to retrieve data from the Excel file into a collection called WMInformeCollection. It also creates a temporary collection called WMInformeToDelete to store the records that need to be removed from the SharePoint list.
The ForAll function is used to iterate through the records in the WMInformeToDelete collection and remove them from the SharePoint list using the Remove function.
Then, another ForAll function is used to iterate through the records in the WMInformeCollection collection and patch (update) the SharePoint list (WMInforme) with the new data.
Finally, the Notify function is used to display a success message once the data import is completed.
Make sure to adjust the column mappings and collection names according to your specific scenario.
Note: The ImportExcel function requires the PowerApps Premium Plan or a PowerApps Per User Plan with premium data connectors. If you don't have the required plan, you can consider using other data import methods such as using Power Automate (previously known as Microsoft Flow) to transfer data from Excel to SharePoint.
Remember to test the code and make necessary adjustments based on your SharePoint list structure and Excel file format.