CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
WonderCsabo
WonderCsabo

Reputation: 12207

Copy and return modified range in a function

My scenario is this: i want to write a function which can remove the 3 lowest element from a range, and returns the modified range to be processed in Google Spreadsheets. I'm totally new to Google App scripts, so my very bad code is here:

function removeWorse(range, n) {

  var data = [];

  for(var i = 1; i <= range.getNumColumns(); i++) {
    data.push(range.getCell(1, i).getValue());
  }
  data.sort();

  var range2;
  range.copyTo(range2);

  for(var i = data.length - 1; i >=n; i--) {
    range2.getCell(1, i).setValue(data[i]);
    Logger.log(2);
  }

  return range2;

}

It gets stuck in line 11: copyTo does not exist.

How can i modify this code to actually do my task? :)

Upvotes: 0

Views: 801

Answers (2)

AdamL
AdamL

Reputation: 24609

I would recommend passing a range to the function as an array of values (so in a spreadsheet cell you would enter eg =removeWorse(A1:K1;3)), modifying that range with Javascript, and then returning the modified range. This avoids any get or set calls which are problematic when used in custom functions. It seems as though you are working with a horizontal range, so something like this should work:

function removeWorse(values, n) {
  values[0].sort();
  values[0].splice(0, n);
  return values[0].reverse();
}

But note that sort() used without an argument will return a lexographic sort (eg 9 will be considered "larger" than 81). You can use a simple function as an argument in sort() to address this:

function removeWorse(values, n) {
  values[0].sort(function(a, b) {return a - b});
  values[0].splice(0, n);
  return values[0].reverse();
}

Upvotes: 3

Kalyan Reddy
Kalyan Reddy

Reputation: 1142

The problem with your code is that the range.copyTo function expects another Range as a parameter. When you define range2 above, it is not a Range object.

Upvotes: 0

Related Questions