Google Apps Script function to copy and paste a previous i value within a forEach loop

Question

I get a list of employees from a third party. Some employees have more than one pay stub per month. So one column of the list is the employee ID and the second is the "sub-id."

Here is an example of a list of employees and IDs:

I want to run a function in Apps Script that copies the value of the employee ID and pastes it on the blank cells (so then I can do some calculations).

I've created a loop for the "Legajo" array but when I get to the if statement I don't know how to get the previous value and set it to the blank item.

function fillFile() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("matriz");
  var numLegajos = sheet.getRange(3, 1, sheet.getLastRow() - 3).getValues();

  numLegajos.forEach(function(legajo,i) {

    if (legajo == "") {

      legajo = ?? // this is where I don't know what to do to get the previous value and set it to the blank item

  }

  Logger.log(legajo);

 });

}

Answer 1

Required reading:

• What does the range method getValues() return and setValues() accept?
• MDN forEach

Solution:

The previous value is the main array's i-1. So, you could refer to the array you're iterating over inside forEach

Sample script:

numLegajos.forEach(function(legajoRow, i) {
  if (legajoRow[0] === '') {
    legajoRow[0] = numLegajos[i - 1][0]; //set previousValue to this row's column element
  }
  console.log(JSON.stringify(legajoRow));
});

Alternatively, the third argument supplied to the forEach's callback is the main array itself. function(legajoRow, i, arr). So, you can also use arr[i-1][0]

const numLegajos = /*Mock getValues()*/ [
  [12],
  [55],
  [''],
  [''],
  [11],
  [''],
  [''],
  ['']
];
numLegajos.forEach(function(legajoRow, i, arr) {
  if (legajoRow[0] === '') {
    legajoRow[0] = arr[i - 1][0]; //set previousValue to this row's column element
  }
});
console.log(JSON.stringify(numLegajos));

Answer 2

Explanation:

• You need to flatten numLegajos using flat() in order to apply the forEach() method.

---

Solution:

function fillFile() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("matriz");
  var numLegajos = sheet.getRange('A3:A'+sheet.getLastRow()).getValues().flat(1);
  
  numLegajos.forEach( (legajo,index) => {
    if (legajo == "") {
      sheet.getRange(index+3,1).setValue(sheet.getRange(index+2,1).getValue())
  }
 }); 
}