CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Scorb
Scorb

Reputation: 1900

function that works on specific columns in arbitrary row

How would one write a function in google sheets that would iterate over all the rows of a sheet. Within the current row, it would populate column A with the output of a math function applied to data in 3 other specific cells in the row (call them b,c,d even though they may not be in those specific columns).

Upvotes: 0

Views: 79

Answers (1)

Bardy
Bardy

Reputation: 2170

Here's an example. You can see usage in example(): applyFuncToRowsWithIndexes expects as the first argument your math function, then as argument 2 - 4, each of the three column indexes.

The math function, in turn, should expect a row as its first argument, and the indexes of the columns as arguments 2 - 4. The math function should update the row index 0, to update column A.

function example() {
  // Apply 'testMathFunc' to each row, with the three columns being
  // B, C and D (ie. index 1, 2 and 3).
  applyFuncToRowsWithIndexes(testMathFunc, 1, 2, 3);
}

// Function loops through each row applying specified function 'mathFunc' to each row
function applyFuncToRowsWithIndexes(mathFunc, colIndex1, colIndex2, colIndex3) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    var row = values[i];

    // MathFunc should take 4 args:
    // The current row, and the indexes of the 3 columns used in the calculation
    mathFunc(row, colIndex1, colIndex2, colIndex3);
  }
  range.setValues(values);
}

// An example 'mathFunc' - testMathFunc, which makes column A simply
// the sum of B, C and D.
// Substitute with your own maths function
function testMathFunc(row, colIndex1, colIndex2, colIndex3) {
  row[0] = row[colIndex1] + row[colIndex2] + row[colIndex3];
}

Upvotes: 1

Related Questions