-1

I am using Google Sheets.

Cell A1:

=image("address.jpg")

This puts the image in a cell. To get the url from Stack Overflow produced this answer.

I created the script, and Google recognised it in autocomplete. The error I am getting is:

TypeError: Cannot call method "match" of null. (line 10).

I ran the regex through a checker, and it does get what I am looking for i.e the address, but the error seems to indicate that it's coming back with nothing.

Does this still work? Has Google changed something?

My work-around is to create two sheets and have §=image in one sheet, while in the second sheet, I remove § and use a standard Google function.

The linked solution is far better, and I'd like to implement that if I could. I cannot post a comment on the original solution's page as I don't have reputation points.

halfer
  • 19,824
  • 17
  • 99
  • 186
Bernard Black
  • 15
  • 4
  • Can you provide more information of your situation? 1. From ``TypeError: Cannot call method "match" of null. (line 10).``, I cannot understand about your current script. 2. How did you run the script? 3. In order to correctly understand your situation, can you provide a sample Spreadsheet? I think that such information will help users think of your solution. – Tanaike Feb 04 '19 at 22:36
  • @tanaike I copied the first accepted solution(Eric's) on this page: https://stackoverflow.com/questions/23750321/getting-a-google-spreadsheet-cells-image-url, and created a new script called getImageURL. I also abided his caveat that 'This won't work if the URL is computed from another cell or formula' Line 10 is 'var matches = formula.match(regex);' To reproduce: type =image("any url.jpg") into cell A1 in Google Sheets. In cell B1, I type =getImageUrl(A1). And I get the null error, which seems to indicate Regex is wrong? – Bernard Black Feb 04 '19 at 23:05
  • Thank you for replying. I modified the script for your situation. This is a simple modification. But if this was not the result you want, I apologize. – Tanaike Feb 04 '19 at 23:48

1 Answers1

1

In your situation, "null" is given as an argument. Because the formula cannot be directly retrieved by =getImageUrl(A1). By this, the error of Cannot call method "match" of null. occurs. The formula can be retrieved by getFormula(). This is also mentioned at Eric Koleda's answer. So for example, the script can be modified as follows.

Modified script:

function getImageUrl(range) {
  var formula = SpreadsheetApp.getActiveSheet().getRange(range).getFormula(); // Added
  var regex = /=image\("(.*)"/i;
  var matches = formula.match(regex);
  return matches ? matches[1] : null;
}

Note:

  • If =image("URL") is put in "A1", when you use this like =getImageUrl("A1"). Please enclose A1 by the double quotes. By this, the string of "A1" is given to the function and is used as the range.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165