Google Sheets – Automated Way to Copy Comments to Cell

Copy of a Comment by Alexander Taylor Oct 27 '15 at 22:34 :

i notice that it only happens when cells contain newlines and possibly other special characters. adding the quotes might be done by the program you paste into -- each program may have the ability to convert tabular data into non tabular data as it wishes. if i paste into Google Keep, i see no quotes, but if i paste into my text editor, i get quotes. perhaps you can try first pasting into Google Keep, then copy again, and paste into destination program.

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

1. ss = active spreadsheet, sheet = active sheet
2. values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
3. row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
4. values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}