Perfectly complete an Excel Invoice Template

Alexander Cepeda says:

Thanks a lot Damien,
I just saw your answer in twitter.
Regarding my requeriment.. it is very close to your example. I have 2 Galleries in my app. Top gallery contains fields for invoice header : customer name, date , record id, status and a botom gallery that contains the detailed information from selected row at top gallery : Items, Description, Category, Pakahe Unit, Discounts, Prices, Final Price, etc. I currently have a “CreateaPdf” Powerautomate based on Html but i requiere Excel too. I’m wondering what is the best option (thinking of Excel Scripting). Re-use html as starting code or get the records from 2 Dataverse Tables i’m populating with the same records or crreate a Json from each gallery. What would you do in my case?

Thanks a lot again for your support.

Alexander Cepeda says:

Hi Damien, please is posible your help on this?. Do you have experience passing Json code from powerapps to powerautomate.
I have replicated your example but using ask PowerApps input parameter instead of Json code directly.

When i use compose action using Json code directly, the flow runs with no problem, but using input parameter from powerapps the Excel script doesn’t execute correctly and rows not filled the excel table.

This is the code i implemented to obtain data from 2 galleries and generate 2 Json codes to feed my Excel invoice using your example. The invoice header an table header populate successfully but as i mentioned the Invoice details doesn’t reflect in the table unless i use the Json code directly in compose action.

I really appreciate any help you can give me.

Regards-

ForAll(
Gallery1_2.AllItems,
If(
RegionalTimestampKey = Gallery1_2.Selected.RegionalTimestampKey,
Collect(
colCustomerHeader,
{
InvoiceID: ThisRecord.RegionalTimestampKey,
Hospital: ThisRecord.Hospital,
Distribuidor: ThisRecord.Distributor,
Validade: ThisRecord.Validity,
CNPJ: ThisRecord.CNPJ,
Estado: ThisRecord.State,
Status: Button4_2.Text
}
)
)
);
ForAll(
Gallery4.AllItems,
Collect(
colRecordDetails,
{
Codigo: ThisRecord.CodigoItem3M,
Descripcion: ThisRecord.Description3M,
DescripcionComercial: ThisRecord.CommercialDescription,
Division: ThisRecord.Division,
UnidadEmbalage: ThisRecord.PackingUnit,
QtyEmbalage: ThisRecord.PackagingQty,
ConsumoMedioMensual: ThisRecord.MonthlyAvgConsumption,
PrecioFinalHospital: ThisRecord.HospitalFinalPrice,
PrecioFinalHospitalUnitario: ThisRecord.HospitalFinalPrice
}
)
);
FillTemplateMSDBR3.Run(
JSON(
colCustomerHeader,
JSONFormat.IgnoreUnsupportedTypes & JSONFormat.IgnoreBinaryData & JSONFormat.Compact & JSONFormat.IndentFour
),
JSON(
colRecordDetails,
JSONFormat.IgnoreUnsupportedTypes & JSONFormat.IgnoreBinaryData & JSONFormat.Compact & JSONFormat.IndentFour
)
);