Google Sheets中转换公式文本为可执行公式及转置求值问题

问题描述 #

• 问题1：粘贴FORMULATEXT()返回的公式字符串（例如="I went with "& B4）时，单元格只会显示文本内容，必须手动添加=才能触发计算，如何实现自动求值？
• 问题2：转置存储公式字符串的列后，如何让结果直接作为公式执行，而非保留文本格式，达到和直接用TRANSPOSE()处理公式列一致的效果？

解决方案 #

方法1：纯公式实现 #

不需要脚本，直接用内置函数组合即可解决：

• 单个公式字符串求值：在目标单元格输入=EVALUATE("="&A1)（A1为存储公式字符串的单元格）；如果字符串本身已带=，可简化为=EVALUATE(A1)。
• 批量列求值：用ARRAYFORMULA批量处理整列：

  =ARRAYFORMULA(IF(A:A="",,EVALUATE("="&A:A)))
  

• 转置+求值组合：直接把转置和求值逻辑结合，处理C4:C6的公式字符串：

  =ARRAYFORMULA(TRANSPOSE(IF(C4:C6="",,EVALUATE(REGEXREPLACE(C4:C6,"^=","")&""))))
  

  注：REGEXREPLACE(C4:C6,"^=","")是为了兼容带=和不带=的两种字符串，统一处理后再确保公式以=开头生效。

方法2：修改AppScript实现 #

你之前的脚本用getValues()和setValues()处理的是文本值，改为处理公式逻辑即可，修改后的代码如下：

function transposeAndConvertFormulas() {
  var spreadsheetId = '你的表格ID';
  var sourceRangeNotation = 'Sheet1!C4:C6'; // 替换为你的来源范围
  var destinationNotation = 'Sheet1!D4'; // 替换为目标起始单元格

  var ss = SpreadsheetApp.openById(spreadsheetId);
  var sourceSheet = ss.getSheetByName(sourceRangeNotation.split('!')[0]);
  var destinationSheet = ss.getSheetByName(destinationNotation.split('!')[0]);

  var sourceRange = sourceSheet.getRange(sourceRangeNotation);
  var sourceTextValues = sourceRange.getValues(); // 获取存储的公式字符串

  // 将字符串转换为可执行公式：确保开头是=，已有则保留，无则添加
  var convertedFormulas = sourceTextValues.map(row => {
    return row.map(cell => {
      if (typeof cell === 'string') {
        return cell.startsWith('=') ? cell : '=' + cell;
      }
      return cell;
    });
  });

  // 转置公式数组
  var transposedFormulas = transposeValues(convertedFormulas);

  // 计算目标范围的行、列、行数、列数
  var startColStr = destinationNotation.replace(/[^A-Z]/g, '');
  var startRow = parseInt(destinationNotation.match(/\d+/), 10);
  var numRows = transposedFormulas.length;
  var numCols = transposedFormulas[0] ? transposedFormulas[0].length : 0;
  var destinationRange = destinationSheet.getRange(startRow, letterToColumn(startColStr), numRows, numCols);

  // 设置为公式而非文本
  destinationRange.setFormulas(transposedFormulas);
}

function transposeValues(values) {
  var transposed = [];
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (!transposed[j]) {
        transposed[j] = [];
      }
      transposed[j][i] = values[i][j];
    }
  }
  return transposed;
}

function letterToColumn(letter) {
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

代码核心修改点：

1. 新增字符串转公式的逻辑，确保每个内容都是以=开头的可执行格式
2. 用setFormulas()替代setValues()，让单元格识别为公式并自动计算
3. 优化了目标范围的计算方式，避免原代码中列数计算的误差

内容的提问来源于stack exchange，提问作者MMsmithH