CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-forms
AZagent
AZagent

Reputation: 13

Script editor is not applying to Google Form Response Sheet

I am using the script below to move rows to other sheets upon edit of a cell. I need to also apply this script to work on the google form response sheet. I created a new column on the form response sheet and when marked "Yes" I need the row to move to another sheet within the workbook. Are google form response sheets different than a normal sheet? The script works on all other sheets.

function onEdit() {
var sheetNameToWatch = "New Listings";
var columnNumberToWatch = 52;
var valueToWatch = "Yes";
var sheetNameToMoveTheRowTo = "New Listings Completed";

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.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(range.getRow(), 1, 1,sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(range.getRow());
}
}

Upvotes: 1

Views: 905

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

If you modify the code to say copyTo instead of moveTo it gives the expected behavior. 

sheet.getRange(range.getRow(), 1, 1,sheet.getLastColumn()).copyTo(targetRange);

Modified code:

function onEdit() {
var sheetNameToWatch = "New Listings";
var columnNumberToWatch = 52;
var valueToWatch = "Yes";
var sheetNameToMoveTheRowTo = "New Listings Completed";

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.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(range.getRow(), 1, 1,sheet.getLastColumn()).copyTo(targetRange);
sheet.deleteRow(range.getRow());
}
}

The reason for this, as you guessed, mostly likely the response sheet is a unique sheet. Also, if you look at your execution transcript it will give an error like this: 

[17-04-04 21:51:56:645 CDT] Execution failed: Cannot cut from form data. Use copy instead. (line 123, file "SOhelp2") [0.405 seconds total runtime]

Hope that helps!

Upvotes: 0

Related Questions