Google表格-水平合并单行中的相同单元格 [英] Google Sheets - Horizontal merge identical cells in single row
问题描述
我正在努力更新以下示例代码: 合并具有相同单词的单元格 由@Tanaike提供
I am working towards updating a sample of code seen at: Merge cells with same words Provided by @Tanaike
我实质上是在尝试与上面的示例代码做相同的事情，但是有一个不同之处，就是我希望将其旋转为在类似的列上水平工作，而不是在类似的行上垂直工作.
I am essentially trying to do the same thing as the sample code above, but with one difference in that I am hoping to pivot this to work horizontally on like columns, instead of vertically against like rows.
我尽了最大的努力来翻转功能，但是我是个初学者，陷入了僵局.下面是我编辑的代码段，我标记了我的理解范围之外的那一行，因此我认为那里有些问题.
I've done my best to flip the functionality, but I am very much a beginner and have run into an impasse. The below is my edited code snippet, and I've marked the particular line where my understanding goes out the window, so I assume there is something wrong there, or so.
function mergeMonths() {
var start = 6; // Start row number for values.
var c = {};
var k = "";
var offset = 0;
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BlockingChart1");
// Retrieve values of column B.
var data = ss
.getRange(4, start, 1, ss.getLastColumn())
.getValues()
.filter(String);
// Retrieve the number of duplicate values. //This is where my eyebrow starts to raise.
data.forEach(function(e) {
c[e[0]] = c[e[0]] ? c[e[0]] + 1 : 1;
});
// Merge cells.
data.forEach(function(e) {
if (k != e[0]) {
ss.getRange(4, start + offset, 1, c[e[0]]).merge();
offset += c[e[0]];
}
k = e[0];
});
}
只需100％清除: 我希望检查给定(排序)行中具有相同内容且重复的单元格，然后合并重复的单元格的单元格. (我正在制作日历类型图表，每个单元格每周一次，但希望将顶部标记为几个月，因此合并重复的"... Dec，Jan，Jan，Jan，Jan，Feb ..."进入标题.
Just to be 100% clear: I am hoping to check cells in a given (sorted) row for cells with identical contents that are repeated and then merge the repeated cells. (I am working on a calendar type chart, at a weekly level per cell, but want to label the top to be months, so merging the repeating "...Dec,Jan, Jan, Jan, Jan, Feb..." into headers.
如果有人想向我推荐一些可能对我的旅程有帮助或可以伸出援助之手的书，将不胜感激.
If anyone wants to point me to some reading which may help me along my journey, or could lend a hand, that would be greatly appreciated.
推荐答案
如果我理解正确，则希望转换一行像这样的单元格:
If I understand correctly you want to transform a row of cells that looks like this:
Jan | Jan | Feb | Feb | Feb | Mar | Apr | Apr | Apr |
对此:
Jan | Feb | Mar | Apr |
如果是这样，我可以帮忙！
If so, I can help!
I think part of your issue comes from your call to getRange(row, column, numRows, numColumns).
在注释中，您说var start = 6是开始行，但将其放在列位置.不确定您的意图是什么，但是使用startRow和startCol变量可能会更清楚.例如，
In a comment you say var start = 6 is the start row, but you put it in the column spot. Not sure what you intended, but it might be more clear to have a startRow and a startCol variable. Eg,
var startRow = 1;
var startCol = 1;
// Later...
ss.getRange(startRow, startCol, 1, ss.getLastColumn())
在Google脚本中调用Range.getValues()会返回二维数组数组".在我提供的示例单元格中，您将得到类似的内容:
Calling Range.getValues() in a Google Script returns a two dimensional "array-of-arrays." With the example cells I provided you would get something like:
data = [['Jan', 'Jan', 'Feb', 'Feb', 'Feb', 'Mar', 'Apr', 'Apr', 'Apr']]
由于我们仅处理一行，因此我们可以采用此外部数组的第一项并调用 that 数据，从而简化了一些事情.所以我会做:
Since we're just dealing with one row, we can take the first item of this outer array and call that data, simplifying things a bit. So I would do:
var data = ss
.getRange(startRow, startCol, 1, ss.getLastColumn())
.getValues()
.filter(String)[0];
哪个给您:
data = ['Jan', 'Jan', 'Feb', 'Feb', 'Feb', 'Mar', 'Apr', 'Apr', 'Apr']
步骤2.计算重复值
下一部分代码用于计算每月重复的次数.使用我们的漂亮的一维数组，我们可以使代码更简单:
Step 2. Counting duplicate values
The next bit of code deals with counting how many times each month is duplicated. With our nice, one-dimensional array we can make the code a little simpler:
data.forEach(function(e) {
c[e] = c[e] ? c[e] + 1 : 1;
});
在这里，我们遍历数组data，依次处理每个条目"e". (e将是"Jan"，然后是"Jan"，然后是"Feb").我们将计数存储在对象c中，该对象开始为空白，完成后将如下所示:
Here we're looping over the array data, dealing with each entry "e" in turn. (e will be "Jan", then "Jan" again, then "Feb"). We're storing the counts in an object, c which starts off blank and will look like this when we're finished:
c = {Feb: 3.0, Apr: 3.0, Jan: 2.0, Mar: 1.0}
The ? and : stuff is fancy JavaScript ternary syntax, which is basically saying:
c[e] = c[e] ? c[e] + 1 : 1;
[-----] [-----][-------][---]
| | | |
| v | |
(1) "have we already created an entry in c for this month e (eg, "Jan")?
| | |
| v |
(2) "if so, COUNT is the current value (look it up) plus 1
| |
| v
(3) | "if not, COUNT is 1"
|
v
(4) "store the COUNT we found in c"
第3步.合并重复的值
最后一步可能像这样:
Step 3. Merge duplicate values
The final step could look like this:
data.forEach(function(e) {
if (e != k) {
ss.getRange(startRow, startCol + offset, 1, c[e]).merge();
offset += c[e];
}
k = e;
});
同样，事情要简单一点，因为data现在是一个1d数组，而不是2d数组.
Again, things are a tiny bit simpler because data is now a 1d array instead of a 2d array.
function mergeMonths() {
var startRow = 1;
var startCol = 1;
var c = {};
var k = "";
var offset = 0;
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BlockingChart1");
// Get data from sheet
var data = ss
.getRange(startRow, startCol, 1, ss.getLastColumn())
.getValues()
.filter(String)[0];
// Count duplicates
data.forEach(function(e) {
c[e] = c[e] ? c[e] + 1 : 1;
});
// Merge duplicate cells
data.forEach(function(e) {
if (e != k) {
ss.getRange(startRow, startCol + offset, 1, c[e]).merge();
offset += c[e];
}
k = e;
});
}
快速提示
最后一条提示:记录器对于查看代码中的内容非常有用.在测试代码时，我会像这样粘贴行:
Quick Tip
One final tip: The logger is super helpful for seeing what's going on in your code. When you're testing your code I would stick lines like:
Logger.log("data: %s", data);
和
Logger.log("e: %s", e);
遍历所有地方，然后查看Google脚本日志(查看>日志)以了解变量在不同点的位置.
All over the place, then check the Google Scripts log (view > logs) to understand what your variables are at different points.
这篇关于Google表格-水平合并单行中的相同单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
