1

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?

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.

0

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
New contributor
kappamaki is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy