Google Sheets Shirk Text to Fit Script

1
votes

I'm trying to get text to auto-resize to fit within its cell with Google Sheets. I have cell widths set to a specific size and have things like emails and job titles that can get pretty long.

I know that Google Sheets doesn't natively support Shrink Text to Fit (WHY!!!!!!) like Excel does but is there a script I can run that will achieve the same thing?

1
google-apps-scriptgoogle-sheetsgoogle-sheets-macros

1 Answers

6
votes

Issue and workaround:

Unfortunately, in the current stage, there are no methods for automatically resize the font size for fitting in the cell width in the Spreadsheet service. So in this case, it is required to think of the workaround. But the direction for calculating the length of texts in the unit of pixel cannot be directly used. Because as a test case, when I compared the text length (pixel) calculated from the font size and the cell width (pixel), those were different. By this, in this answer, I would like to propose a workaround using other direction. The base flow of this workaround is as follows.

  1. Retrieve the cell width.
  2. Automatically resizing the cell width using autoResizeColumn.
  3. Calculate the ratio of the original width and the resized width of the cell.
  4. Change the font size using the ratio.

By this flow, the text length to fit in the cell width can be automatically adjusted. When this flow is used, the sample script is as follows.

Sample script:

Please copy and paste the following script and set targetRange. And run the script.

function myFunction() {
  const autoResizeFont = (range, toLarge) => {
    const sheet = range.getSheet();
    const ss = sheet.getParent();
    const startColumn = range.getColumn();
    const endColumn = range.getColumn() + range.getNumColumns();
    const startRow = range.getRow();
    const endRow = range.getRow() + range.getNumRows();
    const columnObj = [];
    for (let c = startColumn; c < endColumn; c++) {
      columnObj.push({
        column: c,
        width: sheet.getColumnWidth(c)
      });
    }
    const tempSheet = ss.insertSheet("tempForAutoresizeFont");
    sheet.activate();
    const tempRange = tempSheet.getRange("A1");
    for (let r = startRow; r < endRow; r++) {
      for (let c = 0; c < columnObj.length; c++) {
        const srcRange = sheet.getRange(r, columnObj[c].column);
        tempSheet.setColumnWidth(1, columnObj[c].width);
        srcRange.copyTo(tempRange);
        tempSheet.autoResizeColumn(1);
        const resizedWidth = tempSheet.getColumnWidth(1);
        tempSheet.setColumnWidth(1, columnObj[c].width);
        const ratio = columnObj[c].width / resizedWidth;
        if (ratio > 1 && !toLarge) continue;
        const fontSize = srcRange.getFontSize();
        srcRange.setFontSize(Math.ceil(fontSize * ratio));
      }
    }
    ss.deleteSheet(tempSheet);
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const targetRange = "A1:A6";
  const toLarge = true;
  autoResizeFont(sheet.getRange(targetRange), toLarge);
}
  • In this sample script, a temporal sheet is created and the ratio of the original width and the resized width of the cell is calculated using the temporal sheet.
  • When toLarge is true, when the text length is smaller than the cell width, the font size of the text becomes large. By this, the text length is matched to the cell width. When toLarge is false, when the text length is smaller than the cell width, the font size of the text not changed.

Result:

In this demonstration, the lengths of texts in the cells "A1:A6" are matched to the cell width by changing the font size. In this case, toLarge is true.

enter image description here

Note:

  • In this case, the font size for setFontSize(size) is "Integer". By this, the text length might not be exactly the same because the font size is required to be the integer type. So please be careful this.

References: