Question

0

212

Views

Google sheet script auto add new rows with copied formulas

I need to create function which adds rows with copied formulas from above rows. After the script is launched it should result in accurate number (set 5 i this code) of blank rows at the end of the sheet.

The code I managed to create counts what number of rows should be added but adds only one row with copied formulas at the end.

Please help me edit this code to multiple the result of the function by "rowstoadd" parameter.

Sheet image

function autoaddRows() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('Harmonogram');
  var range = sheet.getRange("B2:B").getValues();
  var lastRowB = range.filter(String).length + 2;
  var lastRowA = sheet.getLastRow();
  var blanknrows = sheet.getLastRow() - lastRowB;
  if (blanknrows < 5) {
    let rowstoadd = 5 - blanknrows;
    Browser.msgBox(rowstoadd);
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let lastRowIndex = sheet.getLastRow();
    let existingRange = getRowRange(sheet, lastRowIndex);

    sheet.insertRowAfter(lastRowIndex);
    let newRange = getRowRange(sheet, ++lastRowIndex);

    existingRange.copyTo(newRange);
    newRange.setFormulas(newRange.getFormulas());
    newRange.clearNote();
  }
  
  function getRowRange(sheet, rowIndex) {
    return sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn());
  }
}

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-05T05:09:13.096Z

I believe your goal is as follows.

You want to keep 5 new empty rows.
For example, when the last row of column "A" is 10 and the last row of column "B" is 9, you want to add 4 new rows.
Also, you want to put the formula to the column "B" of the inserted rows.

In this case, how about the following modified script?

Modified script:

function autoaddRows() {
  var addRows = 5;
  var sheet = SpreadsheetApp.getActive().getSheetByName('Harmonogram');
  var range = sheet.getRange("B2:B").getValues();
  var lastRowB = range.filter(String).length + 1;
  var lastRow = sheet.getLastRow();
  var blanknrows = lastRow - lastRowB;
  var diff = addRows - blanknrows;
  if (diff > 0) {
    sheet.insertRowsAfter(lastRow, diff);
    // var range = sheet.getRange("B" + lastRowB);
    // range.copyTo(range.offset(1, 0, diff + 1), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
    var numberOfCol = sheet.getLastColumn() - 1;
    var range = sheet.getRange(lastRowB, 2, 1, numberOfCol);
    range.copyTo(range.offset(1, 0, diff + 1, numberOfCol), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
    range.clearNote();
  }
}

In this modification, the difference between the last rows between column "A" and column "B" is retrieved. And the empty rows are inserted using the difference.
In your script, newRange.clearNote(); is used. So, I also add range.clearNote();. If you want to remove it, please remove it.

0

212

Google sheet script auto add new rows with copied formulas

1 answers

0

Modified script:

References:

Find remote jobs

Andres GPT