1

I have something like this on a sheet

NAME POINTS ELIGIBLE FINAL
Alice 700 YES
Bob 500 NO
Carol 300 NO
Dave 200 YES
Eve 100 YES

I need to achieve the following in column D:

  1. If it's a "NO", the final points will be the same as the original (like a placeholder) i.e. =B{row}
  2. If it's a "YES", the final points will follow the same sequence as column B, except skipping all the "NO" in the list

so in this example, the output should be like this

NAME POINTS ELIGIBLE FINAL
Alice 700 YES 700
Bob 500 NO 500
Carol 300 NO 300
Dave 200 YES 500
Eve 100 YES 300

When doing this manually, I just copy all values of B2:B, paste to D2, then apply filter "YES" to column C and paste to D2 once again. (this will paste some ugly trailing values to empty rows, but doesn't matter)

The current script

However, when doing this with script, I have to use a for loop to check column C of every row, then assign a =B{n} formula to column D, where n increases only when the current row is a YES

j = 2; //starting from the second row
  for (var i = 0; i < lastRow - 1; i++) {
    if (sheet.getRange(i+2,3).getValue() == "YES") {
      sheet.getRange(i+2,4).setFormula(`B${j}`);
      j++;
      }
    else {
      sheet.getRange(i+2,4).setFormula(`B${j}`);
      }
   }

but this also means the processing time will scale with number of rows (10,000 - 20,000ms per ~100 row), all other actions I am doing only takes ~1000ms in total, which is why I'm looking for an option that does not need iterating to save a lot of time.

I don't mind not using formula in column D as long as the value is correct.

I have tried these

get B2:B as range, set filter (C1:C text equals to "YES"), then copyTo() D2:

seq = lbSheet.getRange(`B2:B${lastRow}`);
sheet.getRange("C1:C").createFilter();
filter = lbSheet.getFilter();
criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo("YES").build();
filter.setColumnFilterCriteria(3,criteria);
seq.copyTo(sheet.getRange(`D2`),SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);

this does not paste to filtered cells only, but all cells

getValues(B2:B), set filter (C1:C text equals to "YES"), getRange(D2:D).setValues(B2:B):

seq = lbSheet.getRange(`B2:B${lastRow}`).getValues();
sheet.getRange("C1:C").createFilter();
filter = lbSheet.getFilter();
criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo("YES").build();
filter.setColumnFilterCriteria(3,criteria);
sheet.getRange(`D2:D${lastRow}`).setValues(seq);

this gives range mismatch error

Alison
  • 11
  • 2
  • [Edit] to add code for "I have tried these" – TheMaster Feb 10 '23 at 10:47
  • Also look into best practices for optimization: https://stackoverflow.com/questions/35289183/ – TheMaster Feb 10 '23 at 10:49
  • 1
    Thank you @TheMaster, I have added the code I tried and read your previous post. I will try to complete the for loop part within script instead of read/writing them one by one from the sheet and see if that cuts down on time. – Alison Feb 11 '23 at 09:43

1 Answers1

0

The problem with the OP script is that the target formula is always B${j}. But this is only true then the value is "Yes".

When the value is "No", the target formula references the same row; i.e. B${i+2}.

Lastly, the value results are pushed onto a temporary array, and setValues is only used once at the end of the script.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var lastRow = sheet.getLastRow()
  var tempArray = new Array
  j = 2; //starting from the second row
  for (var i = 0; i < lastRow - 1; i++) {
    if (sheet.getRange(i+2,3).getValue() == "YES") {
      // Logger.log("DEBUG: YES: i:"+i+", j:"+j+", range:"+sheet.getRange(i+2,3).getA1Notation()+", value = "+sheet.getRange(i+2,3).getValue()+", value range = "+sheet.getRange(i+2,4).getA1Notation()+" set value "+`B${j}`+" set value "+sheet.getRange(j,2).getValue())
      tempArray.push([sheet.getRange(j,2).getValue()])
      j++;
      }
      else {
      // Logger.log("DEBUG: NO: i:"+i+", j:"+j+", range:"+sheet.getRange(i+2,3).getA1Notation()+", value = "+sheet.getRange(i+2,3).getValue()+", value range = "+sheet.getRange(i+2,4).getA1Notation()+" set value "+`B${i+2}`+" set value "+sheet.getRange(i+2,2).getValue())
      tempArray.push([sheet.getRange(i+2,2).getValue()])
      }
  }
  // update array values to column D
  sheet.getRange(2,4,lastRow-1,1).setValues(tempArray) 
}
Tedinoz
  • 5,911
  • 3
  • 25
  • 35
  • Thank you, using B${i+2} is probably cleaner than reusing the j counter, thanks for noticing that. As TheMaster suggested, I am moving the for loop part to process inside the script like you did and cutting down on read/writing on the sheet. Although not getting rid of the for loop completely, I suppose that would still give a good performance boost. – Alison Feb 11 '23 at 09:59
  • A loop is not necessarily inefficient, it’s what you do inside the loop that matters. Looking at it again, It would be more efficient to do “getDataRange.getValues” once before the loop, then reference that variable rather than do “getRange.getValue” inside the loop. – Tedinoz Feb 11 '23 at 10:58
  • _B${i+2} is probably cleaner_ “i+2” is not a matter of being cleaner, it IS the row reference for a “No” value. – Tedinoz Feb 11 '23 at 11:03