Excel Graph API - Not Accepting JSON from Collection using JSON VBO

[Mat]

Hi,

So I've been starting to use the Graph API to update an Excel File. 

So far seems to be working as expected except when you input the JSON package it just gives me an error.

This is the JSON text: 
[{"Field1":"AB","Field2":"CD","Field3":"E1"}]

The Error Code:

Internal : Unexpected error Error during Web API HTTP Request
HTTP Status Code: 400
HTTP Response Content: {"error":{"code":"InvalidArgument","message":"The argument is invalid or missing or has an incorrect format.","innerError":{"code":"invalidArgument","message":"The argument is invalid or missing or has an incorrect

If I just include:

[ [1, 2, 3], [4, 5, 6] ] - from what Microsoft Recommends - it works. 
Create TableRow - Microsoft Graph v1.0

My test process looks like this: 


Collection:



The Action I'm trying to use:


How do I fix this, is there an alternative collection to JSON action?

[RobWoodward]

The "Collection to JSON" action is not going to do what you need as it returns a JSON array of objects in [{..}, {..}, {..}] whereas you need and array of arrays [[..], [..], [..]]. You could use the existing VB code in the "Utility - JSON" object as a starting point to build a new action or you could build something more basic just using BP loops and calculate stages, such as:


The expression for Append Row looks like this (assuming all fields are Text):

[JSON]&
"["&
""""&[Input.Field1]&""","&
""""&[Input.Field2]&""","&
""""&[Input.Field3]&""""&
"],"​

and the expression for Trim and Enclose looks like this:

"["&Left([JSON], Len([JSON])-1)&"]"

The advantage of building your own code block is that it will be faster and generic (work with any collection) but the above is a quick fix that should get you up and running.

[Mat]

Hi @RobWoodward , that's actually really helpful.

I wouldn't know how to write custom code to do this, ​and JSON is pretty new to me.  I was trying to figure out a way to amend the JSON text file that gets created but felt like I was hitting a brick wall. It would have been super useful if BPC had built some actions to go with the API integrations that had been built to account for complications like this and make them easier to use.

As for example with Excel you have to start a session and then end a session. Having said that some stuff is very straight forward, such as 'Get Manager' (Graph API - Users) stores a collection of manager info that you can just target in your process. I'm glad we've got them and rather have them than not - I just think BPC could have added a lot of value to these graph integrations.

[ewilson]

Hi Mat,

Thanks for the feedback regarding your use of the Graph integration for Excel. Let me suggest that any enhancement or feature requests you might have be submitted to the Ideas page. That way they make their way back to the dev teams. You can submit and Idea with through the Digital Exchange (DX Ideas page) or here on the Community site (Community Ideas page).

Cheers,
Eric

[Mat]

Hi @ewilson I'll submit them on the forum, as the DX Ideas page doesn't work.

If I try login it reloads the page and the content refuses to connect. Even though I'm already logged in if I try submit an idea it tells me to login. ​