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);

});

}

Marios · Accepted Answer

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())
  }
 }); 
}

TheMaster · Answer

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));

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

2 Answers

Explanation:

Solution:

Required reading:

Solution:

Sample script:

Add your own answers!

Ask a Question