使用Google Apps脚本对行进行分组 [英] Group rows using google apps scripts

问题描述

我正在编写代码，用户可以在其中自动生成课程和子主题的模板.每节课将有10个子主题.

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.

工作代码已在下面更新.

  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;

     }    


  }
};

推荐答案

OP代码非常接近商标.该答案的主要变化是:

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

• 在主题代码中使用点"分隔符时，Google表格会将结果值视为数字；这会导致显示"1.10"的问题.我将分隔符更改为破折号".毫无疑问，还有一种使用toString的潜在方法-但这是快速简便的.

• 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.

课程"分组非常简单； 10个主题，每个主题7行= 70行.

The Lesson grouping is straightforward; 10 topics, 7 rows per topic = 70 rows.

通过引用当前单元格"的位置(可能在工作表上的任何位置)，使主题分组变得复杂.我通过使用row变量简化了此过程，OP已经正确地对其进行了递增.

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

1. sheet.getRange(row,col).setValue("Lesson " + i).setHorizontalAlignment("center");
   在列中将课程编号居中.

1. 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");
返回到点"分隔符，但使第十个主题显示为1.10，等等(贷方为@Tanaike).还将文本在列中居中.

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.

这篇关于使用Google Apps脚本对行进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！