How to display Image from URL in Google Sheets? (and apply it to multiple rows)

Question

So I created a list of members in Google Sheets, each member having a profile image, name, location, etc.

I exported this list from import.io, a data extraction tool. The only problem is that when I export it, I get the profile image url for each member, instead of getting the image itself as a thumbnail.

Someone asked a similar question here. I tried using the =image("url") function and it worked, I can see the image.

What I want is a simple way of applying this function to the 200 cells I have that include a link to an image.

Any idea on how this can be achieved?

Answer 1

Presumably, the image URLs will be found in the same column; otherwise you have bigger issues with your spreadsheet. Here is a script that converts the URLs from Column C to images.

function convertToImage() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange(); 
  var colnum = 3;             // assuming column C; change to another column number
  var cell;
  var values = range.getValues(); 
  for (var i=0; i<values.length; i++) {
    if (values[i][colnum-1]) {
      cell = range.getCell(i+1, colnum);
      cell.setFormula('=image("'+values[i][colnum-1]+'")')
    }
  } 
}

Further refinements are possible: extend the range to multiple column (iterate over values[i][j]), check whether the content is indeed a link (if statement for values[i][j]), etc.

Answer 2

I recently had to do this and was able to use search/replace tools.

Select the cells with image URLS.

Edit -> Find and replace (ctrl+H)

Search expression: (.*)

Replace expression: =IMAGE("$1")

Check boxes:

• Match entire cell contents
• Search using regular expressions