Group cells dynamically based on either indentation or content

Question

My accounting software exports most recent data into a spreadsheet which contains lot of data that is hard to analyze without grouping it.

I'd like to group rows based on similarity to the row above it (Or potentially based on indent, but that might be more difficult).

For example, there is a Discounts and promotions section which has several sub-items (sometimes an item might get added, so in the xample below, at some random point there could be 4 rows of sub-content vs. 3). At the bottom is the Total for that section.

The total row always has the same exact title with the word "Total " appended to the front.

    Discounts & promotions
        Discounts & promotions - Facebook (via Shopify)
        Discounts & promotions - Shopify - name
        Discounts & promotions - Shopify - name - gift card giveaways
    Total Discounts & promotions

Here is a link to some sample data.

Is there a script that can go through the whole sheet and group sections by logic - if this row has the same exact content as this other row + the word Total at the front, then group them.

(Also first I need the script to remove all groups - this is working, and last I need the script to collapse all groups,this is also working)

Here is the code I tried. Nothing is happening to the data in the spreadsheet.

function removeAllGroups1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Daily");
  const rg = sh.getDataRange();
  const vs = rg.getValues();
  vs.forEach((r, i) => {
    let d = sh.getRowGroupDepth(i + 1);
    if (d >= 1) {
      sh.getRowGroup(i + 1, d).remove()
    }
  });
}

function groupRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1:A" + sheet.getLastRow());
  const values = range.getValues();

  let previousValue = "";
  let groupStartRow = 1;

  for (let i = 0; i < values.length; i++) {
    const currentValue = values[i][0];

    // Check if the current value is a "Total" version of the previous value
    if (currentValue.startsWith("Total ") && currentValue.substring(6) === previousValue) {
      // Group the current row with the previous row
      sheet.getRange(i + 1, 1).shiftRowGroupDepth(1);
    } else {
      // Reset the group start row
      groupStartRow = i + 1;
    }

    previousValue = currentValue;
  }
}

function collapse() {
 const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Daily');
   let lastRow = sh.getDataRange().getLastRow();
  
  for (let row = 1; row < lastRow; row++) {
    let depth = sh.getRowGroupDepth(row);
    if (depth < 1) continue;
    sh.getRowGroup(row, depth).collapse();
  }
}

Answer 1

Use forEach() and indexOf() functions for row grouping

Consider this alternative answer, which should align with your desired outcome. This script will group rows according to your specifications.

Code:

// Group Rows Matching from the first and last.
function groupRows() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1:A" + sheet.getLastRow());
  const values = range.getValues();
  removeAllGroups1(range)

  let data = []
  for (let i = 4; i < values.length; i++) {
    // To lower case the current value of the values.
    const currentValue = values[i][0].toLowerCase();
    const toSearch = `total ${currentValue}`;
    // Map out all the values to lowered Cases.
    const lcase = values.map(x => x[0].toLowerCase());
    // Locating the index of the matching value of the current value.
    let index = lcase.indexOf(toSearch);

    // to check if there is same name of the cell
    data.forEach(x => {
      if (x[1] == index) {
        for (let i = index + 1; i < values.length; i++) {
          if (toSearch == lcase[i]) {
            index = i
          }
        }
      }
    })

    if (index != -1 && index != undefined) {
      data.push([i + 1, index])
    }
  }
  // Each Iteration of Group Depth
  data.forEach(x => {
    sheet.getRange(x[0], 1, x[1] - x[0] + 1).shiftRowGroupDepth(1)
  });
}

function removeAllGroups1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Daily");
  const rg = sh.getDataRange();
  const vs = rg.getValues();
  vs.forEach((r, i) => {
    let d = sh.getRowGroupDepth(i + 1);
    if (d >= 1) {
      sh.getRowGroup(i + 1, d).remove()
    }
  });
}

Sample Output:

References:

• forEach()
• indexOf()
• toLowerCase()