MS FLOW Excel Connector - Add a row into a table - Row to add into the specified Excel table - Using File ID and Json to copy rows from one spreadsheet to another

[ecooney007]

Hey Guys,

One of the things I was asked to do was to extract rows from source spreadsheets that come in as attachments, and add them to a main target spreadsheet depending on the client. There are several client target spreadsheets, and I was hoping I could dynamically enter the File Name/Path in the "Add a row into a table" Action, but I was having a tough time finding an example regarding what format the data needed to be in the "Row" field on the screen (see below);

In case anyone needed to know how to use the File Identifier (I get the File Identifier using Sharepoint Actions "Get file metadata using path", then "Get file properties", and I dynamically create the file path right before "Get file metadata using path") in the "Add a row into a table" Action, along with the "Row" field, here is what you need to do (I will assume your column names are always the same);

Basically just put an object in the "Row" field that looks like this;

What I did to have some control over the data is the following;

Normally you can get the row obj out of the 'value' from the "Apply to each" loop --> outputs('Apply_to_each')?['body/value'][0]

[
{
"@odata.etag": "",
"ItemInternalId": "903c80ee-4ef2-4b6a-a740-589c18ff614a",
"Cust No": "QUARTZ1",
"Invoice Date": "12/01/2018",
"Invoice No": "483209482",
"Item Code": "820820rewoiwo93423-0935",
"Qty Shipped": "150",
"Location Name": "QUARTZ PACKING",
"Date Paid": "",
"Ck No": "0398-2938",
"Comment": "NEW UNIT",
"Whse": "08800"
},
{
"@odata.etag": "",
"ItemInternalId": "6e0e9e0d-b2c7-4252-97fd-4d22ca76331b",
"Cust No": "QUARTZ1",
"Invoice Date": "12/01/2018",
"Invoice No": "483209483",
"Item Code": "820820ueriojorj3029-0934",
"Qty Shipped": "100",
"Location Name": "QUARTZ PACKING",
"Date Paid": "",
"Ck No": "0398-2939",
"Comment": "ADDITIONAL UNIT",
"Whse": "08801"
},...

]

But as you can see, there are some fields at the start of the object that aren't needed (@odata.etag, ItemInternalId).

What you can do here is just use the Column Names to get the values you want;

outputs(Apply_to_each')?['body/value'][0]?['Column Name']

But I have a situtation where I can't fully guarantee that the column names will be the same.

There is a way to get an array of keys or values with xpath and xml, but I decided to use some of the other text tools;

1. Get the row object in the "Apply to each" (Object {}) - outputs('Apply_to_each')?['body/value'][variables('ROW COUNTER')]

2. Put the values from the row into an array (Array []) - split(replace(replace(replace(string(variables('ROW OBJ VARIABLE NAME')),'{',''),'}',''), '\"', '"'),',')

One of the things that happens on step 2 here is that the keys and values are wrapped in escaped double quotes;

"\"Cust No\": \"QUARTZ1\"", so we need to get rid of them before we load our ROW OBJ that will we use in "Add a row into a table";

3. Create a new Object (Object {}) -->

3a. Get the Column --> first(split(string(replace(string(variables('TMP ROW ARRAY')?[variables('ROW COUNTER')]), '\"', '"')), ':'))

3b. Get the Value --> last(split(string(replace(string(variables('TMP ROW ARRAY')?[variables('ROW COUNTER')]), '\"', '"')), ':'))

Once I load the object above with only the fields I want, now I can put it into the "Rows" field like above.

There is probably a better and faster way to do this, but it works.