topic Re: Excel Graph API - Not Accepting JSON from Collection using JSON VBO in Digital Exchange

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

Mat — Wed, 18 Aug 2021 16:53:33 GMT

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?

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

RobWoodward — Mon, 23 Aug 2021 14:57:40 GMT

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.

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

Mat — Tue, 24 Aug 2021 09:11:00 GMT

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.

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

ewilson — Wed, 25 Aug 2021 11:29:02 GMT

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

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

Mat — Fri, 03 Sep 2021 16:12:53 GMT

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.