如何在Google Apps Script中用getFormulas和setFormulas实现公式与文本字符串互转

任务A：将Sheet1的公式转为文本字符串存入Sheet2 #

原代码的问题：

• getFormulas() 返回的是二维数组，调用 toString() 会把数组转为逗号分隔的字符串，而 setValues() 要求参数必须是二维数组，导致方法签名不匹配。
• 直接存入公式字符串会被Sheet2识别为公式执行，无法以纯文本形式存储。

正确实现代码：

function saveFormulasAsText() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  
  // 获取Sheet1 A列有效数据行的公式（二维数组格式）
  const formulas = sheet1.getRange(1, 1, sheet1.getLastRow(), 1).getFormulas();
  
  // 给每个公式添加单引号前缀，强制转为纯文本
  const textFormulas = formulas.map(row => [row[0] ? `'${row[0]}` : '']);
  
  // 批量写入Sheet2 A列
  sheet2.getRange(1, 1, textFormulas.length, 1).setValues(textFormulas);
}

说明：

• 用 getLastRow() 动态获取有效数据行数，避免操作大量空白行，提升执行效率。
• 单引号前缀会让Sheet2将内容识别为纯文本，而非可执行公式。

任务B：从Sheet2读取文本字符串，恢复为Sheet3的公式 #

原代码的问题：从Sheet2获取的文本字符串带有单引号前缀，直接用 setFormulas() 会导致公式无效，需先移除前缀。

正确实现代码：

function restoreTextToFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet2 = ss.getSheetByName("Sheet2");
  const sheet3 = ss.getSheetByName("Sheet3");
  
  // 获取Sheet2 A列的文本字符串（二维数组格式）
  const textFormulas = sheet2.getRange(1, 1, sheet2.getLastRow(), 1).getValues();
  
  // 移除单引号前缀，恢复为原始公式
  const formulas = textFormulas.map(row => {
    const text = row[0];
    return text.startsWith("'") ? [text.slice(1)] : [text];
  });
  
  // 批量设置Sheet3 A列的公式
  sheet3.getRange(1, 1, formulas.length, 1).setFormulas(formulas);
}

说明：

• 通过 startsWith("'") 判断文本格式，用 slice(1) 精准移除单引号前缀，还原可执行公式。
• 保持二维数组结构，匹配 setFormulas() 的参数要求。

高效优化要点 #

• 避免使用整列范围（如A1:A），始终用 getLastRow() 锁定有效数据行，减少不必要的单元格操作。
• 优先批量读写数组，Google Apps Script中批量操作的执行速度远高于逐个单元格处理。

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