How do I change the "Resize row" setting from "Fit to data" to "Specify row height, no really, I want this row to be 21 pixels tall no matter what"?

Question

I've tried:

var ss = SpreadsheetApp.openById('1-hcd6JWmWZ9MrSkt2_N8PXDGz4j-my-sheet-id');
var sheet = ss.getSheetByName('test');
sheet.setRowHeight(1, 21);

This works if the row is empty, which is useless since why would I have an empty row at the non-default height.

However, even though it changes the row height, if you manually bring up the "Resize rows..." dialog, it's still set to "Fit to data" instead of "Specify row height".

This is pretty frustrating when combined with a couple other facts about Sheets. No matter what your text wrap settings are, row heights will always "Fit to data" if you paste or setValue(). And, if the row already contains data, even if you explicitly setHeight() on the row, Sheets will refuse to actually change the height, instead letting "Fit to data" always take precedence if it would adjust the row height.

I can't find any way to automate setting row data and also keeping nice, clean, compact, efficient rows heights of 21 or so.

Answer 1

• You want to set the row height to 21 pixels.
• You want to set the condition of row height to "Specify row height" instead of "Fit to data".
• You want to use Google Apps Script.

If my understanding is correct, how about using Sheets API? In my environment, also I could confirm that when the script of sheet.setRowHeight(1, 21) is run, the condition of row height changed to "Fit to data". In order to resolve this issue, I would like to use Sheets API. The sample script is as follows.

When you use this script, at first, please enable Sheets API at Advanced Google Services.

Sample script:

In this sample script, the height of row 1 of test sheet in the Spreadsheet of 1-hcd6JWmWZ9MrSkt2_N8PXDGz4j-my-sheet-id changes to 21 pixels with "Specify row height".

var spreadsheetId = "1-hcd6JWmWZ9MrSkt2_N8PXDGz4j-my-sheet-id";
var sheetName = "test";

var ss = SpreadsheetApp.openById(spreadsheetId);
var sheetId = ss.getSheetByName(sheetName).getSheetId();
var resource = {
  "requests": [
    {
      "updateDimensionProperties": {
        "properties": {
          "pixelSize": 21
        },
        "range": {
          "dimension": "ROWS",
          "sheetId": sheetId,
          "startIndex": 0,
          "endIndex": 1
        },
        "fields": "pixelSize"
      }
    }
  ]
};
Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);

Note:

• As an additional information, when you want to use "Fit to data", you can achieve it using AutoResizeDimensionsRequest of spreadsheets.batchUpdate.

References:

• Advanced Google Services
• spreadsheets.batchUpdate
• UpdateDimensionPropertiesRequest
• AutoResizeDimensionsRequest

If I misunderstood your question and this was not the result you want, I apologize.

Answer 2

sheet.setRowHeightsForced(startRow, numberRows, pixels); is what you want

that craziness above didn't work for me. Im sure it does if you know how to use it but this is much simpler.

so your code would look like this:

   var ss = SpreadsheetApp.openById('1-hcd6JWmWZ9MrSkt2_N8PXDGz4j-my-sheet-id');
   var sheet = ss.getSheetByName('test');
   sheet.setRowHeightForced(1, 1, 21);