Google Sheets formula that includes values from another cell

0
votes

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!!

1
jsonexcelgoogle-sheetsexcel-formula

1 Answers

0
votes

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")