mardi 3 décembre 2019

Using For Loop for Inventory Update

I am trying to make a script to update my inventory based on a parts list on another sheet. I want it to check each row of one column on one sheet, check to see if it matches any of the cells in another column on another sheet (Like a lookup table would.) If that statement is true, I want it to subtract the number on my parts list from the number in inventory.

What I have now will check if the part # on the parts list matches the part # of the same row on the inventory list. That does work. What won't work is getting it to check the next row for that part number if there isn't a match.

I think an if/else statement should do it, but I don't know how to phrase it. I've used file iterators to search in folders, and the concept seems similar, but I'm not sure how to make it iterate over rows.

There may also be a simpler/cleaner way to set this up, but I'm fairly new to Google Scripts and I was designing this off of a similar task I've done before.

var ss = SpreadsheetApp.getActive().getId()
var bomtactics = Sheets.Spreadsheets.Values.get(ss, 'BOM!A5:M2004')
var invtactics = Sheets.Spreadsheets.Values.get(ss, 'Inv Temp!A2:Q1658') 
var invpartno = Sheets.Spreadsheets.Values.get(ss,'Inv Temp!A2:A1658');
var invpartqty = Sheets.Spreadsheets.Values.get(ss,'Inv Temp!M2:M1658');

 for(var i = 0; i < bomtactics.values.length; i++){    
     var BOMquantity = bomtactics.values[i][0];
     var BOMpartnum = bomtactics.values[i][1];
     var invpartnum = invtactics.values[i][0];
     var invquantity = invtactics.values[i][12];

     var newqty = invquantity - BOMquantity
     Logger.log("New Quantity: ", newqty)

     //This works if the Bill of Materials list is an exact match in placement with the inventory list
     if(BOMpartnum == invpartnum){
       SpreadsheetApp.openById(ss).getRange('Inv Temp!M'+(i+2)).setValue(newqty).setFontColor('Red');
     SpreadsheetApp.flush();
   }
 else
 {
   // How to get it to check multiple records like a lookup table.
 }

Aucun commentaire:

Enregistrer un commentaire