= IMAGE("url")，具有指定的高度和自动宽度，以保持宽高比 [英] =IMAGE("url") with specified height and auto width to maintain aspect ratio

问题描述

我有一列具有不同宽高比的图像的URL，我通过使用= IMAGE("url")将它们转换为图像，但希望它们在保持宽高比的同时具有指定的高度. /p>

我想要在行之间留一些空白，即:它们的高度将比图像高度大一点，因此第一个选项无济于事，而且我不知道如何使用自动"宽度使用选项4.

解决方案

使用脚本和公式

一种可能的方式是使用脚本:

1. 在脚本的帮助下找到width / height
2. 使用简单的image函数

我没有找到一种简单的方法来获取Google表格脚本中的图像大小.因此，该解决方案不能在自定义公式中使用.运行脚本的唯一方法是直接执行脚本，或将其分配给按钮.看例子:

在我的示例文件中，您可以粘贴您的图片的URL在A列中，然后单击图片(按钮)以刷新width / height参数.这需要一些时间，具体取决于图片数量.

单元格C2中的公式为: =IMAGE(A2,4,26,26*B2).

如果愿意，还可以将其设置为arrayFormula，请使用: =ArrayFormula(IMAGE(A2:A11,4,26,26*B2:B11))

在我的情况下，数字26是适合照片的高度，请根据需要进行更改.

所需代码:

 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);}

} 

如果图片很大，则可能需要增加以下代码中的停顿:Utilities.sleep(2000);到更大的数字.

---

引用

我从以下网站借来了一些不错的代码

• 此处
• 此处
• 此处
• 此处

希望它会有所帮助，并等待更好的解决方案！

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.

解决方案

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:

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:

• here
• here
• here
• and here

Hope it helps and waiting for better solutions!

这篇关于= IMAGE("url")，具有指定的高度和自动宽度，以保持宽高比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！