Adding multiple new rows in Google Sheets based on a cell value

Question

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.

Answer 1

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);
}