Copy rows from one sheet to another

Question

I need to create a master sheet for our managers that only include activity happening in their areas (I have a script for this that does what I need). These sheets are populated from my master sheet (and are not in the same workbook).

I need to have rows copy to the applicable sheet when there is a new submission. My code below dumps them all to one sheet and I'm not sure why. I'm assuming I don't have the right condition but how do you say "move rows to this sheet when it's this manager"? I thought calling the sheet ID by row would be enough, but I guess not.

Would I then have to do an if else statement for every manager? That will get tedious since I have about 80 managers.

Below is my current code. Any help or advice is appreciated!

function moveRows(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Form Responses 1");
  var data = sh.getDataRange().getValues();

    // Grab the Headers
  var headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues();
  var nameCol = headers[0].indexOf('Enter Your Name');
  var candCol = headers[0].indexOf('Choose Candidate');
  var typeCol = headers[0].indexOf('Merged Experience');
  var docCol = headers[0].indexOf('Document Link');
  var timeCol = headers[0].indexOf('Timestamp');
  var subjectCol = headers[0].indexOf('Choose the Content Area');
  var ratingsCol = headers[0].indexOf('Ratings Summary');
  var accessCol = headers[0].indexOf('Access');
  var activityCol = headers[0].indexOf('Copied to Activity Sheet');
  var masterCol = headers[0].indexOf('Master Activity Sheet');
  var target = [];

  // Loop over the values line by line
  for (var i = 0; i < data.length; i++) {
  var row = data[i];
  var name = row[nameCol];
  var activity = row[activityCol];
  var master = row[masterCol];
  var candidate = row[candCol];
  var type = row[typeCol];
  var subject = row[subjectCol];
  var ratings = row[ratingsCol];
  var access = row[accessCol];
  var guide = row[docCol];
  var time = row[timeCol];

  if (activity == "") { // if condition is true copy the whole row to target
   target.push([time,name,candidate,subject,type,guide,ratings]);
   Logger.log(target);


  var ss2 = SpreadsheetApp.openById(master);
    var sh2 = ss2.getSheetByName("Sheet1");
        sh2.getDataRange().offset(sh2.getLastRow(), 0, target.length, target[0].length).setValues(target);
  } 
 }
}

Note: I have a bunch of different scripts that move rows from one tab to another, but I couldn't find anything where I move it across multiple workbooks.

St3ph St3ph · Accepted Answer · 2016-04-05T14:55:36

What you can do is to have an array with for each manager the sheet where to copy the data.

var managerSheet = {
"manager1":"SheetAzerty",
"manager2":"SheetQwerty"
};

to select the sheet just do :

var manager = "manager1"; //This is the value you retrieve

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(managerSheet[manager]);

Stéphane

Copy rows from one sheet to another

1 Answers