0

Sorry if this is a bit newbie.

What I'm trying to do is take a column and delete the values in some of its cells if they do not contain a formula. 

function clean() {
  var ss = SpreadsheetApp.openById("KEY_REMOVED_FOR_OBVIOUS_REASONS");
  var sheet = ss.getSheetByName("TEST");
  var limit = sheet.getMaxRows();


  for(var i = 6; i < limit; ++i){                                  // Declares variable "i", which represents the row we are currently checking. Basically says "if our row isn't the last one, execute this block then add to i".
    var range = sheet.getRange(i,2);
    if (range.getValue() != "") {                                  // Leverages the fact that .getValue() cannot return functions. If our range is blank, execute this block. 
      range.clear();
    }
  }
}

I established a loop which checks every row one by one. What I'm wondering is, is there a more efficient way to do this than having to call the server every time I do the loop?

getValues() exists of course, but I'm not sure how one might interact with only the desired cells returned by that.

LiamJC
  • 99
  • 1
  • 1
  • 11
  • 1
    you'll have to loop, no getting around that. but why aren't you using getFormula() instead of relying on if getvalue returns functions or not? explained here: http://stackoverflow.com/questions/15673038/how-do-i-copy-a-row-with-both-values-and-formulas-to-an-array – Sujay Phadke Feb 03 '16 at 20:49
  • @SujayPhadke I'm not really looking to see if there's a formula. I'm looking to see if there's any content that is _not_ a formula with the intent of removing it. I suppose I could clear everything that doesn't have a formula, but afaik that's a different way of doing the same thing - no? – LiamJC Feb 03 '16 at 21:10
  • 1
    its the same thing. if you use `getFormula.length() != 0` it'll give you content that is not a formula. its better to rely on inbuilt methods designed to do that job, rather than relying on the behaviour of another method, not designed for that job. if tomorrow, google changes the behaviour of getValue() to indeed return a function, your method will fail but the method i mentioned will still work seamlessly. – Sujay Phadke Feb 03 '16 at 21:20
  • @SujayPhadke ahh okay, that makes sense. I will apply that logic instead, then - thanks! – LiamJC Feb 03 '16 at 21:33
  • 1
    Regarding the original question about too many calls: I would split the process in getting the whole range, filtering the indices where the position of `getFormula()` is empty and then iterate over the range as it saves one `getRange()` and one `getFormula()` request per formula containing cell which speeds up the execution. – Robin Gertenbach Feb 03 '16 at 21:39

2 Answers2

1

I did some testing. .getValues() will return the computed values if there are formulas.

The following code works for me.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getLastRow();
  var range = sheet.getRange(1,1, rows);
  var values = range.getFormulas();
  range.clear();
  range.setFormulas(values);
}
daniel
  • 638
  • 4
  • 14
0

Short answer

Regarding the too many calls issue, use getLastRow() instead of getMaxRows() and use the returned value (number of rows) to get the required range in just one call.

Explanation

  • getLastRow() returns the position of the last row that has content while getMaxRows() returns the maximum height of a sheet regardless of content.

  • getRange(row, column, numRows) returns in just one call the complete range.

Community
  • 1
  • 1
Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Ahh, getMaxRows() is a good idea. Thanks for that. As for getting the whole range, I've known how to do that portion, but I have trouble retroactively clearing cells based on the data from that one range. For instance, I can get the whole range, but if I try to 'clear' based on that range - it clears the whole thing. – LiamJC Feb 04 '16 at 13:52