Copy Row from one Sheet to another sheet which is linked in the row-CodePudding

I have previously been able to copy rows from one Spreadsheet to another, however what I am trying to do now is copy a row to a specific spreadsheet that is linked on the same row and I can't seem to work it out.

I have around 500 rows of names with data (I cannot share a sheet as my firms Google does not allow for sharing externally but I have a screenshot of dummy data below). I used a formula to find the Unique names and then created a Google Sheet for each person and linked the Sheet back to the master data.

Master Data Sheet (tab is called Sheet1)

I am looking for a script that will work through the sheet, copying the rows to the link that is on the same row but I just can't figure it out - the following script does not work but I am at a loss so any help would be appreciated. Apologies if I haven't explained myself very well!!

function copyTo(){

var sSheet = SpreadsheetApp.getActiveSpreadsheet();
var srcSheet = sSheet.getSheetByName("Sheet1");

var data = srcSheet.getDataRange().getValues();
for(var i = 1; i < data.length; i  ) {
  var row = data[i];
  var link = row[3];
  var id = row[4];
  var complete = row[5];

  var COMPLETE = "Complete"

  if(link !== "" && complete !== "Complete"){

    var srcRange = srcSheet.getRange("A"   i   ":C"   i);

  var linkID = id.toString()

  var tarSS = DriveApp.getFileById(linkID);
  var tarSheet = tarSS.getSheetbyName("Sheet1");

   var tarRow = tarSheet.getLastRow();
  //tarSheet.insertRowAfter(tarRow);
  var tarRange = tarSheet.getRange("A"   (tarRow 1)   ":C"   (tarRow 1));
  
  srcRange.copyTo(tarRange);
  srcSheet.getRange(i 1,6).setValue(COMPLETE)

 }
 }};

CodePudding user response：

Try this:

function copyTo() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Sheet1");
  var vs = sh.getDataRange().getValues();
  for (var i = 1; i < vs.length; i  ) {
    var row = vs[i];
    var link = row[3];
    var id = row[4];
    var complete = row[5];
    var COMPLETE = "Complete"
    if (link !== "" && complete !== "Complete") {
      var rg = sh.getRange("A"   i   ":C"   i);
      var linkID = id.toString()
      var tarSS = SpreadsheetApp.openById(linkID);
      var tarSheet = tarSS.getSheetbyName("Sheet1");
      var tarRow = tarSheet.getLastRow();
      var tarRange = tarSheet.getRange("A"   (tarRow   1)   ":C"   (tarRow   1));
      rg.copyTo(tarRange);
      sh.getRange(i   1, 6).setValue(COMPLETE)
    }
  }
};