Javascript - How do I use offset within a forEach loop in google apps script

EzraMartin
November 30, 2024
165 views
0 votes
2 Answers

I have a range consisting of a variable number of rows and 4 columns (A,B,C,D). I need to loop through each of the rows and add the number in column D to the number in column C and then clear the contents of column D. I think I can get the adding and the clearing if i can get the correct cells referenced. i was wanting to use a for or foreach loop and then offset from column A to columns C & D but have been unable to get the offset to work.

function AddInventory2() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Total Inventory')
  const lrow = sh.getLastRow();
  var Avals = sh.getRange("A1:A"+lrow).getValues();
  var Alast  = lrow - Avals.reverse().findIndex(c=>c[0]!='');
  Avals.forEach(function(row){
   targetCell = Avals.offset(0,3);
   Logger.log(targetCell);
  })
}

Answers

- Zachary
- November 28, 2024 at 3:47 am
- 0 votes
0
Avals.offset(0,3) does not work because Avals is a JavaScript array, not a Range object. Offset only works with Range objects in Google Apps Script.

@EzraMartin I still don’t fully understand the meaning of offset from column A to columns C & D. However, if you want to loop each row and add the number in column D to the number in column C, then clear column D, you can try the following function:
```
var range = sh.getRange(1, 1, lrow, 4);
var values = range.getValues();

for (var i = 0; i < values.length; i++) {
  var colC = values[i][2];
  var colD = values[i][3];
    
  if (colD !== '' && !isNaN(colC) && !isNaN(colD)) {
    values[i][2] = colC + colD;
    values[i][3] = '';
  }
}

range.setValues(values);
```
Login or Signup to reply.

Using offset within a for loop:

The code below achieved the desired requirement of offsetting to columns C & D from each of the cells in column A that contain a value.

Note: However, I wouldn’t suggest using offset in a loop if you have more complex data, as it can increase the number of calls to the Spreadsheet service, which may slow down execution and cause runtime issues.

Code:

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('Total Inventory');
  const lrow = sh.getLastRow();

  for (let i = 1; i <= lrow; i++) {
    const cellA = sh.getRange(i, 1);
    const cellC = cellA.offset(0, 2);
    const cellD = cellA.offset(0, 3);
    const valueA = cellA.getValue();
    const valueC = cellC.getValue();
    const valueD = cellD.getValue();

    if (valueA !== "") {
      cellD.setValue(valueA);
      cellC.setValue(valueC + valueA);
      cellA.clear();
      cellD.clear();
    }
  }

Alternative way for a more complex dataset:

In the future, if you have a large dataset, this approach is much better than the first one. It handles large datasets more efficiently by processing the data in memory and reducing the number of service calls.

  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Total Inventory');
  const data = sh.getRange(1, 1, sh.getLastRow(), 4).getValues(); // A:D


  for (let i = 0; i < data.length; i++) {
    const valueA = data[i][0];
    const valueC = data[i][2];
    const valueD = data[i][3];

    if (valueA !== "") {
      data[i][3] = valueA;
      data[i][2] = valueC + valueA;
      data[i][0] = "";
      data[i][3] = "";
    }
  }

  sh.getRange(1, 1, data.length, 4).setValues(data);

Sample Output: