Group rows using google apps scripts

Question

I am writing code in which a user can automatically generate a template of lesson and sub-topics. Each lesson will have 10 sub-topics.

I also need to group the rows lesson-wise and topic-wise.

But, I am unable to group the rows lesson-wise and topic-wise. Tried using the macro-recorder, but the code does not work while generating multiple lessons.

EDIT: Working code is updated below.

  function shiftrowgroupdepth() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();


  // start from row 6 and column 2
  var row = 6;
  var col = 2;

  //Ask user for the no. of lessons
  var shlen = Browser.inputBox("Enter no of lessons", Browser.Buttons.OK_CANCEL);

  for (var i = 1; i <= shlen; i++) {

   sheet.getRange(row,col).setValue("Lesson " + i);
   row++;

    Logger.log(spreadsheet.getCurrentCell().getRow())
   sheet.getRange(row, 1, 70, sheet.getMaxColumns()).activate()
  .shiftRowGroupDepth(1);

   // Add sub-topics (1.1, 1.2 ....)

   for (var j=1;j<=10;j++){

     sheet.getRange(row,col).setValue(i+"."+j);
     sheet.getRange(row+1, 1, 6, sheet.getMaxColumns()).activate()
    .shiftRowGroupDepth(1);

     row=row+7;

     }    


  }
};

Can I ask you about your question? 1. What is the image? Can you explain about it? 2. If your image is the current situation, can you provide the result you expect? Because I cannot understand about `group the rows lesson-wise and topic-wise`. This is due to my poor English skill. I apologize for this. — Tanaike, Sep 01 '19 at 22:08
Your question looks very similar to [How can i automatically generate rows with row grouping?](https://stackoverflow.com/q/57728170/1330560) by@kman. I don't suppose this is just a co-incidence? — Tedinoz, Sep 01 '19 at 23:07
The image is how the final solution will look like @Tanaike. — Hadan, Sep 02 '19 at 16:00
@tedinoz - yes, the linked post is mine. But, I was using my work account. — Hadan, Sep 02 '19 at 16:01
@Hadan Might I suggest that you might take any worthwhile comments from the other post and include them in your question, and then delete the other post. No point in anyone putting any energy into that one too. — Tedinoz, Sep 02 '19 at 22:03
Thank you for replying. By the way, where can we see the initial situation? — Tanaike, Sep 02 '19 at 23:25
@Tanaike [How can i automatically generate rows with row grouping?](https://stackoverflow.com/q/57728170/1330560) — Tedinoz, Sep 02 '19 at 23:44
@Tedinoz Thank you for providing the information. Now I noticed that https://stackoverflow.com/users/8488128/hadan and https://stackoverflow.com/users/8127332/kman were the same user. But unfortunately, I couldn't see the clearly vision of the OP's goal even when I saw the thread of the link you provided. This is due to my poor English skill. — Tanaike, Sep 02 '19 at 23:55
"due to my poor English skill... " I think not, @Tanaike. This is an edited version of what the OP confirmed. 'You manage Course reporting in a Google Spreadsheet. Each Course has its own sheet and is grouped by Lessons, Lessons are grouped by Topics, Topics have a duration and are grouped by sub-Topics. The number of Lessons, Topics, Sub-topics, duration, etc varies for each Course; each user advises the details. Goal: build a Course template (including grouping) that can be programmatically built from user input." — Tedinoz, Sep 03 '19 at 01:53
@Tedinoz Thank you for replying and your support. Unfortunately, I cannot still clearly image the vision from the image of this question, the shared Spreadsheet from the link you provided and your explanation. I think that this is due to my lack of understanding. I deeply apologize for this. I think that each row can be grouped by each category. But I cannot understand the method for running the script and the complete input and output OP wants. When I could correctly understand them, I would like to think of the solution. — Tanaike, Sep 03 '19 at 02:21
@Tanaike thanks for your time. This is resolved now. I have updated the code in the post. — Hadan, Sep 03 '19 at 06:21
@Hadan Thank you for replying. I'm glad your issue was resolved. I could study from your question and Tedinoz's comment. Thank you, too. — Tanaike, Sep 03 '19 at 07:47
@Hadan I must admit that this was my first experience of grouping by script. I notice that you include `sheet.getMaxColumns()` as the "Number of Columns" value in the range used for grouping by _row_. Would you mind explaining for the benefit of the uninitiated the benefit/purpose of this. — Tedinoz, Sep 03 '19 at 08:44
I used the macro recorder to generate this function. So, it included the columns function by default. You can remove the part of the code and still it works i.e sheet.getRange(row+1, 1, 6).activate().shiftRowGroupDepth(1). Not sure about the functionality. — Hadan, Sep 03 '19 at 08:49
Interesting discussion at [is sheet.getRange(2:3) a valid expression?](https://stackoverflow.com/q/55853095/1330560). @TheMaster is invariably correct but my quick-and-dirty (and no doubt error-ridden) attempt at specifying just the rows failed. OTOH our latest code (without `getMaxColumns()`) _is_ working - "perfect", as they say, "is the enemy of good". — Tedinoz, Sep 05 '19 at 00:51

Tedinoz · Accepted Answer · 2019-09-03T23:34:54.853

The OP code was very close to the mark. The main changes in this answer are:

When using a 'dot' separator for the topic codes, Google sheets treats the resulting value as a number; this creates problems displaying '1.10'. I changed the separator to a 'dash'. No doubt there is another potential approach using toString - but this was quick and easy.
The Lesson grouping is straightforward; 10 topics, 7 rows per topic = 70 rows.
Topic grouping had been complicated by referring to the location of the "current cell" - which could be anywhere on the sheet. I simplified this by using the row variable, which the OP had already (correctly) incremented.

function so5774532602() {

  var ss = SpreadsheetApp.getActive();
  var sheetname = "OPSheet";
  var sheet = ss.getSheetByName(sheetname);
  var row = 6;
  var col = 2;

  //Ask user for the no. of lessons
  var shlen = Browser.inputBox("Enter no of lessons", Browser.Buttons
    .OK_CANCEL);

  for (var i = 1; i <= shlen; i++) {

    sheet.getRange(row, col).setValue("Lesson " + i);

    // add grouping
    // Logger.log("DEBUG: i = "+i+", lesson range = "+sheet.getRange(+(row + 1), 2, 70, 1).getA1Notation());
    sheet.getRange(+(row + 1), 2, 70, 1).activate()
      .shiftRowGroupDepth(1);

    row++;

    // Add sub-topics (1.1, 1.2 ....)        leave 6 blank rows below each sub-topic. Then, group those blank rows

    for (var j = 1; j <= 10; j++) {
      // Logger.log("DEBUG: i = "+i+", j = "+j+", row = "+row+", col = "+col); // new
      sheet.getRange(row, col).setValue(i + "-" + j);

      // add grouping
      // Logger.log("DEBUG: range details: row = "+(row + 1) +",column = 1"+"number of rows = "+6+", number of columns = 1");
      // Logger.log("DEBUG: topic range = "+sheet.getRange(+(row + 1), 2, 6, 1).getA1Notation());
      sheet.getRange(+(row + 1), 2, 6, 1).activate()
        .shiftRowGroupDepth(1);

      row = row + 7;

    }
  }
}

Edit Two minor changes for formatting

sheet.getRange(row,col).setValue("Lesson " + i).setHorizontalAlignment("center");
Centres the Lesson Number in the column.
sheet.getRange(row,col).setNumberFormat("@").setValue(i+"."+j).setHorizontalAlignment("center");
A return to a 'dot' separator but enables the tenth topic to display as 1.10, etc (credit @Tanaike). Will also center the text in the column.

@Tanaike In the OP's edit, the 'dot' separator is used for the Topic names. This causes the tenth topic to display as 1.1 and 2.1 instead of 1.10 or 2.10 (or at least it does on my system). Do you see a way to improve the display of the tenth topic while still using the 'dot' separator. — Tedinoz, Sep 03 '19 at 08:53
For example, how about using `setNumberFormat()` to the cell? By this, `1.10` can be used as `1.10`. The sample script is `SpreadsheetApp.getActiveSheet().getRange("A1").setNumberFormat("@").setValue("1.10")`. In this case, `1.10` is put to the cell "A1" of the active sheet as a text without using the single quote to the top letter. Is my understanding for your question correct? — Tanaike, Sep 03 '19 at 22:27
"Is my understanding for your question correct? " Totally; refer my edit. @Hadan, Tanaike's suggestion creates an edit worth considering. — Tedinoz, Sep 03 '19 at 23:36
Thanks, both of you :) I had implemented the number formatting for my columns which is not included here. I am adding a few more functionalities to the template. Will share the final code and sheet once done. — Hadan, Sep 05 '19 at 10:19

Group rows using google apps scripts

1 Answers1