Google Apps Script that copies rows to a new sheet based on a cell value.

moveRows.gs

function onOpen(e) {
  let ui = SpreadsheetApp.getUi(); 
  ui.createMenu('🤖 Automation Tools')
    .addItem('Move reps to individual sheets', 'moveRows')
    .addToUi(); 
}; 

function moveRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); 
  const sheet = ss.getActiveSheet(); 
  const eddieSheet = SpreadsheetApp.openById('1wvrKeJd2zj3_tT1W0jkXoIZEbGf4_-0SSSesRgtK_Og').getSheetByName('Sheet1'); 
  const eddieLastRow = eddieSheet.getLastRow(); 
  
  const josephSheet = SpreadsheetApp.openById('1U0xTv-0_dyFsF3AwwDzT86cjqySjnJcZkn4d4n0MZ1k').getSheetByName('Sheet1'); 
  const josephLastRow = josephSheet.getLastRow(); 

  let lastRow = sheet.getLastRow(); 

  let sortRange = sheet.getSheetValues(2,1,lastRow, 4); 

  Logger.log(lastRow)
  // Logger.log(typeof(sortRange))
  // Logger.log(sortRange.length); 

  let josephCounter = 1; 
  let eddieCounter = 1; 

  for (var i = 1; i <= sortRange.length; i++) {
    let name = sheet.getRange(i,1).getValue(); 
    console.log(i + " - " + name); 

      if (name == "Joseph Palumbo") {
        let rowValues = sheet.getRange(i, 1, 1, 4).getValues();
        josephSheet.getRange(josephLastRow+josephCounter, 1, 1, 4).setValues(rowValues); 
        josephCounter++; 
      }

      if (name == "Eddie Jauregui") {
        let rowValues = sheet.getRange(i, 1, 1, 4).getValues();
        eddieSheet.getRange(eddieLastRow+eddieCounter, 1, 1, 4).setValues(rowValues); 
        eddieCounter++; 
      }
  }; 
}; 

Hi @CT-34589, yes, you can absolutely copy a row to another sheet based on background color of that row or cell. I looked over your code and it looks more or less correct to get the desired result. Are you getting errors or is something else going wrong?

It just wasn’t triggering on onEdit not sure why

@CT-34589 just rewrote your script (hastily) to see if I could get it to work, and this does copy the the selected range (a single cell) to the target sheet based on if the background color is red.

  try {
    if (!e || !e.range) {
      console.log("No range provided in the event object.");
      return;
    }

    // setting variables 
    let col = e.range.getColumn(); 
    let row = e.range.getRow(); 
    let ss = SpreadsheetApp.getActiveSpreadsheet(); 
    let sheet = ss.getActiveSheet(); 
    let cell = ss.getActiveSheet().getActiveCell().getA1Notation(); 
    let sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetName();  
    let backgroundColor = sheet.getRange(cell).getBackground(); 
    
    // Check if the edited range is in the correct sheet
    if (sheetName !== "Your Sheet Name") return;

    // var backgroundColor = ;
    var targetSheetName = "RAS Vanguard Selected Applicants";
    var greenColor = "#ff0000"; // Update with your desired RED color

    // Check if the edited range has the green background color
    if (backgroundColor !== greenColor) {
      Logger.log("Returned on line 33");  // this is a test for me 
      return;
    } 

    // Copy the row to the target sheet
    var targetSheet = ss.getSheetByName(targetSheetName); 
    if (!targetSheet) {
      targetSheet = ss.insertSheet(targetSheetName);
    }

    // Clear existing contents in target sheet
    targetSheet.clear();

    // Copy the row to target sheet
    let rangeToCopy = sheet.getRange(cell);  
    Browser.msgBox(rangeToCopy.getValue()); 
    rangeToCopy.copyTo(targetSheet.getRange(1,1)); 

    // range.copyTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1), {contentsOnly:true});

    // Optionally: remove the green background color after copying
    // range.setBackground(null);
  } catch (error) {
    console.error("An error occurred:", error);
  }
}

@CT-34589 here's a cleaned up and simplified version of the scrip that does the same thing

function onEdit(e) {

  // setting variables 
  let col = e.range.getColumn(); 
  let row = e.range.getRow(); 
  let ss = SpreadsheetApp.getActiveSpreadsheet(); 
  let sheet = ss.getActiveSheet(); 
  let target = ss.getSheetByName('Target'); 
  let targetLastRow = target.getLastRow(); 
  let cell = ss.getActiveCell().getA1Notation(); 
  let activeCell = sheet.getRange(cell); 
  let orange = "#ff9900"; 
  // Browser.msgBox('It works'); 

  // get background color
  let color = activeCell.getBackground(); 
  
  if (color == "#ff9900") {
    activeCell.copyTo(target.getRange(targetLastRow+1,1)); 
  }
}

@palumbo
i think it may be to do with google scripts and setup as the onEdit trigger isn't causing the script to run, although im not sure why

@CT-34589 that looks correct to me. I usually add a this line to my onEdit() functions

Browser.msgBox('It works');

to ensure the onEdit is working. Have you done something similar to ensure it's working?

@palumbo Thank for this https://gist.github.com/palumbo/e47839bcdb7a484d50a7c6e36c861928?permalink_comment_id=4833915#gistcomment-4833915

I tried and it can run. sorry for my lack of knowledge. Can i ask more, how to using 2 criteria in case want to copy data based on certain date and specific name like current?

Hi @daulatliberty, yes, you can do that and it's relatively easy. All you have to do is to add multiple criteria to your if statements on line 32 and 38.

So, for example, I would write the statement like this: if (name == "Joseph Palumbo" && date=="2/28/2024") using the && to designate that I want the script to check for more than one criteria. The rest of the statement will be exactly the same.

this might not be a thing - I'm super new to trying to code so if it's not possible no worries, but when you define "sortRange" is there a way to notate it so I can skip one column? so for example, transfer columns a-g and i-k but not h?

@boudiccamorgana I don't think you can "grab" two disparate parts of the spreadsheet using a single 'getRange', but you can create two variables for the different parts of the sheet you want to sort or move.

Sorry I am extremely new to this so I just want to clarify: like to run the script twice with two ranges, or to somehow say get(rangex and rangey) so to speak ? Idk if this helps or makes it worse but the new sheets don’t skip that column so the master sheet’s “I” would be the new one’s “h” and move it in a row.

@boudiccamorgana Think of the ranges like lego blocks. You can cut up the first sheet any way you want by defining different ranges that you want to copy, then you can put them wherever you want on the target sheet, combining the disparate ranges into one table.

Okay, so what does that look like in code? I'm trying to figure out how I would separate the blocks on the code - so I can have my ranges with

let sortRange = sheet.getSheetValues(2,1,lastRow, 7);
let otherRange = sheet.getSheetValues(9,1,lastRow, 6);

but I'm stuck on where to put the code to tell it where to paste?

I am totally new to writing scripts, so thanks in advance for your patience. When I try to run this script on my master sheet, I get "TypeError: Cannot read properties of null (reading 'getLastRow')"

Any advice? Thank you!