Google Sheets: Cache IMPORTXML data

Question

So I am trying to just create a simple Movie list for myself and want to keep the IMDB/Meta/RT scores in a cell.

I have it working fine with something like this:

=IF(ISBLANK(A2),,IFERROR(importxml(("http://www.omdbapi.com/?apikey=**MYKEY**&t="&A2&"&r=xml&tomatoes=true&y=2018"),"root/movie/@imdbRating")/10))

This will return a value that I can keep in the cell. The PROBLEM is that it doesn't always refresh. Either google sheets is bugged, importxml, or the omdbapi. I have read others having the same issue.

A typical row looks like this:

[Black Panther] [February 16, 2018] [86%] [74%] [97%] [88%]

Since my data does not change much I wanted to somehow cache it. So if the importxml fails it won't blank out the cell, it will just keep whatever the last value was. Maybe I can do this by referring to another tab's cell? I did try that, but as soon as the other tab cell gets blanked out(because the importxml fails) so does the main tab's cell.

Thoughts?

score 0 · Answer 1 · answered Oct 01 '18 at 16:29

I believe this requires an apps script that will add each change you make to row 1, to a corresponding target column 2 - if not empty/NA:

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if(sh.getActiveCell().getColumn() == 1) {
    if(e.value && e.value != '#N/A')
      sh.getRange(e.range.rowStart, 2).setValue(e.value);
  }
}

Goto Tools, Script Editor, from the menu and add the script. Make sure to run it once in the debugger and grant the required permissions.

Also, take note of this references:

Google Sheets: Cache IMPORTXML data

1 Answers1