Add Row To Table Excel Block

[varex_mike]

I'm having trouble with an automation the deletes and rebuilds an Excel file on SharePoint. This is my flow:

The "Add Row To Table" gets this error:

I'm not sure which property is wrecking it. Maybe the Row? I've entered the names of the columns of the table.

Material, Material Description, Total Open Qty, Inventory Qty, Build Qty

any ideas?

Thanks!

Mike

[priscilarubim]

Hi @varex_mike ,

That error almost always comes from the Row parameter, so your instinct is right.

The thing to know is that the Row field is not where the column names go. You already defined the headers back in the "Create Excel Table With Headers" block. The Row field wants the actual cell values for one record, passed as an array, in the same order as those headers. If you typed in Material, Material Description, Total Open Qty, ... as the Row, Excel is getting header text where it expects an array of values, and it throws exactly the InvalidArgument 400 you are seeing.

So inside your loop, the Row should be built from the current item of "Get Straight Table Data," something like:

[
  {$.item.Material},
  {$.item.Material Description},
  {$.item.Total Open Qty},
  {$.item.Inventory Qty},
  {$.item.Build Qty}
]

A few things worth checking while you are in there:

1. Count has to match. Five headers means exactly five values in the array. One short or one extra and it fails the same way.
2. Order has to match the header order from the create-table block, not the order they happen to come out of the straight table.
3. Watch for quotes and line breaks in the text columns. If Material Description ever contains a quote or a newline, it breaks the array. Wrap those values in the json() formula so they get escaped properly. There is a good Qlik support article on this called "How to Add Text Strings with Quotes obtained from Straight Table" if you want the full pattern.
4. Nulls. If a value can come back empty, send an empty string instead of nothing.

If you switch to the Add Rows To Table (Batch) block later for speed, just remember that one wants an array of arrays, [["a","b"],["c","d"]], rather than a single array. Different shape, same gotcha.

My bet is it is the column-names-in-the-Row issue. Swap those for the mapped values and it should go through. Let me know if it still trips and post what your Row field looks like.