I'm not 100% sure if this is a fit for your needs, but Redash[1] (open source, but there is a hosted version too) you can query spreadsheets and APIs (within limits).

Our JSON data source is not merged yet [2], although available already on the hosted version.

You can see details here: https://redash.io/help/data-sources/querying/urls#JSON

[1] https://redash.io/ [2] https://github.com/getredash/redash/pull/3805

(I'm the creator of Redash, now CEO of Redash the company)