CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jab Burgess
Jab Burgess

Reputation: 27

Copy cells to second sheet based on cell value using a script

We use google sheets for our invoice system. Once we pull the order, we fill in the invoice and anything that we do not have we backorder. We type BO in column I. In excel, we used a button that we could click to copy those cells to our 2nd sheet. We need cells A & B to copy to sheet 2 (which is an exact copy of sheet 1) if column I says BO. Here's what I have so far. This almost works... It deletes everything above the rows with data though and copies the data even if column I doesn't have BO.

I need it to just copy Column A & B if column I says BO to sheet 2 (which is a duplicated of sheet 1) I'm sure there's a simple way, but I can't seem to figure it out.

function Backorder() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Carolina Fireworks Order Form");
  var columnToSearch = 9; // I column 

  Logger.log(sheet.getLastRow());
  var range =sheet.getRange(1,columnToSearch,sheet.getLastRow(),columnToSearch);
  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Back Order");
  var values = range.getValues();

  //Destination  
  var numberOfColumns = 12;
  var numberOfRows = 223 ;
  var startColumn = 1;
  var startRow = 12;
                         // equivalent to destination.getRange('A1:L223');     
  var destinationRange= destination.getRange(startRow, startColumn,numberOfRows,numberOfColumns); 
  var count = '0';
  for(var i=0;i<values.length;i++){
    Logger.log("i is now: " +i);
    Logger.log("Current row value: " +values[i][0]) 
    if(values[i][0] == 'BO'){
      Logger.log("*** Value found in column: " +values[i][0] +"**** \n Count: " +i);
      count++;
      var rangeToCopy = sheet.getRange(i, 1,numberOfRows,numberOfColumns);
      rangeToCopy.copyTo(destinationRange);
    }
  }
}

Upvotes: 1

Views: 348

Answers (1)

SputnikDrunk2
SputnikDrunk2

Reputation: 4038

SOLUTION

**Updated **

The updated script below will loop through the sourceSheet on every row and it specifically checks if every row on column I says "BO".

If there's a match on that specific row of column I, the row # from the loop will be added to rangeToCopy variable (e.g. range "A(row #):B(row #)" in A1 notation). Then, the rangeToCopy will be copied to the destination in the same exact range of rangeToCopy, given that the destination is the same exact copy of sourceSheet.

function backOrder(){
  var sheet = SpreadsheetApp.getActive();
  var sourceSheet = sheet.getSheetByName("Carolina Fireworks Order Form");
  var destination = sheet.getSheetByName("Back Order");
  var lastRow = sourceSheet.getDataRange().getLastRow();
  //LOOP THROUGH SOURCE SHEET
  for(var row=1; row<=lastRow; row++){
    //CHECK FOR "BO" ON EVERY ROWS OF COL I ON SOURCESHEET
    if(sourceSheet.getRange(row,9).getValue() == "BO"){
      Logger.log("CELL I"+row+" has \"BO\""+"\n=================\n"+"RANGE TO COPY:\n"+ "\"A"+row+":B"+row+"\"");
      var rangeToCopy = "A"+row+":B"+row;
      sourceSheet.getRange(rangeToCopy).copyTo(destination.getRange(rangeToCopy));
      sourceSheet.getRange(row,9).setValue("- BO").setHorizontalAlignment("right");
    }
  }
}

RESULT

Sample filled out Carolina Fireworks Order Form sheet:

enter image description here

The Back Order sheet after running the script:

enter image description here

Execution logs result for review:

enter image description here

Upvotes: 1

Related Questions