Trying to convert Google Sheets moveTo script to copyTo script

Question

In my small department, we currently have a simplistic ticketing system where a user fills out a google form to request service and then the ticket is added to a google sheet which we keep up on our work PCs and displays in our offices.

I'm attempting to make a script where when a checkbox is clicked, a timestamp is created on a certain column and then row on one sheet is moved over to another sheet where "Completed Tickets" are archived.

I've been able to get the script working completely with the timestamp, and I have been able to get the moving portion working completely with a moveTo function, but ONLY when you manually type in the ticket. If the ticket is submitted using Google forms, it blocks the use of the moveTo command, because it will only allow you to copy and paste formdata, and not cut and paste.

So, what I'm attempting to do is convert my 2nd onEdit script to a copyTo, rather than a moveTo. However, after many attempts at this, I can't seem to get it to function. I've posted the script I have below. Any help would be appreciated.

function onEdit(event) {
  myFunction1(event);
}

function myFunction1(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var timezone = "GMT-7";
  var timestamp_format = "M-d-yyyy HH:MM:SS"; // Timestamp Format. 
  var updateColName = "Timestamp";
  var timeStampColName = "Completed";
  var sheet = event.source.getSheetByName('Form Responses 1'); //Name of the sheet where you want to run this script.
  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;

if(s.getName() == "Form Responses 1" && r.getColumn() == 11 && r.getValue() == true) {
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  } //closes if statement operations; no close to myFunction1()

  function myFunction2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();

if(s.getName() == "Form Responses 1" && r.getColumn() == 11 && r.getValue() == true) {
 var row = r.getRow();
 var numColumns = s.getLastColumn();
 var targetSheet = ss.getSheetByName("Completed Tickets");
 var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
 s.getRange(row, 1, 1, numColumns).moveTo(target);
 s.deleteRow(row);
    } //closes if statement operation
  } //closes myFunction2()
  myFunction2(); //calls myFunction2() so that it will run after myFunction1() code
} // closes myFunction1()

ra89fi ra89fi · Accepted Answer · 2019-03-05T17:42:03

You can copy row from response sheet to target sheet then delete row from response sheet.

if (s.getName() == "Form Responses 1" && r.getColumn() == 11 && r.getValue() == true) {
  var cell = sheet.getRange(index, dateCol + 1);
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
  cell.setValue(date);
  // copy n delete
  var row = r.getRow();
  var numColumns = s.getLastColumn();
  var targetSheet = ss.getSheetByName("Completed Tickets");
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns).setValues(s.getRange(row, 1, 1, numColumns).getValues());
  s.deleteRow(row);
}

Trying to convert Google Sheets moveTo script to copyTo script

1 Answers