How can I use arrays to make this code faster?-CodePudding

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");

};

I use this code to match text from sheet1 and grab a row of data from sheet2 matching that text and bring back data to sheet 1.
It is taking a lot of time to run.
I want to make it run faster. Can someone please guide me .
I am an amateur coder.

CodePudding user response：

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