Googlesheets API does not return Json in format Adalo understands

Get Help External Collections, Custom Actions & Collections
1

Hi All,

I am trying to use an external datasource (google sheets) to populate my app, but the format of the response from the google API does not seem to work for Adalo.

In my external collection, I use the Google API to get the data from my spreadsheet
URL: https://sheets.googleapis.com/v4/spreadsheets/{YOUR SHEET ID HERE}/A1:Z10000001

And when I test it, it works but the format is not ideal:

{
“range”: “Sheet1!A1:Z999”,
“majorDimension”: “ROWS”,
“values”: [
[
“Column Header 1”,
“Column Header 2”,
“Column Header 3”,
],
[
“AAA”,
“BBB”,
“CCC”,
],
[
“XXX”,
“YYY”,
“ZZZ”,
]
]
}

Adalo does not seem to understand this format can my external collection has no items under it aside from Row Count.
Is there a way to use google sheets api to get the desired output, which probably repeats the column header for each value in a row:

[
  "Column Header 1" : "AAA",
  "Column Header 2" : "BBB",
  "Column Header 3" : "CCC"
],
[
  "Column Header 1" : "XXX",
  "Column Header 2" : "YYY",
  "Column Header 3" : "ZZZ"
],

Any help would be much appreciated.
PS: I am trying to avoid using a rendering 3rd party like sheetsdb.io.