如果单元格不为空，则将公式值转换为静态值 [英] Convert Formula Value to Static Value if Cell is not blank

问题描述

我正在使用一个脚本来计算位置之间的里程.例如，在圣地亚哥的A列，加利福尼亚的B列，加利福尼亚的C列，列C 508.3.如果列A或列B为==="，则它将列设置为".

I am using a script that calculates the miles between locations. For example in Column A San Diego, CA in Column B San Francisco in Column C 508.3. If Column A or Column B is === "" then it sets the column to "".

// 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列中！=="的单元格转换为静态值，从而将公式全部删除.预先感谢！

I am trying to limit the number of API calls I make because I have a lot of information which is added regularly. The first step I took is returning "" before the API call if either column is blank. What I would also like to do is convert cells in Column C that are !== "" to a static value removing the formula all together. Thanks in advance!

推荐答案

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

从您的回复评论中，我可以像上面那样理解.如果我的理解是正确的，那么这个答案呢?我认为您的情况有几个答案.因此，请仅考虑其中之一.

From your reply comment, I could understand like above. If my understanding is correct, how about this answer? I think that there are several answers for your situation. So please think of this as just one of them.

在此模式中，当放置自定义公式 = DrivingMiles()时，当返回的值不为空时，该公式将转换为该值.

In this pattern, when the custom formula of =DrivingMiles() is put, when the returned value is NOT empty, the formula is converted to the value.

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

For this situation, I used the OnEdit event trigger of the simple trigger. When you use this script, please copy and paste the following script, and put the custom formula like =DrivingMiles(A1,B1). When the returned value is NOT empty, the formula is automatically converted to the static value.

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"列的单元格具有值和公式或只有值，则它们将从公式转换为值.

In this pattern, when the following script is run, the cells of the column "C" are converted from the formulas to the values when the cell has the value and the formula or only value.

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

• 这可用于活动工作表.
• 简单触发器
• getValue()
• setValue(value)

这篇关于如果单元格不为空，则将公式值转换为静态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！