1

I need to try and find a formula or script that can insert new rows in a Google Sheet between other rows of data, based on cell values. In the below example, cell X2 is showing "3", so we need 2 new rows inserted below.

Cell X3 shows "5", so we need 4 new rows inserted below that also, and continuing on down the sheet in the same fashion. Where a cell contains "1", there are no new rows required.

screenshot

4

1 Answer 1

0

That will require a script. Try something like this:

/**
* Inserts blank rows in the active sheet.
* Rows are inserted after every row where column X2:X contains a number.
* The number in column X2:X tells how many rows to insert after that row.
*
* @OnlyCurrentDoc
*/
function insertBlankRows() {
  // version 1.1, written by --Hyde, 17 January 2023
  //  - see https://webapps.stackexchange.com/q/168794/269219
  const range = SpreadsheetApp.getActiveSheet().getRange('X2:X');
  const rowStart = range.getRow();
  const rowNumbers = [];
  const numRowsToInsert = range.getValues()
    .flat()
    .filter((value, index) => {
      if (typeof value === 'number' && value > 1) {
        rowNumbers.push(rowStart + index);
        return true;
      }
      return false;
    })
    .map(numRows => numRows - 1);
  insertBlankRows_(range.getSheet(), rowNumbers, numRowsToInsert);
}


/**
* Inserts numRowsToInsert blank rows in sheet after rowNumbers.
*
* @param {Sheet} sheet The sheet where to insert blank rows.
* @param {Number[]} rowNumbers The 1-indexed row numbers after which to insert blank rows.
* @param {Number[]} numRowsToInsert How many rows to insert after each rowNumbers.
*/
function insertBlankRows_(sheet, rowNumbers, numRowsToInsert) {
  // version 1.0, written by --Hyde, 8 July 2021
  //  - see https://support.google.com/docs/thread/116344628?msgid=116435373
  numRowsToInsert.reduceRight((_, numRows, index, array) => {
    sheet.insertRowsAfter(rowNumbers[index], numRows);
  }, null);
}
2
  • Amazing, thank you so much @doubleunary, that's perfect and does the exact job. Is there potentially any addition to the script that, once the new rows have been added, copies the data from the cell in column W to the respective cell in the new rows? In my screenshot example, that would mean that the data in W2 would get copied to cells W3 and W4 after the new rows were added. Jan 19 at 9:34
  • Please ask only one question per post. If you have trouble implementing that functionality, post a new question. Jan 19 at 10:34

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.