时间戳

首页 > 解决方案 > 如果单元格不为空，则将公式值转换为静态值

google-apps-script - 如果单元格不为空，则将公式值转换为静态值

问题描述

我正在使用一个脚本来计算位置之间的英里数。例如，A 列 San Diego, CA 列 B 列 San Francisco 列 C 508.3。如果 A 列或 B 列是 === ""，则它将列设置为 ""。

// Note: This is not my code. 

function DrivingMeters(origin, destination) {
    var directions = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .getDirections();
    return directions.routes[0].legs[0].distance.value;
}

function DrivingMiles(origin, destination) {
  if (origin === "" || destination === "") {
    return "";
  } else {
  return DrivingMeters(origin, destination)/1609.34;
  }
}

我试图限制我进行的 API 调用的数量，因为我有很多定期添加的信息。如果任一列为空白，我采取的第一步是在 API 调用之前返回“”。我还想做的是将C列中的单元格转换为 !== "" 为静态值，将公式全部删除。提前致谢！

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-api

解决方案

  • 作为测试用例，您将自定义公式放入“C1”列，如=DrivingMiles(A1,B1).
  • 当“C”列的值不为空时，您希望将公式转换为静态值。
  • 您想使用 Google Apps 脚本实现此目的。

从您的回复评论中，我可以像上面那样理解。如果我的理解是正确的，这个答案怎么样？我认为您的情况有几个答案。所以请认为这只是其中之一。

模式一：

在这个模式中，当放自定义公式时=DrivingMiles()，当返回值不为空时，公式转换为值。

对于这种情况，我使用了简单触发器的 OnEdit 事件触发器。当您使用此脚本时，请复制并粘贴以下脚本，并将自定义公式如=DrivingMiles(A1,B1). 返回值不为空时，公式自动转换为静态值。

示例脚本：

function onEdit(e) {
  var value = e.range.getValue();
  if (e.range.getColumn() === 3 && ~e.range.getFormula().toUpperCase().indexOf("=DRIVINGMILES(") && value) {
    e.range.setValue(value);
  }
}
  • 如果要为特定工作表运行 OnEdit 事件触发器，请修改上述脚本。

模式二：

在此模式中，当运行以下脚本时，当单元格具有值和公式或只有值时，“C”列的单元格将从公式转换为值。

示例脚本：

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1, 3, sheet.getLastRow(), 1); // Column "C"
  var values = range.getValues();
  var formulas = range.getFormulas();
  var convertedValues = values.map(function(e, i) {return e[0] && formulas[i][0] || e[0] ? [e[0]] : [formulas[i][0]]});
  range.setValues(convertedValues);
}
  • 这可用于活动工作表。

参考：

推荐阅读