Copying entire row to a sheet based on cell Value

Question

I have a spreadsheet that I want to copy the entire row on Sheet1 based on the cell value in column F. I have a the main request sheet labeled "Master Requests" that anyone can access and then I have a dropdown in Column F (starting at row 3) that has different selections "Marketing", "Advertising", "Office Work", etc. The entry data goes from Column A through Column P. Based on the dropdown value in Column F I want the entire row to be copied to the sheet with the same name, i.e., if the value in F4 is "Marketing" then I want to copy the whole row (not delete it) and move it to the sheet named "Marketing" on the same spreadsheet. And I don't know why I'm having so much trouble!

function copyRowsToSpaySheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Master Requests");
  var targetSheet = ss.getSheetByName("Marketing");
  
  var data = sourceSheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][6] == "Marketing") {
      targetSheet.appendRow(data[i]);
    }
  }
}

I was expecting the above to work, but it just leaves the sheet blank.

And I tried this as well

function moveRows(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Master Requests');
  const marketSheet = ss.getSheetByName('Marketing');
  const marketLastRow = marketSheet.getLastRow();
  let lastRow = sheet.getLastRow();
  let sortRange = sheet.getSheetValues(4,1,lastRow,16);
  Logger.log(lastRow)
  //Logger.log(typeof(sortRange))
  //Logger.log(sortRange.length);
  let marketcounter = 1;
  for (var i = 1; i <= sortRange.length; i++){
    let name = sheet.getRange(i,6).getValue();
    console.log(i + " - " + name);
    if (name == "Marketing") {
      let rowValues = sheet.getRange(i, 1, 1, 16)
      marketSheet.getRange(marketLastRow+spaycounter, 1, 1, 16).setValues(rowValues);
      marketcounter++;
    }
    };
  };

Which also leaves my sheet named "Marketing" empty.

Answer 1

ALTERNATIVE SOLUTION

Note: @Codedabbler is correct that changing data[i][6] to data[i][5] in the first snippet resolves the issue. This is to provide some other options to execute the script aside from running it in the Google Apps Script Editor.

The full code corrected would be:

function copyRowsToSpaySheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Master Requests");
  var targetSheet = ss.getSheetByName("Marketing");
  var advertising = ss.getSheetByName("Advertising");
  var officeWork = ss.getSheetByName("Office Work");
  var data = sourceSheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    if (data[i][5] == "Marketing") {
      targetSheet.appendRow(data[i]);
    } else if (data[i][5] == "Advertising") {
      advertising.appendRow(data[i]);
    } else if (data[i][5] == "Office Work") {
      officeWork.appendRow(data[i]);
    }
  }
}

This includes the Advertising and Office Work sheets from the description.

Here are some options to run the code without going to Google Apps Script:

OPTION 1

Use onOpen(e) in creating a custom menu for the script.

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Custom Menu')
    .addItem('Copy Rows', 'copyRowsToSpaySheet')
    .addToUi();
}

function copyRowsToSpaySheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Master Requests");
  var marketing = ss.getSheetByName("Marketing");
  var advertising = ss.getSheetByName("Advertising");
  var officeWork = ss.getSheetByName("Office Work");
  var data = sourceSheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    var targetSheet = data[i][5] == "Marketing" ? marketing : data[i][5] == "Advertising" ? advertising : data[i][5] == "Office Work" ? officeWork : null;
    targetSheet ? targetSheet.appendRow(data[i]) : null;
  }
}

This creates something like this:

OPTION 2

Use onEdit(e) to automatically run the script.

function onEdit(e) {
  var range = e.range;
  var source = e.source;
  var row = range.getRow();
  var col = range.getColumn();
  var value = range.getValue();
  var sheet1 = source.getSheetByName("Master Requests");
  var marketing = source.getSheetByName("Marketing");
  var advertising = source.getSheetByName("Advertising");
  var officeWork = source.getSheetByName("Office Work");
  var sheetValues = sheet1.getRange(row, 1, 1, sheet1.getLastColumn()).getValues();

  var targetSheet = col == 6 && value == "Marketing" ? marketing : col == 6 && value == "Advertising" ? advertising : col == 6 && value == "Office Work" ? officeWork : null;
  targetSheet ? targetSheet.appendRow(sheetValues[0]) : null;
}

This runs the code whenever an edit is made to column F with the values Marketing, Advertising, and Office Work, copying the row to its respective sheet.