Merging google sheets: match with specific col values and merge data in 2 different sheets

Question

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

Answer 1

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