Inserting rows with data upon finding certain value in Column B-CodePudding

My question is somehow complicated . I will try to simplify it as much as possible. I have added a link to the sheet for simplicity. Sample Sheet

I have a large table of about 10,000 rows. I want to insert 9 rows after each row containing the word " Test 22" in column B. This is the first stage. The most important part ( stage II) that I want to fill data in these 9 rows as following :

Column A (Product Name) cells will contain the same product name as the first cell adjacent to the cell of value "Test 22"
Column E ( Empty Column 2) cells which are 9 cells will contain these values (Result 1, Result 2 , Result 3, Result 4 , Result 5 , Result 6 , Average, Max, Min). And of course , this process will be repeated through the whole table upon finding the word " Test 22" in column B.

I have managed successfully to perform stage I which is inserting 9 blank rows after each row containing the word " Test 22" in column B, but I couldn't perform stage II. I don't know if this function could be done in 1 step or 2 steps. Your help will be really appreciated. Thanks

CodePudding user response：

It can be something like this:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var new_data = [];

  for (var row in data) {
    new_data.push(data[row]);
    if (data[row][1] == 'Test 22') {
      new_data.push([data[row][0],'','','','Result 1']);
      new_data.push([data[row][0],'','','','Result 2']);
      new_data.push([data[row][0],'','','','Result 3']);
      new_data.push([data[row][0],'','','','Result 4']);
      new_data.push([data[row][0],'','','','Result 5']);
      new_data.push([data[row][0],'','','','Result 6']);
      new_data.push([data[row][0],'','','','Average']);
      new_data.push([data[row][0],'','','','Max']);
      new_data.push([data[row][0],'','','','Min']);
    }
  }

  sheet.getRange(1,1,new_data.length,new_data[0].length,).setValues(new_data);
}

or, about the same algo with less verbose notation:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var new_data = [];
  var col_e = ['Result 1','Result 2','Result 3','Result 4',
    'Result 5','Result 6','Average','Max','Min'];

  for (let row of data) {
    new_data.push(row);
    if (row[1] == 'Test 22') {
      col_e.forEach(e => new_data.push([row[0],'','','',e]));
    }
  }

 sheet.getRange(1,1,new_data.length,new_data[0].length,).setValues(new_data);
}

The code to format the column F:

function format_column_F() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = sheet.getRange('E:E').getValues().flat();
  var formats = new Array(9).fill(['0.00 %']);

  for (let row = 0; row < column.length; row  ) {
    if (column[row] == 'Result 1') {

      let start   = row   1;
      let end     = row   6;
      let average = end   1;
      let max     = end   2;
      let min     = end   3;

      let range   = 'F'   start   ':F'   end;

      sheet.getRange('F'   average).setFormula('=AVERAGE('   range   ')');
      sheet.getRange('F'   max).setFormula('=MAX('   range   ')');
      sheet.getRange('F'   min).setFormula('=MIN('   range   ')');

      sheet.getRange('F'   start   ':F'   min).setNumberFormats(formats);
      
      row  = 9;
    }
  }

}

Refs: