How to delete multiple rows at once in a for loop using GAS?-CodePudding

I understand that if you happen to have to the start and howManyRowsToDelete, one can use deleteRows(start, howManyRowsToDelete), but how to delete multiple rows at once in a for loop, or right after it!?

for (let n = entregasBD.length - 1; n >= 0; n--) {
  for (let a = 0; a < retirarItens.length; a  ) {
    if (entregasBD[n][8] == noEntrega && entregasBD[n][9] == 'Pendente' && entregasBD[n][0] == retirarItens[a][0]) {
      let row = n   1;
      let qtdMov = retirarItens[a][9]; 
      let qtdPendente = entregasBD[n][5]; 
      let qtdEntrega = entregasBD[n][4];
      qtdPendente = qtdPendente   qtdMov;
      qtdEntrega = qtdEntrega - qtdMov;
      console.log('Qtd de Entrega Final: '   qtdEntrega)
      if (qtdEntrega == 0) {
        sheetBDEnt.deleteRow(row);//These calls are what I'm trying to avoid
      } else {
        sheetBDEnt.getRange(row, 5, 1, 2).setValues([=[qtdEntrega, qtdPendente]]);
      }
    }
  }
}

Appreciate your attention.

CodePudding user response：

I believe your goal is as follows.

From how to delete multiple rows at once in a for loop, or right after it!?, you want to delete the rows by one request.

In this case, how about the following modification?

Unfortunately, I cannot imagine your Spreadsheet. So, by guessing the values of entregasBD, retirarItens, and noEntrega, I modified your script.

Modified script:

In order to achieve your goal, in this script, Sheets API is used. So, please enable Sheets API at Advanced Google services.


// do something.
// Please put your script retrieving the values of `entregasBD`, `retirarItens` and `noEntrega`.

const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetId = "###"; // Please set your sheet ID.

const obj = retirarItens.reduce((o, r) => (o[r[0]] = r[9], o), {});
const requests = entregasBD.reduce((ar, [a, , , , e, f, , , i, j], row) => {
  if (i == noEntrega && j == 'Pendente' && obj[a]) {
    let qtdMov = obj[a];
    let qtdPendente = f;
    let qtdEntrega = e;
    qtdPendente = qtdPendente   qtdMov;
    qtdEntrega = qtdEntrega - qtdMov;
    if (qtdEntrega == 0) {
      ar.push({ deleteDimension: { range: { sheetId, startIndex: row, endIndex: row   1, dimension: "ROWS" } } });
    } else {
      ar.push({ updateCells: { start: { sheetId, rowIndex: row, columnIndex: 4 }, rows: [{ values: [{ userEnteredValue: { numberValue: qtdEntrega } }, { userEnteredValue: { numberValue: qtdPendente } },] }], fields: "userEnteredValue" } });
    }
  }
  return ar;
}, []).reverse();
if (requests.length == 0) return;
Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);

In this modification, the rows are deleted by Sheets API. And also, the values are put to the rows using Sheets API. By this, all requests can be done by one API call.

Note:

Unfortunately, I might not be able to correctly understand your actual Spreadsheet and your actual goal you expect. I'm worried about this. So, when this modified script was not useful, when you provide the sample Spreadsheet, I thought that it will help to modify the script.

Modified script:

Note:

References: