D
D
Dollarnuk2021-04-29 20:18:37
Google Apps Script
Dollarnuk, 2021-04-29 20:18:37

How do I continue apps script execution time or optimize?

The code:

//----------------------------------royaltoys---------------------------------------------------------------------

function getRoyalToysAll(){
  var url = 'https://royaltoys.com.ua/my/export/82f668de-8f05-478e-86f3-34224286999c.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var entries = root.getChildren('shop');
  var long = entries[0];
  var offers = long.getChild('offers');
  var offer = offers.getChildren('offer'); 
  var longiteration =offer.length;
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("royaltoys");
  var lr = sheet.getLastRow();
  var lc = sheet.getLastColumn();
  //sheet.getRange(2, 1, lr, lc).clear();
  for (var i = 0; i <= longiteration; i++) {
    var id = offer[i].getAttribute('id').getValue();
    var available = offer[i].getAttribute('available').getValue();
    var price = offer[i].getChild('price').getValue();
    //var stock_quantity = offer[i].getChild('stock_quantity').getValue();
    var description = offer[i].getChild('description').getValue();
    var brend = offer[i].getChild('brend').getValue();
    var currencyId = offer[i].getChild('currencyId').getValue();
    var categoryId = offer[i].getChild('categoryId').getValue();
    for (var j = 0 ; j <= 0; j++){
        if (offer[i].getChildren('picture')[j] == '[Element: <picture/>]'){var picture1 = offer[i].getChildren('picture')[j].getValue()} else {var picture1 = ''}
        if (offer[i].getChildren('picture')[j+1] == '[Element: <picture/>]'){var picture2 = offer[i].getChildren('picture')[j+1].getValue()} else {var picture2 = ''}
        if (offer[i].getChildren('picture')[j+2]== '[Element: <picture/>]'){var picture3 = offer[i].getChildren('picture')[j+2].getValue()} else {var picture3 = ''}
        if (offer[i].getChildren('picture')[j+3]== '[Element: <picture/>]'){var picture4 = offer[i].getChildren('picture')[j+3].getValue()} else {var picture4 = ''}
        if (offer[i].getChildren('picture')[j+4]== '[Element: <picture/>]'){var picture5 = offer[i].getChildren('picture')[j+4].getValue()} else {var picture5 = ''}
        if (offer[i].getChildren('picture')[j+5]== '[Element: <picture/>]'){var picture6 = offer[i].getChildren('picture')[j+5].getValue()} else {var picture6 = ''}
      }
    var name = offer[i].getChild('name').getValue();
    var vendorCode = offer[i].getChild('vendorCode').getValue();

    for (var u = 0 ; u <= 0; u++){
        if (offer[i].getChildren('param')[u]== '[Element: <param/>]'){var param1 = offer[i].getChildren('param')[u].getAttribute('name').getValue(); 
        var paramVal1 = offer[i].getChildren('param')[u].getValue();} 
        else {var param1 = '' ; var paramVal1 = ''}

        if (offer[i].getChildren('param')[u+1]== '[Element: <param/>]'){var param2 = offer[i].getChildren('param')[u+1].getAttribute('name').getValue(); 
        var paramVal2 = offer[i].getChildren('param')[u+1].getValue();} 
        else {var param2 = '' ; var paramVal2 = ''}

        if (offer[i].getChildren('param')[u+2]== '[Element: <param/>]'){var param3 = offer[i].getChildren('param')[u+2].getAttribute('name').getValue(); 
        var paramVal3 = offer[i].getChildren('param')[u+2].getValue();} 
        else {var param3 = '' ; var paramVal3 = ''}

        if (offer[i].getChildren('param')[u+3]== '[Element: <param/>]'){var param4 = offer[i].getChildren('param')[u+3].getAttribute('name').getValue(); 
        var paramVal4 = offer[i].getChildren('param')[u+3].getValue();} 
        else {var param4 = '' ; var paramVal4 = ''}

         if (offer[i].getChildren('param')[u+4]== '[Element: <param/>]'){var param5 = offer[i].getChildren('param')[u+4].getAttribute('name').getValue(); 
        var paramVal5 = offer[i].getChildren('param')[u+4].getValue();} 
        else {var param5 = '' ; var paramVal5 = ''}

        if (offer[i].getChildren('param')[u+5]== '[Element: <param/>]'){var param6 = offer[i].getChildren('param')[u+5].getAttribute('name').getValue(); 
        var paramVal6 = offer[i].getChildren('param')[u+5].getValue();} 
        else {var param6 = '' ; var paramVal6 = ''}

        if (offer[i].getChildren('param')[u+6]== '[Element: <param/>]'){var param7 = offer[i].getChildren('param')[u+6].getAttribute('name').getValue(); 
        var paramVal7 = offer[i].getChildren('param')[u+6].getValue();} 
        else {var param7 = '' ; var paramVal7 = ''}

        if (offer[i].getChildren('param')[u+7]== '[Element: <param/>]'){var param8 = offer[i].getChildren('param')[u+7].getAttribute('name').getValue(); 
        var paramVal8 = offer[i].getChildren('param')[u+7].getValue();} 
        else {var param8 = '' ; var paramVal8 = ''}

        if (offer[i].getChildren('param')[u+8]== '[Element: <param/>]'){var param9 = offer[i].getChildren('param')[u+8].getAttribute('name').getValue(); 
        var paramVal9 = offer[i].getChildren('param')[u+8].getValue();} 
        else {var param9 = '' ; var paramVal9 = ''}

        if (offer[i].getChildren('param')[u+9]== '[Element: <param/>]'){var param10 = offer[i].getChildren('param')[u+9].getAttribute('name').getValue(); 
        var paramVal10 = offer[i].getChildren('param')[u+9].getValue();} 
        else {var param10 = '' ; var paramVal10 = ''}

        if (offer[i].getChildren('param')[u+10]== '[Element: <param/>]'){var param11 = offer[i].getChildren('param')[u+10].getAttribute('name').getValue(); 
        var paramVal11 = offer[i].getChildren('param')[u+10].getValue();} 
        else {var param11 = '' ; var paramVal11 = ''}

        if (offer[i].getChildren('param')[u+11]== '[Element: <param/>]'){var param12 = offer[i].getChildren('param')[u+11].getAttribute('name').getValue(); 
        var paramVal12 = offer[i].getChildren('param')[u+11].getValue();} 
        else {var param12 = '' ; var paramVal12 = ''}

        if (offer[i].getChildren('param')[u+12]== '[Element: <param/>]'){var param13 = offer[i].getChildren('param')[u+12].getAttribute('name').getValue(); 
        var paramVal13 = offer[i].getChildren('param')[u+12].getValue();} 
        else {var param13 = '' ; var paramVal13 = ''}

        if (offer[i].getChildren('param')[u+13]== '[Element: <param/>]'){var param14 = offer[i].getChildren('param')[u+13].getAttribute('name').getValue(); 
        var paramVal14 = offer[i].getChildren('param')[u+13].getValue();} 
        else {var param14 = '' ; var paramVal14 = ''}

        if (offer[i].getChildren('param')[u+14]== '[Element: <param/>]'){var param15 = offer[i].getChildren('param')[u+14].getAttribute('name').getValue(); 
        var paramVal15 = offer[i].getChildren('param')[u+14].getValue();} 
        else {var param15 = '' ; var paramVal15 = ''}
        
        if (offer[i].getChildren('param')[u+15]== '[Element: <param/>]'){var param16 = offer[i].getChildren('param')[u+15].getAttribute('name').getValue(); 
        var paramVal16 = offer[i].getChildren('param')[u+15].getValue();} 
        else {var param16 = '' ; var paramVal16 = ''}

        if (offer[i].getChildren('param')[u+16]== '[Element: <param/>]'){var param17 = offer[i].getChildren('param')[u+16].getAttribute('name').getValue(); 
        var paramVal17 = offer[i].getChildren('param')[u+16].getValue();} 
        else {var param17 = '' ; var paramVal17 = ''}

        if (offer[i].getChildren('param')[u+17]== '[Element: <param/>]'){var param18 = offer[i].getChildren('param')[u+17].getAttribute('name').getValue(); 
        var paramVal18 = offer[i].getChildren('param')[u+17].getValue();} 
        else {var param18 = '' ; var paramVal18 = ''}

        if (offer[i].getChildren('param')[u+18]== '[Element: <param/>]'){var param19 = offer[i].getChildren('param')[u+18].getAttribute('name').getValue(); 
        var paramVal19 = offer[i].getChildren('param')[u+18].getValue();} 
        else {var param19 = '' ; var paramVal19 = ''}

        if (offer[i].getChildren('param')[u+19]== '[Element: <param/>]'){var param20 = offer[i].getChildren('param')[u+19].getAttribute('name').getValue(); 
        var paramVal20 = offer[i].getChildren('param')[u+19].getValue();} 
        else {var param20 = '' ; var paramVal20 = ''}
    }
    var arr = [id,available,price,description,brend,currencyId,categoryId,picture1,picture2,picture3,picture4,picture5,picture6,name,vendorCode,param1,,paramVal1,param2,,paramVal2,param3,,paramVal3,param4,,paramVal4,param5,,paramVal5,param6,,paramVal6,param7,,paramVal7,param8,,paramVal8,param9,,paramVal9,param10,,paramVal10,param11,,paramVal11,param12,,paramVal12,param13,,paramVal13,param14,,paramVal14,param15,,paramVal15,param16,,paramVal16,param17,,paramVal17,param18,,paramVal18,param19,,paramVal19,param20,,paramVal20];
  
    var set = sheet.getRange(i+2, 1, 1,75).setValues([arr]);
  }
};

Answer the question

In order to leave comments, you need to log in

1 answer(s)
G
Grigory Boev, 2021-04-30
@ProgrammerForever

By and large, optimization here can be based on the size of the code - replace repeating sections of code with a loop.
And it is not necessary to display each line when calculating. Form a 2D array (array of arrays) and output it at the very end.

function getRoyalToysAll(url) {
  let xml = UrlFetchApp.fetch(url).getContentText();
  let document = XmlService.parse(xml);
  let root = document.getRootElement();
  let entries = root.getChildren('shop');
  let long = entries[0];
  let offers = long.getChild('offers').getChildren('offer');
  
  let outData = [];
  for (offer of offers) {
    let id = offer.getAttribute('id').getValue();
    let available = offer.getAttribute('available').getValue();
    let price = offer.getChild('price').getValue();
    //let stock_quantity = offer.getChild('stock_quantity').getValue();
    let description = offer.getChild('description').getValue();
    let brend = offer.getChild('brend').getValue();
    let currencyId = offer.getChild('currencyId').getValue();
    let categoryId = offer.getChild('categoryId').getValue();   
    let name = offer.getChild('name').getValue();
    let vendorCode = offer.getChild('vendorCode').getValue();
    let pictures = offer.getChildren('picture').map(el=>el.getValue());
    pictures.length=6;
    
    let params = [];
    for (let pi=1; pi<=20; pi++){
      let param = offer.getChild('param'+pi);
      let data = param?[param.getAttribute('name').getValue(),'',param.getValue()]:['','',''];
      params.push(...data);
    }; 
    let row = [id, available, price, description, brend, currencyId, categoryId, ...pictures, name, vendorCode, ...params];

    outData.push(row);
  }
  return outData;
};

function test_getRoyalToysAll(){
  const url = "https://royaltoys.com.ua/my/export/82f668de-8f05-478e-86f3-34224286999c.xml";    
  const sheetName = "Лист1";
  let outData = getRoyalToysAll(url);
  const outRange = SpreadsheetApp
                    .getActiveSpreadsheet()
                    .getSheetByName(sheetName)
                    .getRange(1,1,outData.length, outData[0].length);
                    
  outRange.setValues(outData);
};

The time limit can be bypassed by placing a function in a dialog box.
It is also possible to shorten the param enumeration if they are in order. If the current one is empty, then do not watch the next ones.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question