strudelhouse strudelhouse - 3 months ago 19
JSON Question

Google Sheets formula that includes values from another cell

In Google Sheets, I have a formula in a cell that connects to an API with a script and spits out the JSON results. For example, I can connect to the Youtube API with a formula that looks like this,

=ImportJSON("https://www.googleapis.com/youtube/v3/videos?id=mv-cj6mBkPk&key=API KEY&fields=items(id,snippet(channelId,title,categoryId),statistics)&part=snippet,statistics")

I'd like to make part of that formula get it's value from a separate cell. For example, the video ID above (mv-cj6mBkPk), rather than have it typed in the above formula, I'd like to pull in the value from a cell (i.e. B1). Then I can create multiple versions of this formula with other video IDs (i.e B2, B3, etc.)

I've tried combining various parts of the formula through CONCATENATE-ing a few cells, but that doesn't seem to 'RUN' the formula, it just shows it.

My skills aren't so advanced in this area, so any help would be great. Thanks!!

Answer

It is not obvious what your problem is exactly, or where you got the code for the ImportJSON() function (as this is not a built-in function), but a variation of the below SHOULD work - if it does not, then post what error you are getting (exactly).
Also, I presume you have substituted "API KEY" for the actual key... I have used API_KEY, so that the formula does not get broken across multiple lines here.

A1 : mv-cj6mBkPk
A2 : =ImportJSON("https://www.googleapis.com/youtube/v3/videos?id="&A1&"&key=API_KEY&fields=items(id,snippet(channelId,title,categoryId),statistics)&part=snippet,statistics")