Google Apps Script – Move Row of Data to Different Sheet Based on Cell Entry

google-appsgoogle-apps-script

I'm currently trying to adjust a script I found that copies the contents of a row from one tab of a google sheet, pastes it into the next descending empty row in a different tab and then deletes the original row. 

function onEdit() {

  var sheetNameToWatch = "Original_Sheet";

  var columnNumberToWatch = 3;
  var valueToWatch = "ValueToLookFor";
  var sheetNameToMoveTheRowTo = "Target_Sheet";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();


  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

What I want to do from here is be able to run the same script but with the target sheet to be a sheet located on a completely different Google sheet instead of from tab to tab within the same Google sheet.

I have next to zero coding knowledge so unsure how I would edit it from here.

Best Answer

You have first get the ID of the target sheet from its url (the xxxx corresponds to the sheet ID: https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=2116300997

to copy it for the following script:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var liness =  SpreadsheetApp.openById("your sheet id goes here");
//
//
var sheet = liness.getSheetByName("Fab")
var linevalues = sheet.getRange("A:W").getValues();
var lastrow=sheet.getLastRow();

Then you can open the new sheet and paste the values into any tab you want just like pasting in active sheet by using setValues().

It will not copy formats and validation rules.

Then you have set values to blank by setValues() or clearContent() in the source sheet.

Related Topic