0

We would like to extend an (amazing) script for Google Sheets that inserts multiple new rows in Google Sheets based on a cell value (original question and answer: https://webapps.stackexchange.com/a/168797/307113)

As well as inserting the new rows, we also need to copy the data from columns A, B and T down to the newly inserted rows, from the source row of the newly inserted rows.

1 Answer 1

0

Use Range.setValues(), like this:

/**
* Inserts rows in the active sheet, copying content from the row above.
* 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.
* The values in columns A, B and T on each row are copied to the newly
* inserted rows.
*
* @OnlyCurrentDoc
*/
function insertFilledRows() {
  // version 1.0, written by --Hyde, 20 January 2023
  //  - see https://webapps.stackexchange.com/a/168864/269219
  const range = SpreadsheetApp.getActiveSheet().getDataRange();
  const columnsToFill = [
    1 - 1, // column A
    2 - 1, // column B
    20 - 1, // column T
  ];
  const values = range.getValues();
  const rowStart = range.getRow();
  const rowNumbers = [];
  const valuesToFill = [];
  const numRowsToInsert = values
    .map(row => row[24 - 1]) // column X
    .filter((value, rowIndex) => {
      if (typeof value === 'number' && value > 1) {
        rowNumbers.push(rowStart + rowIndex);
        const rowValues = values[rowIndex]
          .map((value, index) => columnsToFill.includes(index) ? value : null);
        valuesToFill.push(rowValues)
        return true;
      }
      return false;
    })
    .map(numRows => numRows - 1);
  const ss = range.getSheet().getParent();
  if (rowNumbers.length) {
    insertFilledRows_(range.getSheet(), rowNumbers, numRowsToInsert, valuesToFill);
    ss.toast(`Inserted rows in ${rowNumbers.length} places.`);
  } else {
    ss.toast(`Could not find any rows to insert.`);
  }
}


/**
* Inserts numRowsToInsert blank rows in sheet after rowNumbers.
* Fills the new rows with valuesToFill, one such subarray per inserted block.
*
* @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.
* @param {Object[][]} valuesToFill The values to put into newly inserted rows.
*/
function insertFilledRows_(sheet, rowNumbers, numRowsToInsert, valuesToFill) {
  // version 1.0, written by --Hyde, 20 January 2022
  //  - see https://webapps.stackexchange.com/a/168864/269219
  numRowsToInsert.reduceRight((_, numRows, index) => {
    const rowNumber = rowNumbers[index];
    sheet.insertRowsAfter(rowNumber, numRows);
    const filler = new Array(numRows).fill(valuesToFill[index]);
    sheet.getRange(rowNumber + 1, 1, filler.length, filler[0].length).setValues(filler);
  }, null);
}
1
  • Unbelievable - @doubleunary you're a wizard, thank you so much, worked first time! Commented Jan 20, 2023 at 14:56

Your Answer

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

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