CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apps
user1664305
user1664305

Reputation: 179

Filter and Copy to sheet

I am using a Google Sheet in which I have a list in Column A of people that correlate to sheet with their name. In Column B I have data that should be associated with them to copy to their sheet.

I have created a sample here to show how it should look. I also have attached code that technically does what I am wanting. The only caveat is I cannot get the Names into Column A while still copying. Also, in order for it to work I have to manually select the entire range rather than having the range be static or dynamic (until the last row of data).

How can I alter the code I have below (also within the sheet) to suit my needs? The code below assumes that the names are always in column b and copies the entire row containing that name rather than just the data next to it.

var ss=SpreadsheetApp.getActiveSpreadsheet();
var master = ss.getSheetByName('Home');
var colWidth = master.getLastColumn();// last used col in masterSheet
var sheets = ss.getSheets();// number of sheets

function onOpen() {
  var menuEntries = [ {name: "Copy selected Rows to sheets", functionName: "copyRowsOnConditionV2"},
                     ];
  ss.addMenu("Copy functions",menuEntries);// custom menu
}

function copyRowsOnConditionV2() {
  var sheetNames = [];// array of existing sheet names
  var sheets = ss.getSheets();// number of sheets
  for(s=0;s<sheets.length;++s){sheetNames.push(sheets[s].getName())};

  var selectedfirstRow = ss.getActiveSelection().getRowIndex();
  var selectedHeigth = ss.getActiveSelection().getHeight()
  var selectedFullRange = master.getRange(selectedfirstRow,1,selectedHeigth,colWidth);
  var data = selectedFullRange.getValues();
  for(n=0;n<data.length;++n){
    if(data[n][1].length<16){

     var dest = ss.getSheetByName(data[n][1].toString().replace(/ /g,''));//find the destination sheet
     Logger.log(data[n][1].toString().replace(/ /g,''))
     var destRange = dest.getRange(dest.getLastRow()+1,1);// define range
     master.getRange(selectedfirstRow+n,1,1,colWidth).copyTo(destRange);// and make copy below last row
     }
  }
}

Any help would be appreciated!

Upvotes: 1

Views: 1234

Answers (1)

Jordan Rhea
Jordan Rhea

Reputation: 1206

You need to change the index that you are using in the for loop. To refer to the first column in your sheet, use index 0.

for(n=0;n<data.length;++n){
    if(data[n][0].length<16){ //index 0 refers to column A

     var dest = ss.getSheetByName(data[n][0].toString().replace(/ /g,''));//find the destination sheet
     Logger.log(data[n][0].toString().replace(/ /g,''))
     var destRange = dest.getRange(dest.getLastRow()+1,1);// define range
     master.getRange(selectedfirstRow+n,1,1,colWidth).copyTo(destRange);// and make copy below last row
     }
  }

Upvotes: 3

Related Questions