Movie Genre Data Pulled into Google Sheets

Question

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.

score 2 · Accepted Answer · answered Jul 15 '21 at 18:42

2

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:

Reference:

SUBSTITUTE

answered Jul 15 '21 at 18:42

CMB

4,950
1
4
16

1

Thank you! @carlos-m Looks like this worked – Alex Fuss Jul 15 '21 at 20:53
1

Very much appreciated! :) – CMB Jul 15 '21 at 21:42

score 0 · Answer 2 · edited Jul 15 '21 at 18:34

0

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.

edited Jul 15 '21 at 18:34

General Grievance

4,555
31
31
45

answered Jul 15 '21 at 17:31

Tim

21
1
7

Movie Genre Data Pulled into Google Sheets

2 Answers2