unable to run for loop in google script

Question

I am trying to run a for loop but to no success. what I am trying to do is,I have a sheet with 1 in the entire column, I need to find the first row in which 1 appears and then blank out 25 cells from it.

 var values = sheet.getRange(2, 3, sheet.getLastRow(),sheet.getLastColumn()).getValues();
        for(var i = 0;i<values.length; i++){    
    if(values[i][0] == 1 ){
  Logger.log(i);
  break;
}  }
  for(var j = i;j<i+24; j++){
    sheet.getRange(j,3).setValue("");}

however, nothing happens. I do not know where am I going wrong.

Marios · Accepted Answer · 2021-01-05 05:22:23Z

Explanation:

Your goal is to find the first element in the column that is 1 and then clear the content of 25 cells after that in the same column.

You don't need a for loop to achieve your goal. You can use findIndex.

Since you are interested only in one column, grab that column only and use it as an 1D array; flat will convert the 2D array to 1D array:

sh.getRange(start_row,3,sh.getLastRow()).getValues().flat();
Then use findIndex to find the first 1 in this array:

const index=values.findIndex(v=>v==1) + start_row;

and add 2 (start_row) since you start counting from the second row.
Finally, you don't need a for loop to clear the content of the cells. You can select the desired range and clear the content with clearContent in one go:

sh.getRange(index,3,25).clearContent();

Solution:

function myFunction() {
 const ss = SpreadsheetApp.getActive();
 const sh = ss.getSheetByName('Sheet1'); // use the name of your sheet here
 const start_row = 2;
 const values = sh.getRange(start_row,3,sh.getLastRow()).getValues().flat();
 const index=values.findIndex(v=>v==1) + start_row;
 sh.getRange(index,3,25).clearContent();
}

same solution in the 3 lines of code if that matters to you:

function myFunction() {
 const sh = SpreadsheetApp.getActive().getSheetByName('Sheet1'); // use the name of your sheet here
 const index = sh.getRange(2,3,sh.getLastRow()).getValues().flat().findIndex(v=>v==1) + 2;
 sh.getRange(index,3,25).clearContent();
}

Collectives™ on Stack Overflow

unable to run for loop in google script

1 Answer 1

Explanation:

Solution:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation:

Solution:

Comments

Your Answer

Sign up or log in

Post as a guest

Related