CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-apiomdbapi
Alex Fuss
Alex Fuss

Reputation: 125

Movie Genre Data Pulled into Google Sheets

I want the genres associated with a list of movies in Google Sheets. I am attempting to pull this info via the importxml formula with the OMDB API. However I keep getting an error.

Here is a sample sheet for reference.

I have a key for the API but that is not included in here. The error still appears to show even with the key included.

Any help with this formula or process would be much appreciated.

Or if there is a Google Apps Script that can pull this off I'd love to be pointed in that direction.

Upvotes: 1

Views: 676

Answers (2)

CMB
CMB

Reputation: 5163

Solution:

Based on the API documentation you would need to replace spaces in the title name with +, so your formula would be like this:

=IMPORTXML("http://omdbapi.com?apikey=MYKEY&t="&SUBSTITUTE(A2," ","+")&"&r=xml","root/movie/@genre","en_US")

Of course replace MYKEY with your API key, but the other parts of the request look like this:

enter image description here

Reference:

SUBSTITUTE

Upvotes: 2

Tim
Tim

Reputation: 21

This is your current formula:

=IMPORTXML("http://omdbapi.com?apikey=MYKEY&t="(A3)"&r=xml","root/movie/@genre");

Try using this instead:

=IMPORTXML("http://omdbapi.com?apikey=MYKEY&t=(A3)&r=xml","root/movie/@genre","en_US")

I think this has something to do with concatenation of the formula string.

Upvotes: 0

Related Questions