Google Sheets / Apps Script - Conditional SetRowHeight / SetColumnWidth-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

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.

Sample script 1:

Sample script 2:

Note:

References: