Grouping Rows and moving in a single Google Sheet using Google Apps Script

Question

I receive spreadsheets with some randomized rows and every time I need to organize the rows in certain groups, and add a blank row between 2 groups. I could identify the groups by a tag that's defined in one column, or by a identifier number column, but I also need to get the row that comes right below the identifiable group.

I already mashed together some working code to change some names I need, and added a menu that will trigger the functions, but I can't even begin to understand how I could group and move these rows to the order I need them showing up.

Does anyone have any idea of how I could build that up, or any pointers?

EXAMPLE FILE:

test file

PURPOSE:

In the file I have simulated how I get the sheet, and I need to arrange the second identifier rows together with their respective totals (which comes below).

I added the codes I've been tinkering around which I'll also add here:

function onOpen() {
       var ss = SpreadsheetApp.getActive();
       var items = [
          {name: 'Changenames', functionName: 'Changegroupnames'},
          null, // Results in a line separator.
          {name: 'Organize Rows', functionName: 'movingROWS'}
       ];
    
       ss.addMenu('Organize', items);
    }
    
    function Changegroupnames(){
        var sheet = SpreadsheetApp.getActiveSheet()
        replaceInSheet(sheet,'GROUP 01 - 04 TOTALS','NRS EAST - CITY 01 HEADQUARTERS'),
        replaceInSheet(sheet,'GROUP 03 - 04 TOTALS','NRS EAST - CITY 03 BASE'),
        replaceInSheet(sheet,'GROUP 04 - 04 TOTALS','NRS EAST - CITY 04 BASE'),
        replaceInSheet(sheet,'GROUP 09 - 04 TOTALS','NRS EAST - CITY 09 BASE'),
          replaceInSheet(sheet,'GROUP 10 - 02 TOTALS','NRS WEST - CITY 10 HEADQUARTERS'),
        replaceInSheet(sheet,'GROUP 06 - 02 TOTALS','NRS WEST - CITY 06 BASE'),
          replaceInSheet(sheet,'GROUP 02 - 01 TOTALS','NRS NORTH - CITY 02 HEADQUARTERS'),
        replaceInSheet(sheet,'GROUP 05 - 01 TOTALS','NRS NORTH - CITY 05 BASE'),
        replaceInSheet(sheet,'GROUP 07 - 03 TOTALS','NRS SOUTH - CITY 07 HEADQUARTERS'),
        replaceInSheet(sheet,'GROUP 07 - 03 TOTALS','NRS SOUTH - CITY 08 BASE')
        ;
    }
    
    function replaceInSheet(sheet, to_replace, replace_with) {
      //get the current data range values as an array
      var values = sheet.getDataRange().getValues();
    
      //loop over the rows in the array
      for(var row in values){
    
        //use Array.map to execute a replace call on each of the cells in the row.
        var replaced_values = values[row].map(function(original_value){
          return original_value.toString().replace(to_replace,replace_with);
        });
    
        //replace the original row values with the replaced values
        values[row] = replaced_values;
      }
    
      //write the updated values to the sheet
      sheet.getDataRange().setValues(values);
    }
    
    function movingROWS(){
    var sheet = SpreadsheetApp.getActiveSheet()
     sheet.getRange("=$C2='City1'").moveTo(sheet.getRange("A502"));
     }

EDIT

As asked by @lamblichus I'll try to make it visually more clear. I can't add images but I'll add links to them. Hope it makes it easier to understand.

As it's visible in the first image, I have a column called "second identifier" followed by a column called "First identifier name". I need to sort the grouped rows that have the same second identifier number together in the sheet.

image 01 - https://drive.google.com/file/d/1_LmYFE9jRGPD6VdOnp0cd1kjaCoysS7g/view?usp=sharing

Each individual group must be moved together, including the row right below which contains the totals of the group.

Making the sheet looks like this:

image 02 - https://drive.google.com/file/d/1mirRDEZRqmyWCLVrqT3KU5rFeFRLs4ar/view?usp=sharing image 03 - https://drive.google.com/file/d/1MzhwWOtf6jFjRkdXdRWbCZkZ5frprUX8/view?usp=sharing

And then, add a blank row between the last group that is part of the second identifier group.

I hope I made it a little more clear.

Answer 1

You could do the following:

• Declare an empty 2D array (groups) where all the different groups will be classified (a group being the rows with a unique combination of 1st and 2nd identifier).
• Loop through each row of data.
• For each row, append the row to the last inner array in groups.
• For each row, check if column B is empty. If that's the case, append a new array to groups.
• Declare an empty array (outerGroups) where each set of groups with the same 2nd identifier will be grouped.
• Loop through groups.
• For each group, check if an outerGroup with this 2nd identifier exists (you can use find for this).
• If a match is found, add the group to the found outerGroup. Otherwise, create a new outerGroup that includes this group.
• Modify outerGroups in order to concatenate blank rows in between, and use flat() to convert this to a 2D array.
• Set the resulting values via Range.setValues.

Code snippet:

// Copyright 2021 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function sortGroups() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const firstRow = 3;
  const numCols = sheet.getLastColumn();
  const range = sheet.getRange(firstRow, 1, sheet.getLastRow()-firstRow+1, numCols);
  const values = range.getValues();
  const totalsRow = values.pop();
  let groups = [[]];
  values.forEach((row, i) => {
    groups[groups.length - 1].push(row);
    if (!row[1] && i < values.length-1) {
      groups.push([]);
    }
  });
  let outerGroups = [];
  groups.forEach(group => {
    const outerGroup = outerGroups.find(outerGroup => {
      return outerGroup[0] && outerGroup[0][0] && outerGroup[0][0][1] === group[0][1];
    });
    if (outerGroup) outerGroup.push(group);
    else outerGroups.push([group]);
  });
  outerGroups = outerGroups.map(outerGroup => outerGroup.flat())
                           .map(outerGroup => outerGroup.concat([new Array(numCols)]))
                           .flat()
                           .concat([totalsRow]);
  sheet.getRange(firstRow, 1, outerGroups.length, outerGroups[0].length).setValues(outerGroups);
}