合并Google表格:与特定的col值匹配并在2个不同的表格中合并数据 [英] Merging google sheets: match with specific col values and merge data in 2 different sheets

问题描述

我想问问是否有任何可能的方法来设置类似于以下附加组件的简单版本的应用程序脚本.

如果我误解了您的问题，请告诉我.我想修改它.那时，为了进行修改，您可以共享样本表吗?当然，请从其中删除您的私人信息.

  function myFunction2(){var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet1 = ss.getSheetByName("Sheet 1");var sheet3 = ss.getSheetByName("Sheet 3");var tabsheet1 = sheet1.getRange(2，3，sheet1.getLastRow()-1，sheet1.getLastColumn()-2);//修改的var datasheet1 = tabsheet1.getValues();var datasheet1d = tabsheet1.getDisplayValues();//添加var tabsheet3 = sheet3.getRange(2，3，sheet3.getLastRow()-1，sheet3.getLastColumn()-2);//添加var datasheet3 = tabsheet3.getValues();//修改的var datasheet3d = tabsheet3.getDisplayValues();//添加for(var i = 0; i< datasheet1.length; i ++){for(var j = 0; j< datasheet3.length; j ++){if(datasheet1d [i] [0] == datasheet3d [j] [0]){//已修改datasheet3 [j] .push(");//修改的datasheet1 [i] = datasheet3 [j];//修改的}}}tabsheet1.setValues(datasheet1);} 

  function myFunction2(){var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet1 = ss.getSheetByName("comment_log");var sheet3 = ss.getSheetByName("CM");var tabsheet1 = sheet1.getRange(2，3，sheet1.getLastRow()-1，sheet1.getLastColumn()-2);//修改的var datasheet1 = tabsheet1.getValues();var datasheet1d = tabsheet1.getDisplayValues();//添加var tabsheet3 = sheet3.getRange(2，3，sheet3.getLastRow()-1，sheet3.getLastColumn()-2);//添加var datasheet3 = tabsheet3.getValues();//修改的var datasheet3d = tabsheet3.getDisplayValues();//添加for(var i = 0; i< datasheet1.length; i ++){for(var j = 0; j< datasheet3.length; j ++){if(datasheet1d [i] [0] == datasheet3d [j] [0]){//已修改if(datasheet1 [i] .length！= datasheet3 [j] .length){for(var k = 0; k  

I would like to ask if there is any possible way to set up a simple version of app script that is similar to the below add-ons.

https://chrome.google.com/webstore/detail/merge-sheets/gdmgbiccnalapanbededmeiadjfbfhkl?utm_source=ablebits

Basically, I've got sheet 1 and sheet 3, both sheets will have a common key column with specific values in each cell, and I would like to map both sheets with the data in that column then update the whole row data in the other sheets (For example, if i updated the sheet 3 then it map that col value in sheet 1, then paste the data in the corresponding row)

I have came up with a code that runs but no changes have been made, could anyone please advise how can I modify it to a simple version similar to the above add-ons? Thanks in advance.

I would like to populate the date from sheet 3 to sheet 1 after the code run, while the data from col C is matched in both sheets, please see example below, thanks!

For example, the data in sheet 1 highlighted row is having its key col with col C for common lookup value with sheet 3 while the row sequence is diff with sheet 3 (Please see the next photo, thanks!)

As you can see in sheet 3, the data of the whole row is inserted to the correct row according to the col C key col value which matched with sheet 1.

function myFunction2(){
    // Get your spreadsheet and the sheets "TB" and "2"
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet1 = ss.getSheetByName("Sheet 1");
    var sheet3 = ss.getSheetByName("Sheet 3");

    // Get the range on sheet "TB"
    var tabsheet1 = sheet1.getRange(2, 2, sheet1.getLastRow(), 
    sheet1.getLastColumn());

    // Get the values to compare
    var datasheet1 = tabsheet1.getValues();
    var datasheet3 = sheet3.getRange(2, 2, sheet3.getLastRow(), sheet3.getLastColumn());

    for(var i = 0; i < datasheet1.length; i++){

        for(var j = 0; j < datasheet3.length; j++){

            // Compare data: if they're the same, put the value 
            if(datasheet1[i][0]=(datasheet3[j][0]) == 0){

                //if(datasheet1[i][0].localeCompare(datasheet3[j][0]) == 0){

                datasheet1[i][1] = datasheet3[j][1];
            }
        }
    }

    // Take the modified tab and put it on the spreadsheet
    tabsheet1.setValues(datasheet1);
}

解决方案

You want to copy the data of sheet 3 to sheet 1. For this situation, when the values of column C of sheet 3 and sheet 1 is the same, you want to copy the row of sheet 3 to sheet 1, because the order of rows for sheet 3 and sheet 1 are different. If my understanding is correct, how about this modification?

Modification points :

• In order to retrieve the values from column C to last column, it uses getRange(2, 3, sheet1.getLastRow(), sheet1.getLastColumn()).
• In your script, datasheet3 is the range.
• About if(datasheet1[i][0]=(datasheet3[j][0]) == 0){, in order to comare the date, I used getDisplayValues().
  • By this, the values retrieved as a string are compared.

Modified script :

function myFunction2(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet 1");
  var sheet3 = ss.getSheetByName("Sheet 3");
  var tabsheet1 = sheet1.getRange(2, 3, sheet1.getLastRow(), sheet1.getLastColumn());
  var datasheet1 = tabsheet1.getValues();
  var datasheet1d = tabsheet1.getDisplayValues(); // Added
  var tabsheet3 = sheet3.getRange(2, 3, sheet3.getLastRow(), sheet3.getLastColumn()); // Added
  var datasheet3 = tabsheet3.getValues(); // Modified
  var datasheet3d = tabsheet3.getDisplayValues(); // Added
  for (var i = 0; i < datasheet1.length; i++) {
    for (var j = 0; j < datasheet3.length; j++) {
      if (datasheet1d[i][0] == datasheet3d[j][0]) { // Modified
        datasheet1[i] = datasheet3[j]; // Modified
      }
    }
  }
  tabsheet1.setValues(datasheet1);
}

Reference :

• getDisplayValues()

If I misunderstand your question, please tell me. I would like to modify it. At that time, in order to modify, can you share the sample sheet? Of course, please remove your private information from it.

Edit 1 :

function myFunction2(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet 1");
  var sheet3 = ss.getSheetByName("Sheet 3");
  var tabsheet1 = sheet1.getRange(2, 3, sheet1.getLastRow() - 1, sheet1.getLastColumn() - 2); // Modified
  var datasheet1 = tabsheet1.getValues();
  var datasheet1d = tabsheet1.getDisplayValues(); // Added
  var tabsheet3 = sheet3.getRange(2, 3, sheet3.getLastRow() - 1, sheet3.getLastColumn() - 2); // Added
  var datasheet3 = tabsheet3.getValues(); // Modified
  var datasheet3d = tabsheet3.getDisplayValues(); // Added
  for (var i = 0; i < datasheet1.length; i++) {
    for (var j = 0; j < datasheet3.length; j++) {
      if (datasheet1d[i][0] == datasheet3d[j][0]) { // Modified
        datasheet3[j].push(""); // Modified
        datasheet1[i] = datasheet3[j]; // Modified
      }
    }
  }
  tabsheet1.setValues(datasheet1);
}

Edit 2 :

function myFunction2(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("comment_log");
  var sheet3 = ss.getSheetByName("CM");
  var tabsheet1 = sheet1.getRange(2, 3, sheet1.getLastRow() - 1, sheet1.getLastColumn() - 2); // Modified
  var datasheet1 = tabsheet1.getValues();
  var datasheet1d = tabsheet1.getDisplayValues(); // Added
  var tabsheet3 = sheet3.getRange(2, 3, sheet3.getLastRow() - 1, sheet3.getLastColumn() - 2); // Added
  var datasheet3 = tabsheet3.getValues(); // Modified
  var datasheet3d = tabsheet3.getDisplayValues(); // Added
  for (var i = 0; i < datasheet1.length; i++) {
    for (var j = 0; j < datasheet3.length; j++) {
      if (datasheet1d[i][0] == datasheet3d[j][0]) { // Modified
        if (datasheet1[i].length != datasheet3[j].length) {
          for (var k = 0; k < datasheet1[i].length - datasheet3[j].length; k++) {
            datasheet3[j].push(datasheet1[i][datasheet1[i].length - 1]);
          }
        }
        datasheet1[i] = datasheet3[j]
      }
    }
  }
  tabsheet1.setValues(datasheet1);
}

这篇关于合并Google表格:与特定的col值匹配并在2个不同的表格中合并数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！