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++; 
      }
  }; 
}; 

@palumbo what you have here is awesome! I'm new to scripts but I try to understand every steps, thanks to your YT video. Anyway, I'm having an issue since my search key is not the first column. On my application, i have 30 columns overall and the search key is at column 16 and total rows I have is 12k+. I followed your explanation what to put in the getrange areas but it started to paste in from Column 16 and it discarded columns 1-15. Would you be able to assist with it?

@KamenRider1989 make sure you're giving the entire range to the script when you use getRange

getRange can take 4 arguments - (starting_row, starting_col, last_row, last_col), so if you have 30 columns make sure your using the right row and column numbers when defining your range.

For example: sheet.getRange(1, 1, lastRow, 30)

The above code will grab the range starting at row 1, column 1, then go down to the last row (this variable has to be defined) and over to column 30.

Let me know if that helps.

@palumbo It worked! here's the modified script I had. It pastes the data correctly in the destination file but I have another problem. The thing is it just copy all data and it still includes other statuses other than the "Active" we specified.

function moveRows() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const activeSheet = SpreadsheetApp.openById('IDlink').getSheetByName('Sheet1');
const activeLastRow = activeSheet.getLastRow();

let lastRow = sheet.getLastRow();

let sortRange = sheet.getSheetValues(15,1,lastRow, 40);

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

let activeCounter = 1;

for (var i = 1; i <= sortRange.length; i++) {
let name = sheet.getRange(i,16).getValue(); //The column where to look for
console.log(i + " - " + name);

  if (name == "Active") {
    let rowValues = sheet.getRange(i, 1, lastRow, 40).getValues();
    activeSheet.getRange(activeLastRow+activeCounter, 1, lastRow, 40).setValues(rowValues); 
    activeCounter++; 

  }

}
}

@KamenRider1989 looks like your problem is with this line:

let rowValues = sheet.getRange(i, 1, lastRow, 40).getValues();

More specifically the problem is including lastRow in the range declaration.

If you only want to copy only the row where name == "Active" then change that range declaration to this:

let rowValues = sheet.getRange(i, 1, 1, 40).getValues();

The difference is the 3rd value in the getRange, by changing lastRow -> 1 it should only copy the one row.

Try that and let me know how it works.

@palumbo Thanks so much for the assistance. I tried changing the 3rd value to just 1 but I have this error:
"Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 12704.
moveRows @ moveRows.gs:24"

@palumbo I tried minimizing the number of columns and rows to transfer and it actually worked. Also, with 12k rows and 40 columns, the script reaches its maximum run time. When it hits around 7K rows already with just 4 columns, it exceeds the maximum time. So I guess there's really no problem with your formula, it's on the size of the raw data that's giving the script a hard time. :)

@KamenRider1989 That's definitely a problem with Apps Script since it has to run through Google's infrastructure. The way I've done this in the past is to output the rows being evaluated into Logger.log so I can see the last row worked when the script finishes.

So, for example, the last row worked might be 1200

Before starting the next run I adjust the data range to start with row 1201 to avoid any duplication of work.

@palumbo What I'm thinking is to use 2 functions. 1 for the first have and 1 for 2nd half. Do you think that's possible? Like on the first half, instead of using lastRow, i'll specify the end range. then on the 2nd function, i'll specify the start row to the next row range of the ending range of the first half.

like for the first half,
let sortRange = sheet.getSheetValues(15,1,6000, 40);

then on the 2nd half, instead of starting at row 15, i'll have this:
let sortRange = sheet.getSheetValues(6001,1,lastRow, 40);

@KamenRider1989 I think that's a good idea. I'd put them in a single function so they run consecutively like this:

function myFunction(){

firstHalfFunction(); 
secondHalfFunction(); 

}

@palumbo Thanks for the input. I'd do this one. Thank you for all the assistance! :)

Hi @palumbo Thank you for the script and your video on Youtube very helpful. In my case in order to get the last data based on Yesterday (Day -1). Could you help me how to set a criteria that only copy based on certain date and paste to the last row in destination sheet?

@daulatliberty To do this, I would first get the date using the following code:

var date = new Date();

// add a day
date.setDate(date.getDate() - 1);

After that, I would change line 32 of my code to something like

if (date == date_from_spreadsheet) {
let rowValues = sheet.getRange(i, 1, 1, 4).getValues();
josephSheet.getRange(josephLastRow+josephCounter, 1, 1, 4).setValues(rowValues); 
josephCounter++; 
}

See if you can get that to work.

@palumbo
is there a way to if a row is highlighted in a set colour to copy the row into a new sheen and put it in the next available row
i.e if i highlighted rows 2, 6, 9, 27 it would copy them into the other sheet in rows 2,3,4,5 and if i highlight another that would be coppied into row 5

this is what i got so far

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

    var range = e.range;
    var sheet = range.getSheet();
    
    // Check if the edited range is in the correct sheet
    if (sheet.getName() !== "Your Sheet Name") return;

    var backgroundColor = range.getBackground();
    var targetSheetName = "RAS Vanguard Selected Applicants";
    var greenColor = "#49ad45"; // Update with your desired green color

    // Check if the edited range has the green background color
    if (backgroundColor !== greenColor) return;

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

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

    // Copy the row to target sheet
    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);
  }
}

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?

Here is a quick and short scrip that copies the value of a cell to another sheet if the cell has a background color of red.

The source sheet is simply called Sheet1 and the target sheet is called Target

function myFunction() {
  let ss = SpreadsheetApp.getActiveSpreadsheet(); 
  let sheet = ss.getActiveSheet(); 
  let cell = sheet.getRange('A1'); 
  let color = cell.getBackground(); 
  Logger.log(color); 

  if (color == '#ff0000') {
    let value = cell.getValue(); 
    ss.getSheetByName('Target').getRange('A1').setValue(value); 
  }
}

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!