G表格:基于列值对行进行分组/分隔，使用值或条件边框格式添加行 [英] G Sheets: Group/Separate Rows based on column value, Adding Rows with value or conditional border format

问题描述

因此，我可以使用多个脚本进行所有操作，其中之一是可以根据C列(日期)自动对工作表进行排序的脚本.

So I have everything working with multiple scripts and one of them is a script to automatically sort the sheet based on Column C (Date).

以下是示例工作表: https://docs.google.com/spreadsheets/d/1nP4kZEikx1li8JNwCjsEc3ooadr0fiboglUizUcUdAQ/edit?usp = sharing

基本上，人们的想法是使人们能够在底部添加行，添加信息，并在设置日期后立即将其移动到其所属位置.问题是我需要在不同的日期之间留一个空格，但是手动添加空行是行不通的，因为自动排序会将日期发送到底部，因为日期单元格为空.

Basically the idea is for people to be able to add rows in the bottom, add the information and as soon as the date is set for it to move where it belongs. The problem is that I need a space between the different days but to manually add an empty row can't work because the auto sort sends it to the bottom because the date cell is blank.

是否通过OnOpen触发器将每个日期和每个子标题行分组?基本上可以识别C列中不同的值，并为每个值添加一行，或者实际上是任何带有颜色格式的文本的东西吗?由于我具有自动排序脚本，因此即使在工作表的末尾添加它们也没关系，因为除D列以外的所有其他单元都是空的，因此应仅在每个日期的顶部将其发送.

Is there anyway to group each date with a sub-header row for each day with a OnOpen trigger? Basically something that recognizes the different values from Column C and adds one row with each value or literally any text with color formatting? Since I have the auto sort script it doesn't even matter if they get added at the end of the sheet, since all the other cells besides column D are empty it should just send it at the top of each date.

我确实遇到过此解决方案 https://stackoverflow.com/a/55944980/13895051 但我似乎无法使其正常工作.

I did come across this solution https://stackoverflow.com/a/55944980/13895051 but I can't seem to make it work.

这是我正在使用的自动排序脚本，

This is the auto sort script I'm using in case it matters

SHEET_NAME = "North Tonawanda";
SORT_DATA_RANGE = "A:S";
SORT_ORDER = [
{column: 3, ascending: true},  // 3 = column number, sorting by descending order
{column: 4, ascending: true} // 1 = column number, sort by ascending order 
];

 function onEdit(e){
 multiSortColumns();
 }
 function multiSortColumns(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName(SHEET_NAME);
 var range = sheet.getRange(SORT_DATA_RANGE);
 range.sort(SORT_ORDER);
 ss.toast('Sort complete.');
}

过去我只使用过几个脚本，所以我不知道我在做什么，并且数据透视表实际上不是一个选择.我将不胜感激！谢谢！

I've only worked with a couple scripts in the past so I have no idea what I'm doing and pivot tables are not really an option. I would appreciate the help! Thanks You!

推荐答案

我知道了.我最终创建了一个菜单来触发所有内容.我感到沮丧，所以我创建了一个用于排序的菜单项，并为了加快处理过程，我录制了一个宏，该宏在顶部添加一行并将日期单元格更改为添加日期".因此，只要有人在一行中添加新日期，他们都可以在顶部添加一行，添加日期，点击排序，然后一切都归其所属.

I figured it out. I ended up creating a menu to trigger everything. I was getting frustrated so I created a menu item for the sort and to speed up the process I recorded a macro adding a row to the top and changing the date cell to "Add Date". So anytime someone adds a row with a new date they can add a row to the top, adding the date, hitting sort and everything goes where it belongs.

这不是一个雄辩的解决方案，但最终效果更好.这样，如果需要编辑多行，它们并不会全都跳来跳去.

It's not an eloquent solution but at the end it worked out better. This way if multiple rows need to be edited they're not all jumping around.

在之前的评论中，我提到我尝试过 https://stackoverflow.com/a/55944980/13895051并说没有用.这是一个简单的修复.我不得不将日期列的格式更改为纯文本.下面是按2列排序的最终产品，并在行组之间添加边框.

In a previous comment I mention I tried the solution from https://stackoverflow.com/a/55944980/13895051 and said it didn't work. It was a simple fix. I had to change the format of the date column to plain text. Below is the finished product sorting by 2 columns and adding border between groups of rows.

感谢您的帮助和耐心！

SHEET_NAME = "North Tonawanda";
SORT_DATA_RANGE = "A:S";
SORT_ORDER = [
{column: 3, ascending: true},  // 3 = column number, sorting by descending order
{column: 4, ascending: true} // 1 = column number, sort by ascending order 
];

function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
  ss.toast('Sort complete.');
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setBorder(null, 
null, null, null, false, false);
    var values = sheet.getRange(3, 3, sheet.getLastRow() - 1, 1).getValues();
    var rangeList = values.reduce(function(ar, e, i) {
      if (i > 0 && values[i - 1][0] != e[0] && e[0] != "") {
        ar.push("A" + (i + 2) + ":S" + (i + 2));
      }
      return ar;
    }, [])
    rangeList.push(sheet.getRange(sheet.getLastRow(), 1, 1, 
sheet.getLastColumn()).getA1Notation());
sheet.getRangeList(rangeList).setBorder(null, null, true, null, false, false, 
"black", SpreadsheetApp.BorderStyle.SOLID_THICK);
  }

这篇关于G表格:基于列值对行进行分组/分隔，使用值或条件边框格式添加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！