This is my code snippet working:

function loadBusinessProposalData () {
  var sourceSheet = SpreadsheetApp.openById(idSS).getSheetByName("dctPro");
  var pasteSheet = SpreadsheetApp.openById(idSS).getSheetByName("Gerar");
  var source = sourceSheet.getRange(2,3,1,sourceSheet.getLastColumn() - 2);
  const valuesToPaste = source.getValues();

  generateReferenceCellMap();

  for (let i = 0; i < valuesToPaste[0].length; i  ) {
      var destination = pasteSheet.setActiveSelection(referenceCells[i]);
      destination.setValue(valuesToPaste[0][i]);
      //if (i = 129) {
      //  console.log(i); //for test
      //  Utilities.sleep(1500);
      //}
  }

function generateReferenceCellMap () {
  var referenceSheet = SpreadsheetApp.openById(idSS).getSheetByName("dcPro");
  var referenceRange = referenceSheet.getRange(2,3,1, referenceSheet.getLastColumn() - 2);
  var referenceFormulas = referenceRange.getFormulas();

  for (let i = 0; i < referenceFormulas[0].length; i  ) {
    var reference = getCellReferenceByFormulaReference(referenceFormulas[0][i]);
    referenceCells.push(reference);
  }
}

function getCellReferenceByFormulaReference(formulaReference) {
  
  var delimiterSheetPosition = formulaReference.indexOf("!");
  var cellReference = formulaReference.slice(delimiterSheetPosition   1);

  return cellReference;
  //console.log(cellReference);
}

But I need to force a wait in the middle of the for loop for a formula to update in the worksheet, so I can continue the for loop and change the other cells.

I already tried this way:

  for (let i = 0; i < valuesToPaste[0].length; i  ) {
      var destination = pasteSheet.setActiveSelection(referenceCells[i]);
      destination.setValue(valuesToPaste[0][i]);
      if (i = 129) {
        console.log(i); //for test
        Utilities.sleep(1500);
      }
  }

Thus:

  for (let i = 0; i < valuesToPaste[0].length; i  ) {
      var destination = pasteSheet.setActiveSelection(referenceCells[i]);
      destination.setValue(valuesToPaste[0][i]);
      if (i = 129) {
        console.log(i); //for test
        SpreadsheetApp.flush();
      }
  }

And thus:

  for (let i = 0; i < valuesToPaste[0].length; i  ) {
      var destination = pasteSheet.setActiveSelection(referenceCells[i]);
      destination.setValue(valuesToPaste[0][i]);
      if (i = 129) {
        console.log(i); //for test
        Utilities.sleep(1500);
        SpreadsheetApp.flush();
      }
  }

But in all ways apparently the variable i stops being incremented, and the script loops

Could you help me understand why this is happening?

Thanks.

CodePudding user response：

This if (i = 129) should be this if (i == 129) or possibly this if (i === 129)