How to insert two rows for every three rows of number in google sheet using apps script

Question

picture of add rows example

As the picture shows, there are 11 rows of numbers in Column A, I want to add rows to it and make it shows as the numbers in column D

You see for every 3 rows of number in A, I want to add a new row to its up and bottom, the new row in the top with value "0", and the bottom row with the sum value of these three rows of numbers for example the first three rows are 1,2,3, and their sum is "1+2+3=6" the second three rows are 4,5,6, and the sum is "4+5+6=15" the third three rows are 7,8,9, and the sum is "7+8+9=24" as picture shows and you see the last part of the column are "10 and 12", which are two rows (less than three rows), but in this case, it still need to add two new rows, one row in top with"0" and one row in bottom with its sum "10+12=22"

Right now, I wrote two for loop function to insert the rows

function insertRows() {
         var ss = SpreadsheetApp.getActiveSpreadsheet();
         var sheet = ss.getSheetByName("sheet1");

         var numRows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName ("sheet1").getLastRow();

         for (var i=1; i <= numRows+2; i+=4) {
              var addrow = sheet.insertRowsBefore(i,1);
              }

         }

  function insertRows2() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheetByName("sheet1");

        var numRows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1").getLastRow();

           for (var j=4; j <= numRows+2; j+=5) {
                var rowadd= sheet.insertRowsAfter(j,1)
                }
     }

"insertRows()" is used to insert the head row, "insertRows2()" is used to insert the bottom row, so far these two functions are working good to insert the rows in right place, but I don't know how to fill in the top rows with "0"s in the loop and how to fill the sum value of each three rows of numbers in the bottom rows.

Do you guys know how to fill in the rows with the right values?

Answer 1

How about this sample script? I think that there are several answers for your situation. So please think of this as one of them.

Flow :

1. Retrieve values from "A1:A11" of sheet1.
2. Using the retrieved values, it creates an array for the result.
   • At first, it separates the values, while inserts 0 to the top and the sum of the end.
   • As an important point, the array is created to put the spreadsheet as 2 dimensional array.
3. Put the result values to "D1:19" of sheet1.

Sample script :

function insertRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("sheet1");
  var value = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues().filter(Number);
  var result = [];
  var temp = [[0]];
  var sum = 0;
  for (var i = 0; i < value.length; i++) {
    temp.push(value[i]);
    sum += value[i][0];
    if ((i + 1) % 3 == 0 || i == value.length - 1) {
      temp.push([sum]);
      result = result.concat(temp);
      temp = [[0]];
      sum = 0;
    }    
  }
  sheet.getRange(1, 4, result.length, 1).setValues(result);
}

Note :

• When run this sample script, the lastrow of the sheet is changed. So I used filter() to var value = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues().

If this was not what you want, please tell me. I would like to modify my answer.