I am currently trying to write a for loop that will loop until it comes to a blank cell at which point I want the loop to end. That part is easy. Today I stumbled upon something that made it even easier, potentially. The reason I am here is because I decided to try the following which worked however, I am not sure if this is "janky code just working" or if this is the real deal as I, a no professional programmer, have never run into this simplified "not logic" syntax before.

for(var i = 1;!parseSheet.getRange(i,2).isBlank();i  )

My interest is in the "!" before the logic argument.

The point of this post is the following

1. Is what I have done here, "!" before my logic statement, really a thing or is this just some extremely simple and to the point fluke that is working well for me for some reason?

2. Can I, as a rule, treat "!" before a logic statement like this the same as "not(logic)" or at least treat it a a rule with some exceptions?

3. What are some KEY, just the save your bacon from the fire stuff, best practices?

4. What are some common pitfalls?

5. Perhaps most importantly, what is the terminology for this? What would you call it when you place a "!" before a logic statement?

I am certain that this is something basic to determine on my own except that I have found it very difficult to search for as I do not know the terminology to use. My results are a flood of "!=" results, general Boolean information, or stuff about not statements that do not look as simple as what seems to be working in my for loop. I think this would be helpful to a lot of people as I see a flood or results close to this but not quite as simplified.

I apologize if this is a repost. This is, in my opinion, complicated to try and phrase into a search query and especially so when I am not sure of the terminology regarding what it is that I am looking for.

CodePudding user response：

• ! is logical NOT operator.

• The for loop syntax is:

for ([initialization]; [condition]; [final-expression]) [statement]

All those four parameters initialization, condition, final-expression and statement are optional. You can pick and chose and combine any parameter you like, as noted in the MDN link above. The second parameter is a condition, a logical condition. So, it's syntactically valid to use a logical NOT operator to modify the logical condition as you see fit. It's just preference(a better one too).

While the logic is correct and there are no pitfalls, in this case, the condition itself(without considering the NOT !) will be making expensive calls (as TheWizEd said in the comments). If you make the same loop using arrays, it'll be better:

const values = parseSheet.getRange('B1:B' parseSheet.getLastRow()).getValues()
for(let i = 0;!(values[i][0] === ''); i  );

CodePudding user response：

This is not an answer but I was curious about the different ways of finding the first blank cell. I created a sheet with 990 rows and row 991 is blank.

1. The OP's suggested method.
2. Sheet.getDataRange().getValues()
3. Array.findIndex()
4. Range.getNextDataCell()

Code.gs

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

    let start = new Date();
    for( var i=1; !sheet.getRange(i,1).isBlank(); i   );
    let end = new Date();
    console.log("!sheet.getRange: " (end.valueOf()-start.valueOf()) " msec - i = " i);

    start = new Date();
    let values = sheet.getDataRange().getValues();
    for( i=0; i<values.length; i   ) {
      if( values[i][0] === "" ) break;
    }
    end = new Date();
    console.log("Sheet.getDataRange: " (end.valueOf()-start.valueOf()) " msec - i = " i);

    start = new Date()
    // get it again to be comparable
    values = sheet.getDataRange().getValues();
    i = values.findIndex( row => row[0] ==  "" );
    end = new Date()
    console.log("Array.findIndex: " (end.valueOf()-start.valueOf()) " msec - i = " i);

    start = new Date();
    let range = sheet.getRange(1,1,sheet.getLastRow(),1);
    range = range.getNextDataCell(SpreadsheetApp.Direction.DOWN);
    end = new Date();
    console.log("Range.getDataRegion: " (end.valueOf()-start.valueOf()) " msec - i = " range.getRowIndex());
  }
  catch(err) {
    console.log(err)
  }
}

Execution log

3:10:59 PM  Notice  Execution started
3:11:03 PM  Info    !sheet.getRange: 3022 msec - i = 991
3:11:03 PM  Info    Sheet.getDataRange: 153 msec - i = 990
3:11:03 PM  Info    Array.findIndex: 148 msec - i = 990
3:11:03 PM  Info    Range.getDataRegion: 189 msec - i = 990
3:11:03 PM  Notice  Execution completed