根据与另一列的值进行比较，使用引号分隔符"|"将一行中的值合并 [英] merge values in column in a row with the quotes separator "|", based on a comparison with the values of another column

问题描述

正如标题所示，我正在尝试寻找一种方法来处理电子表格中到Google应用程序中的某些数据. 我认为最好的方法是直接树立榜样，并一如既往地感谢您的支持.

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.

简单地:

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

对于Google应用程序脚本的代码提示，我将不胜感激. 谢谢

I would be grateful for a hint of code to google app script. thanks

Gì

编辑(进行中)

我希望通过共享文件回答您的问题.

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.

在ColumnB中，商品xxx或yyy或zzz等没有重复.

there is no duplication in ColumnB for article xxx or yyy or zzz etc. ..

工作表"sheet_demo"中表示该脚本应为

in sheet "sheet_demo" indicates that the script should be.

Spreadsheeet demo

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)
  }
}

编辑(最终) TKS Serge！

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屋！