Loop or Array Map, which is faster? How would I change from Nested Loops to an Array Map Method in Google Script?

Question

How could I speed the below code up? I believe the Array Map method would speed things up but I don't know how to implement this due to the nested loop. Maybe there is another method I'm unaware of? I am more familiar with VBA and trying to learn Google Script very Sloooooowly!

var document = XmlService.parse(response);
  var root = document.getRootElement();
  var NS = XmlService.getNamespace("urn:ebay:apis:eBLBaseComponents");
   
  var ack = root.getChildText('Ack', NS);
  var time = root.getChildText('Timestamp', NS);
  var version = root.getChildText('Version', NS);
  var build = root.getChildText('Build', NS);
  var shortmessage = root.getChildText('ShortMessage', NS);
  var longmessage = root.getChildText('LongMessage', NS);
 
  var sr = root.getChildren('SaleRecord', NS);    
  
  for (var i = 0; i < sr.length; i++) {
      
    var orderids = sr[i].getChildren('SellingManagerSoldTransaction', NS);
    
    for (var L = 0; L < orderids.length; L++) {      
     
    var srn = orderids[L].getChild('SaleRecordID', NS).getText();

    var sku = orderids[L].getChild('CustomLabel', NS).getText();
      
    var title = orderids[L].getChild('ItemTitle', NS).getText();

    var qty = orderids[L].getChild('QuantitySold', NS).getText();  
        
    var lineitem = orderids[L].getChild('OrderLineItemID', NS).getText();    
   
    var name = sr[i].getChild('ShippingAddress', NS).getChild('Name', NS).getText();
    var pcode = sr[i].getChild('ShippingAddress', NS).getChild('PostalCode', NS).getText();
    var status = sr[i].getChild('OrderStatus', NS).getChild('CheckoutStatus', NS).getText();
    var paidtime = sr[i].getChild('OrderStatus', NS).getChild('PaidTime', NS).getText();
    var shipped = sr[i].getChild('OrderStatus', NS).getChild('ShippedStatus', NS).getText();
    var email = sr[i].getChild('BuyerEmail', NS).getText();
    var price = sr[i].getChild('SalePrice', NS).getValue();
    var totprice = sr[i].getChild('TotalAmount', NS).getValue();
    
    sheet.appendRow([srn, sku, title, qty, price, totprice, lineitem, name, pcode, status, paidtime, shipped, email]);
      
      }
XML Response Example:

It's not really about loops, Major time taken would be due to `sheet.appendRow()`. Read "Best practices" in the documentation. — TheMaster, Oct 08 '20 at 15:25
Where can I find Best Practices for `sheet.appendRow()`? After Googling I found another page on Stack which suggested the use of `range.setValues(array)`. Maybe I should look into this? — Razz, Oct 08 '20 at 15:34
So am I correct that you are steering me towards writing 'all' the data to an array first then in one hit write it to the sheet? — Razz, Oct 08 '20 at 15:45
So @TheMaster I found the `.push` method. I have added and empty array before the loop, replaced the `.appendRow()` with '.push()' then after the loop added `sheet.getRange(sheet.getLastRow() + 1, 1, arr.length, arr[0].length).setValues(arr);` and hay presto it works. But more to the point sooooo much quicker than before. Thanks Guys! — Razz, Oct 09 '20 at 13:37

score 1 · Answer 1 · answered Oct 08 '20 at 10:16

Answer

You should use your current loop. By looking in your code using for loop is a good option.

Since Google Apps Script uses V8 now, this question is equivalent to a ES6 question about for loops. When it comes to performance you should keep in mind a couple things.

You have different ways to iterate an array and you can't decide what's the "best" option without keeping in mind what are you going to do with it.
You are just appending a new row in your sheet so neither map nor forEach are faster than a for loop here.

Brief explanation

There are many options to iterate an array: forEach, map, for, for...of, filter, etc

If you want to create a new array with new values given an existing array you should use map function instead, because map allocates memory and stores the return value in the "callback" function. This function creates and returns an array, that's why you should not use it in this specific case unless you wanted to return an array containing some values.

By using forEach loop the performance is affected by what happens inside each iteration. This function has a callback function to mutate the current array, that's why it is usually a little bit slow depending on the array size.

Otherwise by using for the performance is just affected by how time getChildren(), getChild() and sheet.appendRow() take.

Loop or Array Map, which is faster? How would I change from Nested Loops to an Array Map Method in Google Script?

1 Answers1

Answer

Brief explanation