CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
marikamitsos
marikamitsos

Reputation: 10573

How to insert a cell with value on every Nth row in a column in Google Sheets

There is a list of different values as a continuos list (array) in a column.
We need them grouped and separated every Nth row (7th) in another column by inserting some text between them. Not a whole row. Just a cell. As a result they will be "pushed-down" the column.


+------+-----------+-----------+
|      |   Col2    |  Col3     |
+------+-----------+-----------+
|    1 | Sunday    | Group 01  |
|    2 | Monday    | Sunday    |
|    3 | Tuesday   | Monday    |
|    4 | Wednesday | Tuesday   |
|    5 | Thursday  | Wednesday |
|    6 | Friday    | Thursday  |
|    7 | Saturday  | Friday    |
|    8 | 10        | Saturday  |
|    9 | 20        | Group 02  |
|   10 | 30        | 10        |
|   11 | 40        | 20        |
|   12 | 50        | 30        |
|   13 | 60        | 40        |
|   14 | 70        | 50        |
|   15 | MERCURY   | 60        |
|   16 | MARS      | 70        |
|   17 | JUPITER   | Group 03  |
|   18 | VENUS     | MERCURY   |
|   19 | SATURN    | MARS      |
|   20 | EARTH     | JUPITER   |
|   21 | NEPTUNE   | VENUS     |
|   22 | Mary      | SATURN    |
|   23 | John      | EARTH     |
|   24 | Paul      | NEPTUNE   |
|   25 | Ann       | Group 04  |
|   26 | ...       | Mary      |
|   27 | ...       | John      |
|   28 |           | Paul      |
|   29 |           | Ann       |
|   30 |           |           |
|   31 |           |           |
|   32 |           |           |
|   33 |           |           |
+------+-----------+-----------+

I have so far tried and succeeded on having the desired result by using a simple query: 

=QUERY(({"***Group";B1:B7;"***Group";B8:B14;"***Group";B15:B21;"***Group";B22:B25;}),"SELECT *")

I wonder though. Is there a more "elegant" as well as general way using either formula or script.

Upvotes: 0

Views: 2962

Answers (2)

TheMaster
TheMaster

Reputation: 50462

You could use Array.splice to insert arrays every Nth row: 

/**
 * Inserts string every nth row
 *
 * @customfunction
 * @param {Object[][]} arr Input Column
 * @param {number} every Nth row to insert
 * @param {string} str String to insert
 */
function insertText(arr, every, str) {
  for (
    var i = 0, j = 0;
    i < arr.length - 1 && arr.splice(i++, 0, [str + ++j]);
    i += every
  );
  return arr;
}
const arr = [ //Input col A1:A25 looks like this 
  ['Sunday'],
  ['Monday'],
  ['Tuesday'],
  ['Wednesday'],
  ['Thursday'],
  ['Friday'],
  ['Saturday'],
  ['10'],
  ['20'],
  ['30'],
  ['40'],
  ['50'],
  ['60'],
  ['70'],
  ['MERCURY'],
  ['MARS'],
  ['JUPITER'],
  ['VENUS'],
  ['SATURN'],
  ['EARTH'],
  ['NEPTUNE'],
  ['Mary'],
  ['John'],
  ['Paul'],
  ['Ann'],
];
console.log(insertText(arr, 7, 'Group'));

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26806

I suggest you the following solution based on Apps Script:

function myFunction() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var firstRow=1;
  var column=2;
  var destColumn=3;
  var increment=7;
  var lastRow=sheet.getLastRow();
  var values=sheet.getRange(firstRow, column, lastRow,1).getValues();
  var array=[];
  var k=1;
  Logger.log(lastRow);
  for(var i=0;i<lastRow;i++){
    if(i%increment==0){
      array.push("Group "+k);
      k++;
    }
     array.push(values[i][0]);
  }
  var outerArray = [];
  for(var j=0;j<array.length;j++){
    var tempArray = [];
    tempArray.push(array[j]);
    Logger.log(array[j]);
    Logger.log(tempArray);
    outerArray.push(tempArray);
  }
  sheet.getRange(1, destColumn, array.length, 1).setValues(outerArray);
}

Workflow:

  • Push the contents of the column of interest into an array
  • Insert an additional entry with content every incrementth time
  • Transpose the array into the [column][row] syntax
  • Insert the values into the destination column

References:

Upvotes: 1

Related Questions