Setting minimum row height

Question

In the past, I used to open a new spreadsheet, resize all lines (the default feels way too cramped for me), and then enter data. If at some point I entered data longer than the row height, it would stretch the entire row automatically.

So basically, it would keep the "Fit to data" row height setting, while letting me manually define the "default" height (height when no text is present).

Not sure what changed, but now whenever I resize lines manually, it disables the "Fit to data" checkbox, so whenever I enter data longer than my line height, it won't automatically adjust. If I return the "fit to data" setting, it will shrink all empty or single-line lines to the default, too-small height.

Did something change? Am I missing something? Is there a way to set a minimum line height but still keep the "fit to data" mode?

Here's an example of a spreadsheet I have where everything works as I want it - All rows are at least 30 pixels high, but when adding data it expands if two lines or more.

spreadsheet sample

Answer 1

There is a quite hacky solution that I like to use to set a certain min row height for my spreadsheets. Follow the steps below to reproduce the result.

1. Add a new column to the sheet
2. Fill each row in the column with one letter, number, ...
3. Set the column height to "Fit to data"
4. By changing the text size of all the cells in the new column you can now adjust the minimum row height of all your rows
5. (optional) Hide the contents of the column by having the same text and background color (Hiding the whole column will remove the min row height)

Answer 2

I had a similar issue, one spreadsheet has a Row Height of 35, yet is set to "Fit to data" and I couldn't figure out how to replicate it. After searching and finding no answers, I looked back on the history of the sheet and discovered was created using a Template, specifically the "To-do list" template, which has the larger height and Fit to data set by default.

If yours was created using a Template, this could be where the setting came from.

So while there does not seem to be any way to manually set a min-height, some templates have it. Unfortunately, it is not a style you can copy between Spreadsheets. However, you can do the following to create a new Spreadsheet then copy your values over.

1. Find a Template with the desired height
2. Create a new Spreadsheet with that Template
3. Add an empty sheet (Sheet2) that doesn't have all the Template formatting you don't want
4. Copy an empty row from Sheet1
5. Select all rows on Sheet2 and use Paste special > "Paste format only"
6. Delete Sheet1
7. Copy data from existing sheet into new one

Answer 3

The answer is no. You can either have:

• fixed rows (custom hight / 'fit to data' hight)
• floating rows (default hight)

Answer 4

My solution is the same as the first answer, but with an array formula.

I set the format of text size in column A to the size I like and then put this array formula {=ARRAYFORMULA(if(C2:C="","","24"))} in cell A2 to fill the column with text of that size.

Answer 5

2023 - I did not find any direct solution to this, but I combined what I found in another answer (https://webapps.stackexchange.com/a/166556/313353) with more stuff:

function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  autoChangeRowHeight_(e);
  autoColumnResize(e);
}

function autoChangeRowHeight_(e) {
  const sheet = e.range.getSheet();
  if (e.value) {
    sheet.autoResizeRows(e.range.rowStart, 1); /** when edited, set to autosize */

    if (sheet.getRowHeight(e.range.rowStart) < 40) {
      sheet.setRowHeight(e.range.rowStart, 40); /** SET a row height, if the autosize is below 40 (i.e. default of 21) */
    }
  } else { /** WHEN BLANK */
    sheet.setRowHeight(e.range.rowStart, 40); /** when BLANK, set the row height to 40 */
  }
}

function autoColumnResize(e) {
  if (e.range.columnStart === 2) { /** Column B -- if edited, skip */
    return;
  }

  const sheet = e.range.getSheet();
  sheet.autoResizeColumns(e.range.columnStart, 1)
}