Google Sheets / Apps Script - Conditional SetRowHeight / SetColumnWidth

Question

In Google Sheets, I am trying to sort out how to set the height and width of a specific cell to 180x180 only IF another cell in that row contains a value.

Conditional Formatting doesn't do width/height, and even when I set the image height and width with =IMAGE("image_url",4,180,180), the "Fit to Data" doesn't respect the image size.

Is there any way to handle this with App Script?

The use case: I am scraping a webpage to look for product images for a shopping list. If I find an image URL, I show the image, and want to size it, but don't want the rows without images to be 180px high.

--- UPDATE ---

Thank you. I have resorted to the following, which I don't hate:

I have a checkbox labeled "Enlarge Images?" at the top of the sheet (in T16). The rendered image lives in R19, and the following logic in an adjacent 'empty' cell:

=if(AND(R19<>"",$T$16=TRUE),"







","")

This checks that there is an image defined and that the "enlarge images?" option is checked. If both pass, it inserts a number of linebreaks (Alt+Enter), which Resize » Fit to Data recognizes, so the row resizes accordingly. It also gives me the ability to shrink the images back down when I don't need to see them.

Answer 1

You can try to use auto resize:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
sheet.autoResizeRows(1, sheet.getLastRow());

References

• autoResizeRows(startRow, numRows)

Answer 2

In your situation, how about the following sample scripts?

Sample script 1:

In this sample script, the formula like =IMAGE("image_url",4,180,180) is put to a cell and the cell height and width are changed using Google Apps Script. In this case, the formula is put to a cell "A1" and the cell height and width are changed.

function sample1() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const cellA1Notation = "A1"; // Please set the cell range as a1Notation.
  const imageUrl = "###"; // Please set the image URL.
  const imageWidth = 180; // Please set the width you want.
  const imageHeight = 180; // Please set the height you want.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const image = `=IMAGE("${imageUrl}",4,${imageHeight},${imageWidth})`;
  const range = sheet.getRange(cellA1Notation);
  range.setFormula(image);
  sheet.setRowHeight(range.getRow(), imageHeight).setColumnWidth(range.getColumn(), imageWidth);
}

Sample script 2:

From January 19, 2022, the image can be directly put to the cells using Google Apps Script. When this is used, the sample script is as follows. In this case, the image is put to a cell "A2" and the cell height and width are changed.

function sample2() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const cellA1Notation = "A2"; // Please set the cell range as a1Notation.
  const imageUrl = "###"; // Please set the image URL.
  const imageWidth = 180; // Please set the width you want.
  const imageHeight = 180; // Please set the height you want.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const image = SpreadsheetApp.newCellImage().setSourceUrl(imageUrl).build();
  const range = sheet.getRange(cellA1Notation);
  range.setValue(image);
  sheet.setRowHeight(range.getRow(), imageHeight).setColumnWidth(range.getColumn(), imageWidth);
}

Note:

• These are simple sample scripts. So please modify them for your actual situation.

References:

• setRowHeight(rowPosition, height)
• setColumnWidth(columnPosition, width)
• Class CellImageBuilder