CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Brandon
Brandon

Reputation: 97

Convert Formula Value to Static Value if Cell is not blank

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

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!

Upvotes: 1

Views: 2324

Answers (1)

Tanaike
Tanaike

Reputation: 201338

  • As a test case, you put a custom formula to the column "C1" like =DrivingMiles(A1,B1).
  • When the value of the column "C" is NOT empty, you want to convert the formulas to the static value.
  • You want to achieve this using Google Apps Script.

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.

Pattern 1:

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.

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.

Sample script:

function onEdit(e) {
  var value = e.range.getValue();
  if (e.range.getColumn() === 3 && ~e.range.getFormula().toUpperCase().indexOf("=DRIVINGMILES(") && value) {
    e.range.setValue(value);
  }
}
  • If you want to run the OnEdit event trigger for the specific sheet, please modify above script.

Pattern 2:

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.

Sample script:

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);
}
  • This can be used for the active sheet.

References:

Upvotes: 2

Related Questions