Google Script: Append new values in column to another sheet

Question

I have been working on this project and can't get it to work quite the way I want.

I have two sheets with unique IDs in one column. I need to make sure whenever a new ID is added in one sheet (Sheet1), it is copied to the last empty row in the other sheet (Sheet2). Making sure it ignores it if the value already exist (to avoid duplicates). The idea was to create a function and have it triggered every minute or so.

Below is the example spreadsheet and the script that is almost working now. The only issue is that instead of adding new values to the first empty row at the bottom, is copying them in a weird order (see Sheet2 to see what I mean). The header in my spreadsheet is in row 4, and I'm guessing the issue has something to do with that?

Example Spreadsheet: https://docs.google.com/spreadsheets/d/12Ty5pWhLULDn6GZ1Qb3RqR556TXjIL7GdWvxW2xJ7M0/edit?usp=sharing

Script:

function updateSheet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = "Sheet1"; 
var destinationSheet = "Sheet2";
var source_sheet = ss.getSheetByName(sourceSheet);
var target_sheet = ss.getSheetByName(destinationSheet);
var lastCol = target_sheet.getLastColumn();
var lastRow = target_sheet.getLastRow();

//assumes headers in row 1
var r = target_sheet.getRange(2,1, lastRow - 1, lastCol);

//Note the use of an array
r.sort([{column: 1, ascending: true}]);

  // Process sheet
  _updateSpreadsheet(source_sheet, target_sheet);

}


function _updateSpreadsheet(source_sheet, target_sheet) {
  var last_row = target_sheet.getLastRow();  
  var source_data = source_sheet.getDataRange().getValues();
  var target_data = target_sheet.getDataRange().getValues();
  var resultArray = [];
  
  for (var n = 1 ; n < source_data.length ; n++) {
    var keep = true;
      for(var p in target_data) {
       if (source_data[n][0] == target_data[p][0]) {
                 keep = false; break;
      }
    }
    Logger.log(keep);
 //   if(keep){ resultArray.push(source_data[n])};
//    if(keep){ resultArray.push([source_data[n][0]])};
    var columnsToKeep = [0];
    var tempData = [];
    if(keep){
      for(var c in columnsToKeep){ tempData.push(source_data[n][columnsToKeep[c]])}
    resultArray.push(tempData);
    }     
  }
  last_row++;
  Logger.log(resultArray);
  if(resultArray.length>0){
    target_sheet.getRange(last_row,1,resultArray.length,resultArray[0].length).setValues(resultArray);
  }
}

Tanaike Tanaike · Accepted Answer · 2020-07-01T22:56:00

I believe your goal as follows.

You want to append unique values from "Sheet1" to "Sheet2" after it sorts the cells "A2:A" on "Sheet2" by modifying your script.

For this, how about this modification?

Modification points:

In your case, how about retrieving the cells "A4:A" from source_sheet instead of getDataRange()?
In your script, when no values in "Sheet2", an error occurs at target_sheet.getRange(2,1, lastRow - 1, lastCol).

When above points are reflected to your script, it becomes as follows.

Modified script:

function updateSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = "Sheet1"; 
  var destinationSheet = "Sheet2";
  var source_sheet = ss.getSheetByName(sourceSheet);
  var target_sheet = ss.getSheetByName(destinationSheet);
  var lastCol = target_sheet.getLastColumn();
  var lastRow = target_sheet.getLastRow();
  if (lastRow > 1) { // <--- Added
    var r = target_sheet.getRange(2,1, lastRow - 1, lastCol);
    r.sort([{column: 1, ascending: true}]);
  }
  _updateSpreadsheet(source_sheet, target_sheet);
}

function _updateSpreadsheet(source_sheet, target_sheet) {
  var last_row = target_sheet.getLastRow();  
  var source_data = source_sheet.getRange("A4:A" + source_sheet.getLastRow()).getValues(); // <--- Modified
  var target_data = target_sheet.getDataRange().getValues();
  var resultArray = [];
  for (var n = 0 ; n < source_data.length ; n++) { // <--- Modified
    var keep = true;
      for(var p in target_data) {
       if (source_data[n][0] == target_data[p][0]) {
         keep = false; break;
       }
    }
    var columnsToKeep = [0];
    var tempData = [];
    if(keep){
      for(var c in columnsToKeep){ tempData.push(source_data[n][columnsToKeep[c]])}
      resultArray.push(tempData);
    }     
  }
  last_row++;
  resultArray = resultArray.filter(String);  // <--- Added
  if(resultArray.length>0){
    target_sheet.getRange(last_row,1,resultArray.length,resultArray[0].length).setValues(resultArray);
  }
}

By this modification, when "Sheet2" has no values, the header ID is put to the 1st row. After 2nd run, 1st row is ignored and the cells below 2nd row are sorted, and then, the new unique value is appended.

Google Script: Append new values in column to another sheet

1 Answers

Modification points:

Modified script:

References: