Script to copy data from one sheet to another based on criteria

Question

I have a google spreadsheet called the master list. It contains two sheets 'Response' and 'Master Response'. The response sheet has around 3000 rows of data and the first column in the 'response' sheet is called ' responded?'. I want to copy the row from 'response' sheet to ' master response' sheet if the 'responded?' column has the value 'called' in it and every time a new entry is made in the 'response' sheet the appropriate row of data should be copied to the 'master response' sheet. I am currently using the following code but the rows get moved from the 'Response' sheet and not copied. I wanted a script where the rows are copied to the 'Master response' sheet and should also stay on the ' response' sheet. Thank you in advance.

function onEdit() 
{
var sheetNameToWatch = "Response";
var columnNumberToWatch = 1;
var valueToWatch = "Called";
var sheetNameToMoveTheRowTo = "Master Response";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getActiveCell();
if (sheet.getName() == sheetNameToWatch && range.getColumn() == 
columnNumberToWatch && range.getValue() == valueToWatch)
{
var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
sheet.getRange(2, 1, sheet.getLastRow(), 
sheet.getLastColumn()).moveTo(targetRange);
}
}

Ritz Ritz · Accepted Answer · 2017-11-17T22:34:43

You need to use copyTo instead of moveTo to copy the data from one sheet to another.

sheet.getRange(2, 1, sheet.getLastRow(),sheet.getLastColumn()).copyTo(targetRange);

To Copy it to different Spreadsheet.

var ss = SpreadsheetApp.openById("<<SPREADSHEET ID>>").getSheetByName("Sheet1");
var newTargetRange = ss.getRange(ss.getLastRow() + 1, 1); 
sheet.getRange(2, 1, sheet.getLastRow(),sheet.getLastColumn()).copyTo(newTargetRange);

Script to copy data from one sheet to another based on criteria

1 Answers