(re)formatting a Json array

Hi Edwin,

After checking the Google API's JSON you have provided above it doesn't appear to be valid JSON - https://jsonlint.com/

Google's JSON

ThinkAutomation's JSON

If the format needs to be as Google are requesting then you would likely need to compose the JSON manually or scripted within a Set Variable action type.

Hello and Thanks Daniel,

I'm sorry I made a mistake and I see I pasted an extra comma with the array, it had to be his

[
["Data", "Name","Mark"],
["blabla", "John","A7"]
]

See this how Google states it: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values

Can you provide screen shots of how you are attempting to achieve this within ThinkAutomation so that we can review it please?

If you are using the Set Variable's Opertation of Convert CSV to JSON then it will always produce something like this from a CSV file as this is the valid manner to build CSV data from a spreadsheet into an array of objects. You'd likely need to script something to match Google's requirement of a line for the head and then a line for each row. It's not really something which we'd be able to support in our action types as it isn't what you'd expect to achieve with the data when converting a CSV into a JSON array.

This is a sample I made with multiple rows using what I believe to have been your approach and this is valid JSON but not for the object that Google are expecting in their API. The only route for this would be scripting using a custom written behaviour:

[
  {
    "Name": "DH",
    "Job": "TS"
  },
  {
    "Name": "LD",
    "Job": "TS"
  },
  {
    "Name": "KG",
    "Job": "TS"
  },
  {
    "Name": "OA",
    "Job": "TS"
  },
  {
    "Name": "TP",
    "Job": "DV"
  }
]