CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Julie-Anne
Julie-Anne

Reputation: 23

Google Apps Script group rows dynamically

I would like to group rows automatically on a google sheet based on a set of rows which changes daily as well as their length (depth of the groups). If you see the pictures below, I would like all the rows with a * in column A to be grouped together.

From this :

To this :

Here is my code where I first remove the groups that were previously there, find all the "group headers" and place them in a list called index, then I attempt to group the rows using (index, l). However the only group created is between line 4 and 5.

function GroupRows() {


var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Roadmap Live')
  
  var range = sheet.getDataRange()
  var source = sheet.getDataRange().getDisplayValues()
  var vs = range.getValues()
  var index = []

  // remove groups before creating new ones

  source.forEach((r,i)=> {
    let d = sheet.getRowGroupDepth(i+1)
    if (d>=1){
      sheet.getRowGroup(i+1,d).remove()
    }
  })

  //Find rows to group

  for (var i = 1; i<source.length; i++){
    if (source[i][0] == "-"){
      // Logger.log(source[i][0])
      index.push(i+1) //this collects all the rows that has * in them
      }
      }
  Logger.log(index)


  //Group rows

  for(var i = 0;i<index.length-1;i++){
       var rangetomodify = sheet.getRange(index[i],1)
       var l = index[i+1]-index[i]-1 
       var k = index[i]
      rangetomodify.shiftRowGroupDepth(1)
      sheet.getRowGroup(index,l)
      }

}

enter image description here

I suppose there must be some map function I should use but I'm not sure how to implement it?

Upvotes: 0

Views: 1386

Answers (1)

Tanaike
Tanaike

Reputation: 201428

I believe your goal is as follows.

  • You want to group the rows when column "A" has a value of *.

In your script, it seems that index of sheet.getRowGroup(index, l) is an array. So, I think that an error occurs there. And also, in your script, it seems that the value of * is not checked. I think that the reason for your current issue of However the only group created is between lines 4 and 5. might be due to this.

In this case, how about the following sample script?

Sample script 1:

function GroupRows() {
  const sheetName = "Roadmap Live"; // This is from your script.
  const spreadsheet = SpreadsheetApp.getActive();
  const sheet = spreadsheet.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  const values = sheet.getRange("A4:A" + lastRow).getDisplayValues();
  for (let i = 4; i <= lastRow; i++) {
    const d = sheet.getRowGroupDepth(i);
    if (d > 0) sheet.getRowGroup(i, d).remove();
  }
  values.forEach(([a], i) => {
    if (a == "*") {
      sheet.getRange(i + 4, 1).shiftRowGroupDepth(1);
    }
  });
  sheet.collapseAllRowGroups();
}
  • When this script is run, the rows that the column "A" has * are grouped.

Sample script 2:

I thought that in the current stage when the above script is run, the process cost might be a bit high. So, as another approach, in order to reduce the process cost, I would like to propose using Sheets API. The sample script is as follows.

In this case, please enable Sheets API at Advanced Google services.

function sample2() {
  const sheetName = "Roadmap Live"; // This is from your script.
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(sheetName);
  const ssId = ss.getId();
  const sheetId = sheet.getSheetId();
  sheet.expandAllRowGroups();
  const n = Sheets.Spreadsheets.get(ssId, { ranges: [sheetName] }).sheets[0].rowGroups.reduce((n, { depth }) => n < depth ? depth : n, 0);
  const requests1 = Array(n).fill("").map(_ => ({ deleteDimensionGroup: { range: { sheetId, dimension: "ROWS" } } }));
  const values = sheet.getRange("A4:A" + sheet.getLastRow()).getDisplayValues();
  const requests2 = values.flatMap(([a], i) => a == "*" ? { addDimensionGroup: { range: { sheetId, startIndex: i + 3, endIndex: i + 4, dimension: "ROWS" } } } : []);
  Sheets.Spreadsheets.batchUpdate({ requests: [...requests1, ...requests2] }, ssId);
  sheet.collapseAllRowGroups();
}
  • When this script is run, the same result as the above script is run with the low process cost.

References:

Upvotes: 1

Related Questions