2

I have a column of URLs of images that are in varying aspect ratios, I am converting them to images by using =IMAGE("url") but would like them all to have a specified height while maintaining the aspect ratio.

I'd like some white space between the rows, ie: their height would be a little larger than the image height, so the first option does not help, and I'm not aware of how to use an "auto" width using option 4.

Rubén
  • 34,714
  • 9
  • 70
  • 166
screampuff
  • 217
  • 3
  • 11
  • 1
    [This](https://support.google.com/docs/answer/3093333?hl=en) is all you can do with `IMAGE` function. If that's not enough, you may need to write a custom function in google apps script. – Adelin Feb 26 '16 at 15:16
  • I'm proposed to remove the Google Apps Script tag and to add the formulas tag because the question doesn't mention anything related to the first but instead it's about a spreadsheet formula. – Rubén Feb 26 '16 at 18:09

1 Answers1

3

Use script and formula

One possible way is using script:

  1. Find width / height with help of script
  2. Use simple image function

I did not found an easy way to get image sizes in google sheets script. So, this solution can't be used in custom formula. The only way to run the script is to do it directly, or assign it to a button. Look at example:

enter image description here

In my example file you can paste your picture's urls in column A and then click picture (button) to refresh width / height parameter. It'll take some time, depending on number of pictures.

Formula in cell C2 is: =IMAGE(A2,4,26,26*B2).

You could also make it an arrayFormula if you like, use: =ArrayFormula(IMAGE(A2:A11,4,26,26*B2:B11))

Number 26 is proper height for picture in my case, change it according to your needs.

Required Code:

function returnDims(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var lRow = sheet.getLastRow();
  var imgUrl = '';
  
  for (var i = 2; i <= lRow; i++) {
    r1 = sheet.getRange(i, 1);
    imgUrl = r1.getValue();
    if (imgUrl != ''){
      imageSize(imgUrl, i, 2);
      Utilities.sleep(2000);
    }
  }
}


function imageSize(imgUrl, r, c) {
  if  (typeof imgUrl == 'undefined') {
    imgUrl = 'http://www.google.com/intl/en_ALL/images/logo.gif';
  }
  
  var t = '';     
  t += '<script>\n';
  t += 'function hd(){\n';
  t += 'var img = new Image();\n';
  t += 'img.onload = function() {\n';
  t += "google.script.run.returnVal(this.width / this.height, " + r + ", " + c + ");\n";
  t += '}\n';
  t += "img.src = '" + imgUrl + "';\n";
  t += '}\n';
  t += 'google.script.run.withSuccessHandler(google.script.host.close)\n';
  t += '.returnVal(hd(), ' + r + ', ' + c + ');\n';
  t += '</script>\n';  
  
  
  var output = HtmlService.createHtmlOutput(t);
  output.setSandboxMode(HtmlService.SandboxMode.IFRAME); 
  SpreadsheetApp.getUi().showModalDialog(output,'please, wait...');
  output.clear();
  
}


function returnVal(h, r, c) {
  Logger.log(h);
  var sheet = SpreadsheetApp.getActiveSheet();
  var R = sheet.getRange(r, c);

  if (h != '') {
  R.setValue(h);}

}

If your pictures are big, you may need to increase the pause in this code: Utilities.sleep(2000); to some bigger number.

Referencies

I borrowed some good code from:

Hope it helps and waiting for better solutions!

Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81