forEach loop in google scripts

Question

I had a function that did what I wanted for one row at a time. Now I want to apply that to multiple rows so I'm trying to use a forEach loop. I'm not throwing errors, but I'm also not getting what I want! (Nothing is output to the cell, and Logger.log only shows Range)

I'm trying to compare a list (from a row) of jobs employees worked the week against the main list of jobs available and then return just the list of worked jobs back to the spreadsheet in a specific cell.

The row in the spreadsheet has more than just the jobs in it, which is why I pull every third item. Some cells have more than one job code in them as well.

The initial one row function:

/**
 * Test Returns the job codes assigned for the week
 * must be entered like this: =TWOJOBS(CELL("address",B12)&":"&CELL("address",AC12))
 * @param {Range} Employee's Info Range 
 * @return {List} How many days of work per job code
 * @customfunction
 */


function TWOJOBS(pRange) {
  //get the current active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FULL YEAR 2021");
//get the Named Range of JOBS
  var JobList = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("JOBS");
//get the Job Codes
  var jobjob = JobList.getValues();
  var jobjobjob = String(jobjob)
//get the length of the Job List
  var joblength = jobjobjob.length;
//Logger.log(joblength); 
//get the values from the input range
  var empData = ss.getRange(pRange);
  var empWeek = empData.getValues()[0];
  var empDatalst = [];
  
  for(var k=0; k<empWeek.length; k++){
    empDatalst.push(String(empWeek[k]).split());
  }
  /*get every third item in the EmpDatalst array*/
  const every_nth = (arr, nth) => arr.filter((e, i) => i % nth === nth - 1);
  var empJobs = every_nth(empDatalst, 3);

  var indivEmplJobs = [];
  var innyEmJojo = [];
  for(var i = 0; i<empJobs.length; i++){
   //for(var j=0; j<empJobs[i].length; j++){
     indivEmplJobs.push(String(empJobs[i]).split(";"));
    } 
  for (var i = 0;i<indivEmplJobs.length;i++){
    for(var j=0; j<indivEmplJobs[i].length; j++){
      innyEmJojo.push((indivEmplJobs[i][j]).trim())
   }
  }  
  var jobsworked = [];
  for(var i=0; i<joblength; i++){
       if(innyEmJojo.includes(String((jobjob[i])))){
      jobsworked.push(String((jobjob[i])));    
      }
  }

And then the multi-row function (I have named ranges for each week):

    /**
 * Test Returns the job codes assigned for the week
 * Assign this script to a button to get the info for the week
 * @param {Range} Employee's Info Range 
 * @return {List} How many days of work per job code
 * @customfunction
 */


function TestWeek29Jobs() {
  //get the current active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //get the Named Range of JOBS
  var JobList = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("JOBS");
  //get the Job Codes
  var jobjob = JobList.getValues();
  var jobjobjob = String(jobjob)
  //get the length of the Job List
  var joblength = jobjobjob.length;

  //get the named range of the week
  var empData = ss.getRangeByName("Week29");
  //var empData = ss.getRange("B787:V787");
  //get the values in the range
  var empWeek = empData.getValues();
  
  var empDatalst = [];

  empWeek.forEach(function(person){
      //Logger.log(empData.getRow());
      for(var k=0; k<empWeek.length; k++){
      empDatalst.push(String(empWeek[k]).split());
          }//end k for
        /*get every third item in the EmpDatalst array because every third is just the job code*/
      const every_nth = (arr, nth) => arr.filter((e, i) => i % nth === nth - 1);
      var empJobs = every_nth(empDatalst, 3);

      var indivEmplJobs = [];
      var innyEmJojo = [];
      for(var i = 0; i<empJobs.length; i++){
        indivEmplJobs.push(String(empJobs[i]).split(";"));
        }//end i for loop
      for (var i = 0;i<indivEmplJobs.length;i++){
        for(var j=0; j<indivEmplJobs[i].length; j++){
          innyEmJojo.push((indivEmplJobs[i][j]).trim())
            }//end j for loop
          }//end i for loop  
      var jobsworked = [];
      for(var i=0; i<joblength; i++){
        if(innyEmJojo.includes(String((jobjob[i])))){
          jobsworked.push(String((jobjob[i]))); 
        }//end if
      }//end for 

      var text = ""
      var row = empData.getRow();
      var targetcell = ss.getRange("AA"+row);
      for (var j=0; j<jobsworked.length; j++){
      if(jobsworked[j] != ""){
        text += jobsworked[j]+",";        
            }//end if
         targetcell.setValue(text);
         Logger.log(targetcell);
          }//end for
  }); //end of forEach
}//end of function

Can anyone help guide me if I'm WAY off or if I'm just missing something in the forEach functionality? Thanks in advance.

CMB · Accepted Answer · 2021-07-16 17:36:01Z

2

The method Array.forEach(function(item) {}) will execute a function for each element of Array, which is represented by item.

In your implementation:

empWeek.forEach(function(person){
  // code here
});

Since empWeek is now a 2D array, each element is now a 1D array, so person is now equal to empWeek[0], empWeek[1], so on, each is a 1D array.

So to access a single cell value, you need to put an index to person:

for(var k=0; k<person.length; k++){
  empDatalst.push(String(person[k]).split());
}

Also in the last code block, since you will be now writing to multiple rows, you need to increment row for each execution, so you need to put an index on the forEach function parameter. idx starts from 0 and increments by 1 for each function execution.

empWeek.forEach(function(person, idx){
  var empDatalst = [];
  for(var k=0; k<person.length; k++){
    empDatalst.push(String(person[k]).split());
  }

  // code here

  var text = "";
  var row = empData.getRow()+idx;
  var targetcell = ss.getRange("AA"+row);
  for (var j=0; j<jobsworked.length; j++) {
    if (jobsworked[j] != "") {
      text += jobsworked[j]+",";        
            }//end if
      targetcell.setValue(text);
      Logger.log(text);
    }
});

Reference:

forEach() JavaScript

answered Jul 16, 2021 at 17:36

CMB

5,1811 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NMelch Over a year ago

Thanks Carlos. I suspected the person index bit, but without the incrementing rows I couldn't see any of the results! This worked like magic!

CMB Over a year ago

Thanks for the response, the community would appreciate if you would accept the answer if it helps solve your problem. someone-answers

Collectives™ on Stack Overflow

forEach loop in google scripts

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related