根据与另一列的值的比较,将引号分隔符“|”中的行中的值合并到一行中 [英] merge values in column in a row with the quotes separator "|", based on a comparison with the values of another column
问题描述
我认为最好的方法是直接设置一个例子,并且一如既往地感谢您的支持。
简单地说:
sheet1(colA,colB)
sheet2(colA',colB')
sheet1
colA colB
1 xxx 40
2 xxx 42
3 yyy 42
4 yyy 44
。 ...等
表2
colA'colB'
1 xxx 40 | 42
2 yyy 42 | 44
3 ... .. | ..(等)
我将不胜感激谷歌应用程序脚本的一些代码。
谢谢
Gì
编辑(进行中)
我希望通过共享文件回答您的问题。
表格已经存在,代码是部分编写的,我需要帮助。
在ColumnB中没有xxx或yyy或zzz等重复。
sheet_demo 表示脚本应。
函数merge_col (){
变种片= SpreadsheetApp.openById( 0AiuqXkUqDMgzdFM5aVNxeEhPbWFmbGhybl9TVkt4ZWc);
var ss_sorg = sheet.getSheetByName('sheet1');
var ss_dest = sheet.getSheetByName('sheet2');
var sorg_data = ss_sorg.getRange(A2:C)。getValues();
var dest_data = ss_dest.getRange(A2:C)。getValues();
var dest_Cod = [];
var dest_Des = [];
var temp_Cod,temp_Des;
for(var i = 0; i< sorg_data.length; i ++){
temp_Cod = null;
temp_Des = null; (sj_data [i] [0] = sorg_data [j] [0 ]的ToString()匹配(sorg_data [I] [0])&安培;&安培;!sorg_data [I] [0] = ){
temp_Cod = sorg_data [I] [1] ;
temp_Des = sorg_data [i] [2];
break;
}
}
dest_Cod.push([temp_Cod]);
dest_Cod.join('|');
dest_Des.push([temp_Des]);
dest_Des.join('|');
}
如果(dest_Cod.length大于0 || dest_Des.lenght大于0){
ss_dest.getRange(2,2,dest_Cod.length,1).setValues(dest_Cod) ; Logger.log(dest_Cod)
ss_dest.getRange(2,3,dest_Des.length,1).setValues(dest_Des); Logger.log(dest_Des)
}
}
编辑(FINAL)
TKS Serge!
函数merge_col(){
var ss = SpreadsheetApp.getActive()。getSheetByName '为sheet1');
var data = ss.getDataRange()。getValues();
var header = data.shift();
var col1 = data [1] [0];
var col2 ='';
var col3 ='';
var dataSheet2 = [header];
for(var i = 0; i< data.length; i ++){
Logger.log(data [i] [0] +' - '+ col1 +' - '+ data [i] [ 2])
if(col1 == data [i] [0]){
col1 = data [i] [0];
col2 + = data [i] [1] +'| ;
col3 + = data [i] [2] +'| ;
} else {
dataSheet2.push([col1,col2,col3]);
var col1 = data [i] [0];
col2 = data [i] [1] +'| ;
col3 = data [i] [2] +'| ;
}
}
dataSheet2.push([col1,col2,col3]);
Logger.log(dataSheet2);
var sheet2 = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName('sheet2');
sheet2.clear();
sheet2.getRange(1,1,dataSheet2.length,dataSheet2 [0] .length).setValues(dataSheet2);
(b
$ b pre $ 函数merge_col(){
var ss = SpreadsheetApp。 。getActive()getSheetByName( '为sheet1');
var data = ss.getDataRange()。getValues();
var header = data.shift();
var col1 = data [1] [0];
var col2 ='';
var col3 ='';
var dataSheet2 = [header];
for(var i = 0; i< data.length; i ++){
Logger.log(data [i] [0] +' - '+ col1 +' - '+ data [i] [ 2])
if(col1 == data [i] [0]){
col1 = data [i] [0];
col2 + = data [i] [1] +'| ;
col3 + = data [i] [2] +'| ;
} else {
dataSheet2.push([col1,col2,col3]);
var col1 = data [i] [0];
col2 = data [i] [1] +'| ;
col3 = data [i] [2] +'| ;
}
}
dataSheet2.push([col1,col2,col3]);
Logger.log(dataSheet2);
var sheet2 = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName('sheet2');
sheet2.clear();
sheet2.getRange(1,1,dataSheet2.length,dataSheet2 [0] .length).setValues(dataSheet2);
}
as the title suggests I'm trying to find a way to treat some data in a spreadsheet to google app. I think the best way is to directly set an example and thank you in advance for your support as always.
simply:
sheet1 (colA, colB)
sheet2 (colA', colB')
sheet1
colA colB
1 xxx 40
2 xxx 42
3 yyy 42
4 yyy 44
. ... etc.
sheet2
colA' colB'
1 xxx 40 | 42
2 yyy 42 | 44
3 ... .. | .. (etc.)
I would be grateful for a hint of code to google app script. thanks
Gì
EDIT (in progress)
hi,
I hope that by sharing the file answer your questions.
the sheet already exist, the code is written in part and I would need help on this.
there is no duplication in ColumnB for article xxx or yyy or zzz etc. ..
in sheet "sheet_demo" indicates that the script should be.
function merge_col() {
var sheet = SpreadsheetApp.openById("0AiuqXkUqDMgzdFM5aVNxeEhPbWFmbGhybl9TVkt4ZWc");
var ss_sorg = sheet.getSheetByName('sheet1');
var ss_dest = sheet.getSheetByName('sheet2');
var sorg_data = ss_sorg.getRange("A2:C").getValues();
var dest_data = ss_dest.getRange("A2:C").getValues();
var dest_Cod = [];
var dest_Des = [];
var temp_Cod, temp_Des;
for (var i = 0; i < sorg_data.length; i++) {
temp_Cod = null;
temp_Des = null;
for (var j = 1; j < sorg_data.length; j++) {
if (sorg_data[i][0] == sorg_data[j][0].toString().match(sorg_data[i][0]) && sorg_data[i][0] != "") {
temp_Cod = sorg_data[i][1];
temp_Des = sorg_data[i][2];
break;
}
}
dest_Cod.push([temp_Cod]);
dest_Cod.join('|');
dest_Des.push([temp_Des]);
dest_Des.join('|');
}
if (dest_Cod.length > 0 || dest_Des.lenght > 0) {
ss_dest.getRange(2, 2, dest_Cod.length, 1).setValues(dest_Cod); Logger.log(dest_Cod)
ss_dest.getRange(2, 3, dest_Des.length, 1).setValues(dest_Des); Logger.log(dest_Des)
}
}
EDIT (FINAL) TKS Serge!
function merge_col() {
var ss = SpreadsheetApp.getActive().getSheetByName('sheet1');
var data = ss.getDataRange().getValues();
var header = data.shift();
var col1 = data[1][0];
var col2 = '';
var col3 = '';
var dataSheet2 = [header];
for(var i=0 ; i<data.length ; i++){
Logger.log(data[i][0]+' - '+col1+' - '+data[i][2])
if(col1==data[i][0]){
col1 = data[i][0];
col2+=data[i][1]+' | ';
col3+=data[i][2]+' | ';
}else{
dataSheet2.push([col1,col2,col3]);
var col1 = data[i][0];
col2 = data[i][1]+' | ';
col3 = data[i][2]+' | ';
}
}
dataSheet2.push([col1,col2,col3]);
Logger.log(dataSheet2);
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
sheet2.clear();
sheet2.getRange(1, 1, dataSheet2.length, dataSheet2[0].length).setValues(dataSheet2);
}
Here is a code that does exactly what you expected using a simple loop and a comparison.
function merge_col() {
var ss = SpreadsheetApp.getActive().getSheetByName('sheet1');
var data = ss.getDataRange().getValues();
var header = data.shift();
var col1 = data[1][0];
var col2 = '';
var col3 = '';
var dataSheet2 = [header];
for(var i=0 ; i<data.length ; i++){
Logger.log(data[i][0]+' - '+col1+' - '+data[i][2])
if(col1==data[i][0]){
col1 = data[i][0];
col2+=data[i][1]+' | ';
col3+=data[i][2]+' | ';
}else{
dataSheet2.push([col1,col2,col3]);
var col1 = data[i][0];
col2 = data[i][1]+' | ';
col3 = data[i][2]+' | ';
}
}
dataSheet2.push([col1,col2,col3]);
Logger.log(dataSheet2);
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
sheet2.clear();
sheet2.getRange(1, 1, dataSheet2.length, dataSheet2[0].length).setValues(dataSheet2);
}
这篇关于根据与另一列的值的比较,将引号分隔符“|”中的行中的值合并到一行中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!