Syncing data between Google Sheets: Matching specific column values and updating rows

Other Questions Google Sheets
1

I’m trying to find a way to sync data between two sheets in Google Sheets. Here’s what I want to do:

  1. I have Sheet1 and Sheet3
  2. Both sheets have a common key column (Column C)
  3. I want to match the values in this key column
  4. When there’s a match, I want to update the entire row in Sheet1 with data from Sheet3

I tried writing a script, but it’s not working. Here’s a simplified version of what I’m aiming for:

function updateSheets() {
  var workbook = SpreadsheetApp.getActive();
  var sheet1 = workbook.getSheetByName('Sheet1');
  var sheet3 = workbook.getSheetByName('Sheet3');
  
  var keyColumn = 3; // Column C
  
  var sheet1Data = sheet1.getDataRange().getValues();
  var sheet3Data = sheet3.getDataRange().getValues();
  
  for (var i = 1; i < sheet1Data.length; i++) {
    var key1 = sheet1Data[i][keyColumn - 1];
    
    for (var j = 1; j < sheet3Data.length; j++) {
      var key3 = sheet3Data[j][keyColumn - 1];
      
      if (key1 === key3) {
        sheet1.getRange(i + 1, 1, 1, sheet1Data[i].length).setValues([sheet3Data[j]]);
        break;
      }
    }
  }
}

Can someone help me fix this or suggest a better approach? Thanks!

2

Hey Tom_Artist, I’ve been in a similar situation before. While the script approach can work, it might not be the most efficient for large datasets. Have you considered using Google Sheets’ built-in functions? I’ve had great success with ARRAYFORMULA combined with VLOOKUP for this kind of task.

Here’s a formula you could try in Sheet1:

=ARRAYFORMULA(IF(C2:C<>“”, VLOOKUP(C2:C, Sheet3!C:Z, {1,2,3,4,5,6,7,8,9,10}, FALSE), “”))

Replace the column references and numbers as needed. This formula will automatically update rows in Sheet1 based on matches in Column C with Sheet3. It’s faster than scripts for most cases and updates in real-time.

If you’re dealing with a massive amount of data, though, you might want to look into Google Apps Script’s batch operations for even better performance. Let me know if you need more details on either approach!

3

Your approach is on the right track, but there are a few optimizations we can make to improve efficiency. Instead of nested loops, consider using a hash table (object in JavaScript) to store the data from Sheet3. This way, you can look up matching keys in constant time.

Here’s a revised version of your script:

function updateSheets() {
  var workbook = SpreadsheetApp.getActive();
  var sheet1 = workbook.getSheetByName('Sheet1');
  var sheet3 = workbook.getSheetByName('Sheet3');
  
  var keyColumn = 3;
  var sheet1Data = sheet1.getDataRange().getValues();
  var sheet3Data = sheet3.getDataRange().getValues();
  
  var sheet3Map = {};
  for (var i = 1; i < sheet3Data.length; i++) {
    sheet3Map[sheet3Data[i][keyColumn - 1]] = sheet3Data[i];
  }
  
  for (var i = 1; i < sheet1Data.length; i++) {
    var key = sheet1Data[i][keyColumn - 1];
    if (sheet3Map[key]) {
      sheet1.getRange(i + 1, 1, 1, sheet1Data[i].length).setValues([sheet3Map[key]]);
    }
  }
}

This should significantly improve performance for larger datasets. Let me know if you need any clarification.

4

Yo Tom_Artist, ur script looks alright but it might be slow for big sheets. have u considered using VLOOKUP or QUERY functions instead? they’re usually faster for this kinda stuff. if u need help with those lemme know, i can show u some examples