CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
Nagaram92
Nagaram92

Reputation: 173

I need help getting this "For" loop and "If" statement to work

The entire script relies on two combinations of a For loop and an If statement. Both are essentially the same. But for some reason I can get the first section to work but not the second section.

I have included my script below with notes. Thank you.

/** @OnlyCurrentDoc */

function onEdit(e) {

//This part ensures the main script only runs if the cell F6 in the sheet "Daily Data" is edited.
  if (
    e.source.getSheetName() == "Daily Data" &&
    e.range.columnStart == 6 &&
    e.range.columnEnd == 6 &&
    e.range.rowStart >= 6 &&
    e.range.rowEnd <= 6 
  ) {

    //Section 1: This section finds the lowest # from a list of numbers by finding the lowest empty cell in column D using an If statement:
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var daily_data = spreadsheet.getSheetByName("Daily Data");
      var entirelistoftimes = daily_data.getRange(3, 4, 62).getValues();  
          for(var i=0; i<entirelistoftimes.length ; i++){  //This For loop will run through the entire D column in sheet "Daily Data".
            if (entirelistoftimes[i] == ""){        //This If statement will look for the first empty cell.
              //Copies the Total Number:
              var TotalNo = daily_data.getRange(i+2, 2).getValues();   //Gets the # from the cell next to the last filled cell in column D.
                spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Daily Data'), true);
              spreadsheet.getRange('F8').setValues(TotalNo);  //Displays the # in a cell F8 in sheet "Daily Data".
            //Stop once the first blank cell has been found:
            break;
          }
        }

    //THIS IS THE SECTION I CANNOT GET TO WORK:
    //Section 2: This section uses the # we got from the above section to find a time from a the corresponding row of sheet "Long Term Data":
      var LTD_data = spreadsheet.getSheetByName("Long Term Data");
      var LTD_data_entirelistoftimes = LTD_data.getRange(6, 2, 65).getValues();  
          for(var j=0; j<LTD_data_entirelistoftimes.length ; j++){  //This For loop will run through the Long Term Data sheet, through the list of numbers column.
            if (LTD_data_entirelistoftimes[j] == TotalNo){        //This if statement will look through column B from row 6 for the # we got from section 1 above.
              //Copies the time from column D in Lon:
              var YesterdayTime = LTD_data.getRange(j, 4).getValues();    //Gets the time from column D in row j in the "Long Term Data" Sheet.
                spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Daily Data'), true);
                spreadsheet.getRange('F9').setValues(YesterdayTime);  //Displays the time from the time underneath the # in sheet "Daily Data".
            //Stop once the above has been completed:
            break;
          }
        }
      }
}
;

Upvotes: 1

Views: 70

Answers (1)

Tanaike
Tanaike

Reputation: 201653

If your script is modified, how about the following modification?

Modification point:

  • In your script, at if (LTD_data_entirelistoftimes[j] == TotalNo){, LTD_data_entirelistoftimes[j] and TotalNo are 1 dimensional array and 2 dimensional array, respectively. Because getValues() returns 2 dimensional array. I think that the reason of your issue is to directly compare those values.

Modified script:

Please modify your script as follows.

Pattern 1:

When == is used for the comparison operator, you can modify as follows.

From: 
if (LTD_data_entirelistoftimes[j] == TotalNo){
To: 
if (LTD_data_entirelistoftimes[j] == TotalNo[0][0]){

or

if (LTD_data_entirelistoftimes[j][0] == TotalNo[0][0]){

Pattern 2:

When === is used for the comparison operator, you can modify as follows.

From: 
if (LTD_data_entirelistoftimes[j] == TotalNo){
To: 
if (LTD_data_entirelistoftimes[j][0] === TotalNo[0][0]){

Note:

  • At if (entirelistoftimes[i] == ""){, == is used as the comparison operator. So the if statement works.

References:

If this modification didn't resolve your issue, I apologize. At that time, can you provide a sample Spreadsheet? By this, I would like to modify it.

Upvotes: 2

Related Questions