move data from sheet to sheet google sheets

Question

My sheet is getting data from form responses. I have multiple tabs for the different request types. Currently when the form is submitted, it comes to the main tab and I manually change the column "status" Column 7 to "Pending Assignment" and using the below script to it sends to the corresponding tab. Is there a way to automate this? Or as the request are coming in move to tabs based on what is in column 4 (request type)?

  function myEdit(e){

  var ss = e.source;
  var s = ss.getActiveSheet();
  var r = e.range;

  var actionCol = 7;
  var nameCol = 4;

  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();

  var colNumber = s.getLastColumn();

  if ((e.value == "Pending Assignment" && colIndex == actionCol)) {
   var targetSheet = s.getRange(rowIndex, nameCol).getValue();
     if (ss.getSheetByName(targetSheet)) { 
      var targetSheet = ss.getSheetByName(targetSheet);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);

  var sourceRange = s.getRange(rowIndex, 1, 1, colNumber); 
  sourceRange.copyTo(targetRange);
  s.deleteRow(rowIndex);
}  
}
}

Akshin Jalilov Akshin Jalilov · Accepted Answer · 2015-07-28T07:00:56

instead of onEdit, you can use onFormResponse trigger.

function onFormResponse() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formsheet = ss.getSheetByName("Form Responses 1");
  var lastrow = formsheet.getLastRow();

  var actionCol = 7;
  var nameCol = 4;
  var colNumber = formsheet.getLastColumn();

  formsheet.getRange(lastrow, actionCol).setValue("Pending Assignment");

  var targetSheet = s.getRange(lastrow, nameCol).getValue();

  if (ss.getSheetByName(targetSheet)) { 
    var targetSheet = ss.getSheetByName(targetSheet);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);

    var sourceRange = formsheet.getRange(lastrow, 1, 1, colNumber); 
    sourceRange.copyTo(targetRange);
    formsheet.deleteRow(lastrow);
  }
}

move data from sheet to sheet google sheets

1 Answers