Как мне продолжить время выполнения apps script или оптимизировать?

Question

[Dollarnuk]

Код:

//----------------------------------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 1

[Григорий Боев]

По большому счёту тут оптимизация может быть по размеру кода - заменить циклом повторяющиеся участки кода.
И не надо выводить каждую строку при расчёте. Формируйте 2D массив (массив массивов) и в самом конце его выводите.

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

Ограничение по времени можно обойти, поместив функцию в диалоговое окно.
Ещё возможно сократить перебор param, если они идут по порядку. Если текущий пустой - то следующие не смотреть.