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

@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!