Unanswered

Updating Excel File with Graph API

(0) Share

Report

Posted on by solstice

Hi,

I am trying to build a flow that updates certain excel files (dynamically called) using the Graph API, since the excel connector has issues and won't do batch updates to excel files nor dynamic addressed files.

However while i have figured out a way to access the files no problem, the issue i have is to dynamically build the "batch body content" for the HTTP request.

The issue is that i will have lets say 25+ rows in a batch and the GRAPH API expects the body to be for the update of the table in the file:

{

"values" : [

[a, b, c, ... <matching columns>],

[d, e, f, ... <matching columns>],

... and so on

]

}

I can't seem to build it properly using either a "String Variable" nor a "Array Variable" as the output would be wrong.

Aka if I use a "String" Variable i can get to the body to be:

{

"values" : [

"[a,b, c, ... <matching columns>],[d, e, f, ... <matching columns>],...]"

]

}

Thus not working as it has quotation around the string and thinks it is one value and failing on column count.

On an array i may be able to get closer, but still having the same issue with using strings as a array can't seem to do a object of type array with [].

{

"values" : [

"[a, b, c, ... <matching columns>]",

"[d, e, f, ... <matching columns>]",

... and so on

]

}

Thus while being able to see entries separately, it can't define the columns do to each entry being a string.

Anyone have any ideas?

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @solstice

Could you show a screenshot of your flow how it's mapping the body?

Is that string double quotes are automatically added? If so why not use a compose action to map it.

Thanks

Was this reply helpful? Yes No
Verified answer

solstice 40 on at

Like (1)

Report
I believe i have found the solution to this, thanks to a site by Pieter Veenstra.
https://sharepains.com/2020/06/08/super-fast-update-excel-using-power-automate/

Doing things manually with Arrays or Strings will lead to a issue, i rather use the JSON input i receive into a compose and then use a select statement to create the proper output.

Example as shamelessly lifted from Pieters site shown:
Json data example
I’m going to start by building a large json list of records. It doesn’t matter too much how complex the data is, the number of records is going to be the most limiting part that I’m going to look at within this post.
I’m starting by creating a Compose action and set its content to the following json.
[ { "Title": "Title 1", "Description": "Description 1" }, { "Title": "Title 2", "Description": "Description 2" }, ... { "Title": "Title 19", "Description": "Description 19" }, { "Title": "Title 20", "Description": "Description 20" } ]
This will result in json data displayed in the compose

Example Excel file
Now that I have my json, I will prepare an Excel file in SharePoint with a table:

Now that all the prerequisites are in place it’s time to complete my flow.
Converting the Json to json
To convert my json I will configure my flow to use select action

In the above action I used the following code to get the right pieces of information for each record in my select mapping.
[ @{item()['Title']}, @{item()['Description']} ]
This now generates the follow json for me in exactly 0 seconds

This has worked for me and i hope it will help others.
Credit has to be given to Pieter Veenstra for this solution.

S.

Was this reply helpful? Yes No
pradiptanayak 12 on at

Like (0)

Report

How to insert ["68642", "47", "68662", "-", "Status At Closure", "\"Closed"] array where the last item "Status At Closure", "\"Closed" is single string, since there is a comma in it, a backslash is added to it automatically. The API might think it as 6 columns (excel table has 5 Columns) and I am getting error since there is a mismatch in Number of Columns.

Was this reply helpful? Yes No