When I click "Add Item," I want the code to run and display the information in the table below the form.
function AddItem() {
//DEFINE ALL ACTIVE SHEETS`
var ss = SpreadsheetApp.getActiveSpreadsheet();
//DEFINE MENU SHEET `
var poSheet = ss.getSheetByName("POF");
var itemSheet = ss.getSheetByName("Products");
`//GET NEXT ROW OF PO SHEET`
var lastrowPO = poSheet.getLastRow() + 1;
`//GET LAST ROW OF ITEM SHEET`
var lastrowItem = itemSheet.getLastRow();
`// GET VALUE OF PART AND QUANTITY`
var part = poSheet.getRange('B15').getValue();
var quantity = poSheet.getRange('B17').getValue();
` // GET UNIT PRICE FROM ITEM SHEET`
for (var i = 2; i <= lastrowItem; i++) {
if (part == itemSheet.getRange(i, 3).getValue()) {
var part = itemSheet.getRange(i, 3).getValue();
var unitCost = itemSheet.getRange(i, 5).getValue();
var prodcode = itemSheet.getRange(i, 2).getValue();
} else if (part == itemSheet.getRange(i, 2).getValue()) {
var part = itemSheet.getRange(i, 3).getValue();
var unitCost = itemSheet.getRange(i, 5).getValue();
var prodcode = itemSheet.getRange(i, 2).getValue();
}
};
// POPULATE PO SHEET
poSheet.getRange(lastrowPO, 1).setValue(prodcode);
poSheet.getRange(lastrowPO, 2).setValue(part);
poSheet.getRange(lastrowPO, 3).setValue(quantity);
poSheet.getRange(lastrowPO, 4).setValue(unitCost).setNumberFormat("#,###.00");
};
Description
I'm not able to test this but basically what I did was change your getValue/setValue to getValues/setValues. This typically improves performance dramatically.
Some parts of your logic I didn't quite understand but tried to duplicate your logic as is. For example, you change prodcode to part in the else block. And rather than continue looping once I've found a match I break out of the loop.
Using the items array row 1 of the spreadsheet is the first index 0 of the array. Likewise column 1 of the spreadsheet is the second index 0 of the array.
Code.gs
function AddItem() {
//DEFINE ALL ACTIVE SHEETS`
var ss = SpreadsheetApp.getActiveSpreadsheet();
//DEFINE MENU SHEET `
var poSheet = ss.getSheetByName("POF");
var itemSheet = ss.getSheetByName("Products");
//GET NEXT ROW OF PO SHEET`
var lastrowPO = poSheet.getLastRow() + 1;
//GET LAST ROW OF ITEM SHEET`
var lastrowItem = itemSheet.getLastRow();
// GET VALUE OF PART AND QUANTITY`
var part = poSheet.getRange('B15').getValue();
var quantity = poSheet.getRange('B17').getValue();
// Use getValues to get all data
var items = itemSheet.getDataRange().getValues();
// GET UNIT PRICE FROM ITEM SHEET
// Here I'm assuming you want from row 2 to the last row
// And I'm assuming you want the first occurance of part
for( var i = 1; i < items.length; i++ ) {
if( part == items[i][2] ) {
var unitCost = items[i][4];
var prodcode = items[i][1];
break;
}
else if( part == items[i][1] ) {
var prodcode = part;
part = items[i][2];
var unitCost = items[i][4];
break;
}
};
// POPULATE PO SHEET
// To use setValues it must pass a 2D array of 1 row
poSheet.getRange(lastrowPO,1,1,4).setValues([[prodcode,part,quantity,unitCost]]);
poSheet.getRange(lastrowPO, 4).setNumberFormat("#,###.00");
};
Reference