Google表格根据文本调整文字 [英] Google Sheets Shirk Text to Fit Script

问题描述

我正在尝试使文本自动调整大小以适合Google表格的单元格.我已将像元宽度设置为特定大小，并且电子邮件和职称之类的内容可能会变得很长.

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.

我知道Google表格本身并不像Excel一样支持缩小文本以适合文本(WHY !!!!!!)"，但是我可以运行一个脚本来实现相同的目的吗?

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?

推荐答案

问题和解决方法:

不幸的是，在当前阶段，没有任何方法可以自动调整字体大小以适合电子表格服务中的单元格宽度.因此，在这种情况下，需要考虑解决方法.但是不能直接使用以像素为单位计算文本长度的方向.因为作为测试用例，当我比较根据字体大小和单元格宽度(像素)计算出的文本长度(像素)时，它们是不同的.这样，在这个答案中，我想提出一个使用其他方向的解决方法.此替代方法的基本流程如下.

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. 检索像元宽度.
2. 使用autoResizeColumn自动调整单元格宽度的大小.
3. 计算单元格的原始宽度与调整大小后的宽度之比.
4. 使用比例更改字体大小.

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.

请复制并粘贴以下脚本并设置targetRange.并运行脚本.

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);
}

• 在此示例脚本中，创建了一个临时工作表，并使用该临时工作表计算了单元格的原始宽度与调整大小后的宽度之比.
• 当toLarge为true时，如果文本长度小于单元格宽度，则文本的字体大小会变大.这样，文本长度与单元格宽度匹配.当toLarge为false时，如果文本长度小于单元格宽度，则文本的字体大小不变.
• 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.

在此演示中，单元格"A1:A6"中的文本长度为通过更改字体大小与单元格宽度匹配.在这种情况下，toLarge是true.

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.

• 在这种情况下，setFontSize(size)的字体大小为整数".这样，由于字体大小必须为整数类型，因此文本长度可能不完全相同.所以请注意这一点.
• 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.
• autoResizeColumn(columnPosition)
• setFontSize(size)
• autoResizeColumn(columnPosition)
• setFontSize(size)

这篇关于Google表格根据文本调整文字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！