Shopify Question posted in
The Shopify help center can be found here.

Shopify – Group cells dynamically based on either indentation or content

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();
  }
}
Tags:

2

Answers


  1. 0

    Use Array.findIndex(), like this:

    function groupRows(keyRange = SpreadsheetApp.getActive().getRange('Daily!A5:A')) {
  const sheet = keyRange.getSheet();
  removeAllGroups_(sheet);
  const rowStart = keyRange.getRow();
  const keys = keyRange.getDisplayValues().flat().map(k => k.trim());
  const groups = [];
  keys.forEach((key, startIndex) => {
    const endIndex = keys.findIndex(k => k === `Total ${key}`);
    if (endIndex !== -1) groups.push([rowStart + startIndex + 1, endIndex - startIndex]);
  });
  groups.forEach(([start, numRows]) => sheet.getRange(start, 1, numRows).shiftRowGroupDepth(1).collapseGroups());
}

function removeAllGroups_(sheet) {
  sheet.getDataRange().getValues().forEach((_, index) => {
    const depth = sheet.getRowGroupDepth(index + 1);
    if (depth) sheet.getRowGroup(index + 1, depth).remove();
  });
}

    See Array.findIndex().

    Login or Signup to reply.
  2. 0

    You want to group rows on a P&L based on a "cell value" being followed by a subsequent "Total" cell value. For example, "Sales" and "Total Sales".

    Consider this answer.

    Logic

    • removeExistingGroups("Daily") – delete existing groups; re-uses the OP’s code
    • var data = dataRange.getValues().flat() get the values in Column A as a 1D array
    • for (i=0;i<data.length;i++){ – loop through the rows
      • var title = data[i] – get the row value
      • var totalTitle = "Total "+title – create an equivalent "Total" value
      • var index = data.indexOf(totalTitle) – use indexOf to find a match for the "Total value"
      • if a match is NOT found – do nothing
      • if a match IS found identify the range to be grouped
        • var groupRange = sheet.getRange(dataRow,1,matchRow-dataRow)
        • groupRange.shiftRowGroupDepth(1); – group the range
    function groupSheetRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Daily")

  // delete existing groups
  removeExistingGroups("Daily")
  
  // first row of data
  var firstRow = 5
  // get the last row of data
  var lastRow = sheet.getLastRow()
  
  // get the data range for column A
  var dataRange = sheet.getRange(firstRow,1,lastRow-firstRow+1-4,1)
  // Logger.log("DEBUG: the target range = "+dataRange.getA1Notation())
  var data = dataRange.getValues().flat()
  // Logger.log(data) // DEBUG

  // loop through the data to find matches
  for (i=0;i<data.length;i++){
    var title = data[i]
    var totalTitle = "Total "+title
    // indexOf returns -1 is no match is found,
    // or the array index IF a match is found
    var index = data.indexOf(totalTitle)
    // test for result
    if (index == -1){
      // can't find a match, 
       // Logger.log("DEBUG: NO MATCH: i = "+i+", Title = "+title+", total Title = "+totalTitle+", index = "+index)
    }else{
      // found a match
      var dataRow = i+firstRow
      var matchRow = index+firstRow
      var matchValue = data[index]
      var groupRange = sheet.getRange(dataRow,1,matchRow-dataRow)
      // Logger.log("DEBUG: MATCH FOUND: i = "+i+", Title = "+title+", row = "+dataRow+", total Title = "+totalTitle+", index = "+index+", matched row = "+matchRow+", matched value = "+matchValue+", range to be grouped = "+groupRange.getA1Notation())
      // The row grouping depth is increased by 1.
      groupRange.shiftRowGroupDepth(1);
    }
  }
}

function removeExistingGroups(sheet) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(sheet);
  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

    output

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search