title: Google Sheets: Move a row of data to another sheet based on cell value
body: <p>I am trying to <strong>move a row of data to another sheet based on cell value</strong>. I use this code that i found after researching on the Internet.</p>
  <pre><code>/**
   * 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 == &quot;ok&quot; &amp;&amp; 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(&quot;moved&quot;);
      }
    }
  }
  </code></pre>
  <p><strong>This code works succesfully to transfer the row of data on a different tab in the same spreadsheet.
  But it doesnt work if i want to move that data to a diferrent spreadsheet.</strong>
  How can i edit that code to also works if i want to move that data row to a different spreadsheet?</p>
  <p>I need some advice!
  Thanks!!</p>
  <p><strong>UPDATE 1</strong></p>
  <p>I created that trigger code:</p>
  <pre><code>function myFunction() {
    
  }
  function createSpreadsheetOpenTrigger() {
    var ss = SpreadsheetApp.getActive();
    ScriptApp.newTrigger('myFunction')
        .forSpreadsheet(ss)
        .onEdit()
        .create();
  }
  </code></pre>
  <p>So i run that with granded permissions</p>
  <p><strong>and then
  the second</strong></p>
  <pre><code>/**
   * 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
    var targetss = SpreadsheetApp.openById(&quot;1S8KcrvzjvRxrABXa7A3W7QcLsCyag7DAHnWXgBjiaMc&quot;);
    if (e.value == &quot;ok&quot; &amp;&amp; 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 = targetss.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.' 
        var sourceData = sourceRange.getValues();
        targetRange.setValues(sourceData);
        // ..but we can still delete the row after
        s.deleteRow(rowIndex);
        // or you might want to keep but note move e.g. r.setValue(&quot;moved&quot;);
      }
    }
  }
  </code></pre>
  <p>with the changes from Carlos M.</p>
  <p>I run that and grand permissions again.</p>
  <p>Am i saying it right?</p>
  <p><strong>UPDATE 2</strong></p>
  <pre><code>    function createSpreadsheetOpenTrigger() {
    var ss = SpreadsheetApp.getActive();
    ScriptApp.newTrigger('myFunction')
        .forSpreadsheet(ss)
        .onEdit()
        .create();
  }
  
  function myFunction(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
    var targetss = SpreadsheetApp.openById(&quot;1S8KcrvzjvRxrABXa7A3W7QcLsCyag7DAHnWXgBjiaMc&quot;);
    if (e.value == &quot;ok&quot; &amp;&amp; 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 = targetss.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.' 
        var sourceData = sourceRange.getValues();
        targetRange.setValues(sourceData);
        // ..but we can still delete the row after
        s.deleteRow(rowIndex);
        // or you might want to keep but note move e.g. r.setValue(&quot;moved&quot;);
      }
    }
  }
  </code></pre>
  <p><strong>I saved it and run it but it dont work.What am i doing wrong?</strong></p>
  <p><strong>UPDATE 3</strong></p>
  <blockquote>
  <p>That is the original spreadsheet</p>
  </blockquote>
  <p>. I want to transfer that line 3 when it says ok in the F colum.
  <a href="https://i.stack.imgur.com/bxwAU.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bxwAU.jpg" alt="Original Spreadsheet" /></a></p>
  <blockquote>
  <p>That is the spreadsheet i want that data of the row to go to.</p>
  </blockquote>
  <p><a href="https://i.stack.imgur.com/a8kHv.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/a8kHv.jpg" alt="Spreadsheet i want the data to go to, (TAB m1)" /></a></p>
  <blockquote>
  <p>That is the script i wrote in the script editor in the original
  spreadsheet.</p>
  </blockquote>
  <p><a href="https://i.stack.imgur.com/G8ijI.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/G8ijI.jpg" alt="Script" /></a></p>
  <p><a href="https://i.stack.imgur.com/soadQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/soadQ.jpg" alt="Script" /></a></p>
  <blockquote>
  <p>That is the log result when i click at run</p>
  </blockquote>
  <p><a href="https://i.stack.imgur.com/jjA9X.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jjA9X.jpg" alt="Result" /></a></p>
  <p><strong>It doesnt show any error but the row is not getting transfered in the other spreadsheet. Do i have some mistake in the code?</strong></p>