Google Sheets: Move a row of data to another sheet based on cell value

Question

I am trying to move a row of data to another sheet based on cell value. I use this code that i found after researching on the Internet.

/**
 * 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");
    }
  }
}

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. How can i edit that code to also works if i want to move that data row to a different spreadsheet?

I need some advice! Thanks!!

UPDATE 1

I created that trigger code:

function myFunction() {
  
}
function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

So i run that with granded permissions

and then the second

/**
 * 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("1S8KcrvzjvRxrABXa7A3W7QcLsCyag7DAHnWXgBjiaMc");
  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 = 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("moved");
    }
  }
}

with the changes from Carlos M.

I run that and grand permissions again.

Am i saying it right?

UPDATE 2

    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("1S8KcrvzjvRxrABXa7A3W7QcLsCyag7DAHnWXgBjiaMc");
  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 = 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("moved");
    }
  }
}

I saved it and run it but it dont work.What am i doing wrong?

UPDATE 3

That is the original spreadsheet

. I want to transfer that line 3 when it says ok in the F colum.

That is the spreadsheet i want that data of the row to go to.

That is the script i wrote in the script editor in the original spreadsheet.

That is the log result when i click at run

It doesnt show any error but the row is not getting transfered in the other spreadsheet. Do i have some mistake in the code?

CMB · Accepted Answer · 2021-03-02T16:54:21.427

2

Solution:

You need an installable trigger to authorize edit access to two spreadsheets. You can check the documentation to create this trigger that runs on edit.

Sample:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

Run this function manually to create the trigger.

Use SpreadsheetApp.openById() to open your target spreadsheet. Then you can define ranges similarly to your source spreadsheet.

Sample:

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("TARGET_SS_ID_HERE");
  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 (targetss.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("moved");
    }
  }
}

Replace TARGET_SS_ID_HERE with the ID from this link: https://docs.google.com/spreadsheets/d/<ss_ID>/edit#gid=0

Sample Output:

After setting 'ok' to two rows in column F:

edited Mar 02 '21 at 16:54

answered Mar 01 '21 at 14:52

CMB

4,950
1
4
16

Thank you so much for your answer! I will try to do that . – wolfbooy Mar 01 '21 at 17:02
So i don t need the code i post in the question? – wolfbooy Mar 01 '21 at 17:03
1

Please replace the function name with ````myFunction```` and replace the if block with the sample code I posted. Let me know if it works. – CMB Mar 01 '21 at 17:10
I made the code but it is asking for permissions from my google account and it says that it is not safe . Why is not safe? – wolfbooy Mar 01 '21 at 17:55
1

Possibly it's only a browser message, but you would need to apply edit permissions on the spreadsheets to your google account for the script to work. – CMB Mar 01 '21 at 17:58
I updated the code with your help, i will post the update to my initial question to see if i did it the right way! – wolfbooy Mar 01 '21 at 17:59
1

I edited my answer to show the complete code. – CMB Mar 01 '21 at 18:09
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229359/discussion-between-vatn-co-and-carlos-m). – wolfbooy Mar 01 '21 at 18:36
Sorry for bothering you I run the code and it didn t work i post it in my initial question as update 2 Can you please help me? What i am doing wrong? – wolfbooy Mar 01 '21 at 18:42
Please post the error message you are getting. – CMB Mar 01 '21 at 19:41
i don t get any eeror message. It says that Execution Completed. But the problem is that the row is not getting transfered to the other spreadsheet i want – wolfbooy Mar 02 '21 at 09:31
I posted all the screenshots of the sheets and the code in my initial question as UPDATE 3 – wolfbooy Mar 02 '21 at 09:45
I see that the trigger has been created successfully. Did you try changing the value in cell F3 to 'ok' and see if the row has been moved? If not can you check the My Executions panel in ````script.google.com```` and see if there are failed executions of ````myFunction````? – CMB Mar 02 '21 at 14:36
yes i changed it to ok and nothing happened – wolfbooy Mar 02 '21 at 14:46
Please check the My Executions panel in script.google.com and see if there are failed executions of myFunction, you can update the question with the screenshot of the failure. – CMB Mar 02 '21 at 14:49
There is no failures i checked – wolfbooy Mar 02 '21 at 16:15
1

Apologies, I have missed this line, it should have been ````if (targetss.getSheetByName(targetSheet)) { ````. I edited my answer, please try now if this works. – CMB Mar 02 '21 at 16:53
its okay! I will try it now! – wolfbooy Mar 02 '21 at 18:27
1

it worked!! thank you so much for all your help!! – wolfbooy Mar 02 '21 at 18:35

Google Sheets: Move a row of data to another sheet based on cell value

1 Answers1

Solution:

Sample Output: