CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
TylerD
TylerD

Reputation: 3

I am trying to filter data from 1 spreadsheet into two separate spreadsheets through Apps Script using an for loop and if function

I am fairly new to appscript and only did a little coding in college. Here is my issue:

I am trying to filter data from one sheet into two separate existing sheets. The existing sheets already have data and formulas in it. Also, the filtered data needs to update onEdit and filtered the data into the first row that does not contain text. Another caveat is that I still need to be able to manually enter data into the cells as well from time to time and I need the function to realize this and not overwrite the manually entered data.

We are using QR codes to import data for inventory and the imported data needs to be filtered. But, we need to be able to manually input new line items into these sheets still for inventory that is non related to existing QR codes.

The function will run and filter the data correctly for the most part, but the row spacing is all over the place and the first cell, typically throws a #REF error and then continues on with the function.

At this point, I'm pretty stuck.

(I know that it will need to become an onEdit(e) function later, but just need the code to work first) Here is my function:

function myfunction(){
  var ss = SpreadsheetApp;
  var orderlog = ss.getActiveSpreadsheet().getSheetByName("Order Log - COMM");
  var scanit = ss.getActiveSpreadsheet().getSheetByName("Scan-It Data");
  var mfg = ss.getActiveSpreadsheet().getSheetByName("Order Log - MFG");
  var scanlastRow = scanit.getLastRow();
  var commlastrow = orderlog.getLastRow();
  

 
// For loop to filter Vendor
  for(var i=1;i <= scanlastRow;i++){
  
  // Grab the Value from the Scan-It Data Sheet 
    var workingcell = scanit.getRange(1 + i, 2 );
    var val = workingcell.getValue();

    // If the Value equals IMMCO, send it to the MFG Sheet
    // If the Value is anything else, send it to the COMM Sheet

    if (val == "IMMCO") {

      // Grab the Scan-It Data and send it to the MFG sheet
      var scanitrange = scanit.getRange(1 + i, 1 );
      //var mfglastrow = mfg.getLastRow();
      //mfg.insertRowAfter(mfglastrow);
      var mfgrow = mfg.getRange(71 + i, 1 );
      scanitrange.copyTo(mfgrow);

      } else {
      
      //Grab the Scan-It Data and send it to the last row of the COMM Sheet
      var scanitrange = scanit.getRange(1 + i, 1);
      var orderlogrow = orderlog.getRange(328 + i, 1 );
      //orderlog.insertRowAfter(commlastrow);
      //var commrange = orderlog.getRange("A327:A"+(commlastrow+1));
      scanitrange.copyTo(orderlogrow); 
      }
    }
  
}

Upvotes: 0

Views: 40

Answers (1)

doubleunary
doubleunary

Reputation: 19220

I would recommend that you use a tried and tested solution such as the moveRowsFromSpreadsheetToSpreadsheet_ script. The code is almost 800 lines long so it is impractical to quote here, but it is quite simple to configure using the parameters section.

Upvotes: 0

Related Questions