resize the entire rows automatically of multiple google sheet

Admin 1
Viewed 92

How to resize the entire rows of all google sheets to 21 Height ( I have approx. 10 Sheets) and i have been trying with below code but its not working.

I have below code which resize the rows according to the string but does not resize it to the Height 21.

function resizeAllrows () {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var firstrow = dataRange.getRow();
  var lastrow = dataRange.getLastrow();
  sheet.autoResizeRows(firstrow, lastrow);
}

Please have a look on below picture where above code does not work. Your help will be highly appreciated.

Don't have option for advance google services.

enter image description here

google-apps-script google-sheets-api google-sheets
1 Answers

Modification points:

  • In your script, I think that the spells of getLastrow is required to be modified to getLastRow.
  • But, from but does not resize it to the Height 21., when the font size is large and/or several text with the line breaks are put in a cell, even when autoResizeRows and also setRowHeights are used, it seems that the row height depends on the font size and the all text height. It seems that this is the current specification. So, when you want to forcibly change the row height to 21 pixels for this situation, it is required to use Sheets API.
    • The sample script for this can be seen at this thread.

When above points are reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function resizeAllrows() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var resource = {
    requests: [{
      updateDimensionProperties: {
        properties: { pixelSize: 21 },
        range: { sheetId: sheet.getSheetId(), dimension: "ROWS" },
        fields: "pixelSize"
      }
    }
    ]
  };
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}

Note:

  • When you want to use above script to all sheets in the active Google Spreadsheet, you can also use the following script.

      function resizeAllrows() {
    var ss = SpreadsheetApp.getActive();
    var requests = ss.getSheets().map(sheet => {
      return {
        updateDimensionProperties: {
          properties: { pixelSize: 21 },
          range: { sheetId: sheet.getSheetId(), dimension: "ROWS" },
          fields: "pixelSize"
        }
      }
    });
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
  }

  • When you want to use above script for the specific sheets, you can also use the following script.

      function resizeAllrows() {
    var sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names.
    var ss = SpreadsheetApp.getActive();
    var requests = ss.getSheets().reduce((ar, sheet) => {
      if (sheetNames.includes(sheet.getSheetName())) {
        ar.push({
          updateDimensionProperties: {
            properties: { pixelSize: 21 },
            range: { sheetId: sheet.getSheetId(), dimension: "ROWS" },
            fields: "pixelSize"
          }
        });
      }
      return ar;
    }, []);
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
  }

References:

user_71086530
Related