Google Script: copy values from one sheet to another determining the range

Question

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). The IMPORTRANGE won't work as being dynamic any static information added in the other sheet would be irrelevant to the respective ID.

In another thread [1], I got help developing this script that will do exactly that. However, this script will insert the data in the other sheet in Column A. How can I modify the script so I can decide the specific range where I want the script to insert the data (in Sheet 2).

Update: I created this spreadsheet as an example. I'm trying to ensure that the script does the calculation (ie. adding vlaues that are not duplicated in the first available empty row), starting in cel C10 in "Sheet2". It would be also great if I can somehow change that range if I need to: https://docs.google.com/spreadsheets/d/1abESAXFrOHoqRQRNqQGfmAFxlbD10wlprAf1tca4y7o/edit#gid=132361488

Thanks!

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

[1] Google Script: Append new values in column to another sheet

ziganotschka ziganotschka · Accepted Answer · 2020-07-16T14:25:31

target_sheet.getRange(last_row,1,resultArray.length,resultArray[0].length)
.setValues(resultArray);

This line

Takes the sheet stored in the variable target_sheet
Accesses the defined range
Sets values

The syntax to access a range used in your code is getRange(row, column, numRows, numColumns)

whereby the start column is numerical - in your case it's 1 which corresponds to column A.

If you want to modify the start column from A to B:

Just change your range definition from

getRange(last_row,1,resultArray.length,resultArray[0].length)

to

getRange(last_row,2,resultArray.length,resultArray[0].length)

Google Script: copy values from one sheet to another determining the range

1 Answers