There was a question that came up in the Google Apps Script G+ community about moving a row of data to another sheet. The person was reusing some code posted by Victor Yee back in 2012 which hooks into the onEdit event in Google Sheets. The idea is a Google Form is used to collect data into a Google Sheet. Someone then looks at the data entered and decides if it should be actioned. If yes then the data is moved to an appropriate sheet within the spreadsheet. The route of the problem appeared to be not only has Google Apps Script changed a lot since then but so has Google Sheets and Forms. In particular it looks like new Sheets “Cannot cut from form data. Use copy instead.”:
To use ‘copy’ instead in Victor’s code you would replace moveTo
with:
s.getRange(rowIndex, 1, 1, colNumber).copyTo(target);
and add the line afterwards of
s.deleteRow(rowIndex);
which will delete the row just changed. I’m not sure why moveTo
doesn’t work. Perhaps there is conflict between the onSubmit and onEdit events. Looking through Victor’s code I was surprised he didn’t use the onEdit fields available. For example:
Field | Example | Notes |
e.source |
Spreadsheet |
A Spreadsheet object, representing the Google Sheets file to which the script is bound |
e.range |
Range |
A Range object, representing the cell or range of cells that were edited |
e.value |
10 |
Only available if the edited range is a single cell |
.. so I’ve reworked into:
/** * Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 4. */ function onEdit(e) { // see Sheet event objects docs // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events var ss = e.source; var s = ss.getActiveSheet(); var r = e.range; // to let you modify where the action and move columns are in the form responses sheet var actionCol = 6; var nameCol = 4; // Get the row and column of the active cell. var rowIndex = r.getRowIndex(); var colIndex = r.getColumnIndex(); // Get the number of columns in the active sheet. // -1 to drop our action/status column var colNumber = s.getLastColumn()-1; // if our action/status col is changed to ok do stuff if (e.value == "ok" && colIndex == actionCol) { // get our target sheet name - in this example we are using the priority column var targetSheet = s.getRange(rowIndex, nameCol).getValue(); // if the sheet exists do more stuff if (ss.getSheetByName(targetSheet)) { // set our target sheet and target range var targetSheet = ss.getSheetByName(targetSheet); var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber); // get our source range/row var sourceRange = s.getRange(rowIndex, 1, 1, colNumber); // new sheets says: 'Cannot cut from form data. Use copy instead.' sourceRange.copyTo(targetRange); // ..but we can still delete the row after s.deleteRow(rowIndex); // or you might want to keep but note move e.g. r.setValue("moved"); } } }
which you can also get by making a copy of this sheet (Update: remember to open Tool > Script editor and then click Resource > Current project triggers to add the onEdit event to the function). See also Michael’s comment about using var s = e.range.getSheet();