0

I would appreciate any inputs on this Google Apps Script code!

I am trying to implement a do-while loop to copy the formula from cell B2 to C2 and follow that sequence from B3 to c3, B4 to c4, and so on.

The condition that I want to give to the do while loop is that as long as the active cell is > 0, the loop runs. If I remove the loop from the script and I run the code manually, it will work but with the code it gets stuck.

Copy of challenge and script in tab 1 and 2 here: https://docs.google.com/spreadsheets/d/1-3ZZBar2BExaFhOMzpjpAbDFMTDmupuX4x0irwv3ixM/copy

Images below:

enter image description here

enter image description here

1
0

This function should do what you are asking for:

function fillFormulaDown() {
  const sheet = SpreadsheetApp.getActiveSheet();
  let currentCell = sheet.getActiveCell();
  while (currentCell.getValue()) {
    let formula = currentCell.getFormulaR1C1();
    if (formula) {
      currentCell.offset(0, 1).setFormulaR1C1(formula);
    }
    currentCell = currentCell.offset(1, 0);
  }
}

...but that is an inefficient way of copying a larger number of formulas, because each cell is read and written separately. You can make the code run tens of times faster with something like this:

function betterFillFormulaDown() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const currentCell = sheet.getActiveCell();
  const numRows = sheet.getLastRow() - currentCell.getRow() + 1;
  const currentColumn = currentCell.offset(0, 0, numRows, 1);
  const values = currentColumn.getValues().flat();
  const formulas = currentColumn.getFormulas().flat();
  const numFormulasToCopy = values.findIndex((value, index) => !value || !formulas[index]);
  if (!numFormulasToCopy) {
    return;
  }
  if (numFormulasToCopy === -1) {
    numFormulasToCopy = numRows;
  }
  const rangeToCopy = currentCell.offset(0, 0, numFormulasToCopy, 1);
  rangeToCopy.offset(0, 1).setFormulasR1C1(rangeToCopy.getFormulasR1C1());
}

The better function runs faster, because it gets and sets a larger number of cells in one go. Operating on one cell with .setFormula() costs about the same as operating on one thousand cells with .setFormulas().

The copying will stop at the first cell that is blank, zero or does not contain a formula.

1
  • Thank you so much for your help!!!!! It seems I was very far from the right script. I will apply the one you suggested. Thanks again! – Gilbert May 16 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.