Forms Attachment Data into Excel Table

[benw22]

Hello all,

I am trying to take the data in an Excel form which is uploaded via Forms and get the data into a separate, master data table (also in Excel). 

The goal of the flow is that when a party submits a new request, they can do a single item request via the form, which is easily added via the "No" path, but if there are several requests at the same time, they can fill out an Excel Table, which I would like to have automatically mapped into the same master file. 

My research so far got me to the point where I can take the Excel attachment and make it a Sharepoint attachment, and while that seems as though it COULD be waste, I am struggling to get the Sharepoint Attachment data into the Excel Table.

Thank you in advance!

[Yutao · Accepted solution]

Hi @benw22 ,

Instead of saving the form attachment to a SharePoint list attachment, can you directly interact with the submitted workbook?

That workbook should have been already uploaded to OneDrive for Business along with the form submission. So you should be able to use the "List rows present in a table" action to read out the rows out of it and then use the "Add a row into a table" action to add them into the master table.

Here is one test flow that somehow simulates this scenario:

 

Hope this helps!

Yutao

[Yutao]

BTW, at the step of "List rows present in a table", in order to input the table name for a dynamic workbook, you'll need to first click on "Enter custom value" then manually type in the table name you want (in my case, "Table1"). Otherwise it will always complains that the table name is invalid.

[Anonymous]

Hello, thank you so much for providing that answer as it already helped me a lot. What I still struggle with is the json parsing at the end. Could you explain to me where can I find the content you put into the 'Schema' field in a "Parson json for the rows data" point?

[Yutao]

@Anonymous - I recall I had to:

• Do a test run of the flow with the action (e.g. "List rows present in a table") whose result you want to parse
• Look into the test run result and copy out the returned content of that particular action
• Now get back to the flow designer. On the Parse JSON action, click on "Generate from sample", paste in the content from the step above, and click "Done".
• That should automatically generate a schema for you

Please give it a try and see if that works.

[Anonymous]

By 'returned content of that particular action' you mean the data that is in 'show raw outputs? (see screenshot for reference). Because in 'value' sections there are only two lines of text, which seems like not enough considering content of a uploaded file.

[Yutao]

Sorry it's been a while and I forgot many details that I should have included in my last response.

So imagine your table in Excel is something like this:

And for the test run result of "List rows present in a table", please go ahead and click on "Show raw outputs". And the output should be something like this:

{
  "statusCode": 200,
  "headers": {
    ...
  },
  "body": {
    ...
    "value": [
      {
        "@odata.etag": "",
        "ItemInternalId": "6dbf53b2-68fb-4905-be93-6ee5cd154616",
        "Fruit": "Apple",
        "State": "Washington",
        "Value": "7"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "57d342a7-7b50-41b4-978b-f2c9b23562c2",
        "Fruit": "Banana",
        "State": "Oregon",
        "Value": "8"
      },
      {
        "@odata.etag": "",
        "ItemInternalId": "ba822d20-c51a-40bd-9f4d-4499410f65de",
        "Fruit": "Cherry",
        "State": "California",
        "Value": "9"
      }
    ]
  }
}

You only need to copy the content of "body/value", including the square brackets "[" and "]". In our case, you should copy:

[
  {
    "@odata.etag": "",
    "ItemInternalId": "6dbf53b2-68fb-4905-be93-6ee5cd154616",
    "Fruit": "Apple",
    "State": "Washington",
    "Value": "7"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "57d342a7-7b50-41b4-978b-f2c9b23562c2",
    "Fruit": "Banana",
    "State": "Oregon",
    "Value": "8"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "ba822d20-c51a-40bd-9f4d-4499410f65de",
    "Fruit": "Cherry",
    "State": "California",
    "Value": "9"
  }
]

And for "Content" of the Parse JSON action, please choose "value List of Items" from the drop down list:

However, come to think of it, if what you really want is to iterate through all the rows in the table and read out the cells, you can directly loop through the result of "List rows present in a table" without parsing JSON. For example, the below example iterates through the rows of the table and send mobile notification with the cell values of each row. There is no need to parse JSON.

[Anonymous]

Great, I got it running now. Thank you so much for your detailed help!