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

1 Answer 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.

3
  • 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, 2021 at 23:11
  • Hi! going back to the example above. I have been trying to add the Filteredbyuser to the script below but is not working for me. 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); } }
    – Gilbert
    Jun 23, 2021 at 21:50
  • The is a different question. Please post a new question, and describe the problem rather than describing your attempted solution. See What is the XY problem? Jun 25, 2021 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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