Google-sheets – How to move a row into another sheet

Use a custom function.

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data

result

Example

I've created an example file for you: multi-line cells into new rows.

• Add the script under Tools > Script editor.
• Save the script, the name doesn't matter.
• Use the custom function in a cell just like a built in (see the screenshot above).

Here is a script that does the following: if a user enters 'DONE' in any cell in sheet named 'Sheet1', the row with that cell is copied over to Sheet2, and deleted from Sheet1. To use, just enter in Tools > Script Editor, and change sheet names if needed.

The script does not check that 'DONE' is in the last nonempty cell of a row: it can be anywhere.

function onEdit(e) { 
  if (e.value === 'DONE') {
    var sheet = e.range.getSheet();
    if (sheet.getSheetName() == 'Sheet1') {    // Name of the sheet with projects
      logSheet = e.source.getSheetByName('Sheet2');   // Name of the archive sheet 
      var row = e.range.getRow(); 
      var lastRow = logSheet.getLastRow();
      var range = sheet.getRange(row, 1, 1, sheet.getLastColumn()); 
      range.copyTo(logSheet.getRange(lastRow + 1, 1));
      sheet.deleteRow(row);
    }
  }
}

Depending on the contents of your spreadsheet, you may want to replace the copying command with

range.copyTo(logSheet.getRange(lastRow + 1, 1), {contentsOnly: true});

so that only static values are copied, not formulas. This makes sense if the row contains formulas that would stop working properly if moved to another sheet.