CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Alex
Alex

Reputation: 21

Using Google app script to copy rows to different sheet for a specified column value

I am trying to copy rows B and C from "MBOM" sheet to "Inventory" sheet, where column H = "Y".

I'm getting.... TypeError: Cannot read property "length" from undefined. (line 15, file "Code")

function onEdit(e){
  
  var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MBOM");
  var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inventory");
  
  var readinventory = sheetFrom.getDataRange().getValues();
        var target = new Array();// this is a new array to collect data
        for(n=0;n<readinventory.length;++n){ // iterate in the array, row by row
          if (readinventory[n][6]=="Y"){ // if condition is true copy row values to target
            target.push( [readinventory[n][1], readinventory[n][2]] );// copy column B and C (entire row is inventory[n])
           }
        }
        //Paste to another sheet from first cell onwards, using the array length of specifed columns
        sheetTo.getRange(1,1,target.length,target[0].length).setValues(target);
        
  
}

What am I doing wrong? Thanks in advance for all your help!!

Upvotes: 2

Views: 825

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

When using the setValues() method, the values must be in a two dimensional array.

The following code uses an inner array, and populates the inner array whenever the conditions are met. Then the inner array is added to the outer array. The outer array continually gets new inner arrays. The inner array must be reset to a blank array whenever new values are to be added to it.

function onEdit(e){
  
  var arrOfValuesToSet,i,innerArray,L,sheetFrom,sheetTo,sourceSheetTabName,startColumn,
      startRow,targetSheetTabName,valuesFromSourceShTab;
  //Define variables without assigning a value - value will initially be undefined
  
  sourceSheetTabName = "MBOM";//Put the name of the sheet tab for the source
  targetSheetTabName = "Inventory";//Enter the name of the target sheet tab
    
  sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheetTabName);
  sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetTabName);
  
  valuesFromSourceShTab = sheetFrom.getDataRange().getValues();//Get all the values out 
  //of the sheet tab
  arrOfValuesToSet = [];// this is a new array to get the values to be put into the
      //source sheet tab
  innerArray = [];
  
  L = valuesFromSourceShTab.length;
    
  for (i=0;i<L;i++) {//iterate through the source cate in the array, row by row
    if (valuesFromSourceShTab[i][6]=="Y"){ // if condition is true copy
     //row values to target
      innerArray = []; //Reset
      
      innerArray.push( [valuesFromSourceShTab[i][1], valuesFromSourceShTab[i][2]] );
      // copy column B and C (entire row is inventory[n])
      arrOfValuesToSet.push(innerArray);
    }
  }
  
  startRow = 1;
  startColumn = 1;
  
  sheetTo
    .getRange(startRow,startColumn,arrOfValuesToSet.length,arrOfValuesToSet[0].length)
    .setValues(arrOfValuesToSet);
    //Set values in the target sheet tab -

};

Upvotes: 1

Related Questions