When activating the script manually, the data are registered one below the other, when active via tr-CodePudding

The script below is created in this spreadsheet which I left free for access with the trigger activated every 1 minute so that they can see in real time what is happening:

But when activated via Trigger every 1 minute, it always records on row 67 and overwrites the existing values:

What is happening that manually generates the correct result and for the trigger the result is totally different and wrong?

CodePudding user response：

If you try printing the data of variable values via Trigger, it will print the value of H2:H of Sheet Combinados. The column H of sheet Combinados has 65 rows in it (excluding the header). This is where your script get the row count of 67.

Output:

When you use getActiveSpreadsheet.getRange() without indicating the sheet name in the range, it will automatically get the data of the first sheet in your spreadsheet.

You should change the range to:

var column = SpreadsheetApp.getActiveSpreadsheet().getRange('Soccerway!H2:H');

Here, I updated your code by adding variables that can be reuse, moved variables that are not needed to be looped and group the data so that you will only set values once. This will lessen the run time of your code.

function soccerway() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Soccerway");
  ss.getRange("L2:N").copyTo(ss.getRange('H2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
  if (ss.getRange("A2").getValues()[0][0] ==  "ATIVO") {
    var range;
    range = ss.getRange('F2:F31');
    var loop = range.getValues().flat().filter(e => e);
    var column = ss.getRange('H2:H');
    var values = column.getValues();
    var ct = 0;
    while (values[ct] && values[ct][0] != "") {
      ct  ;
    }
    var valueArr = [];
    if(ct > 0){
      for (var key of loop) {
      var data = [[key,]]
      var contentText = UrlFetchApp.fetch(key).getContentText();
      var $ = Cheerio.load(contentText);
      var ElementSelect = $('#page_match_1_block_match_info_5 > div > div > div.container.left > a.team-title');
      var ElementSelect2 = $('#page_match_1_block_match_info_5 > div > div > div.container.right > a.team-title');
      var data1 = ElementSelect.text().trim()   '&&&&&'   ElementSelect.attr('href');
      var data2 = ElementSelect2.text().trim()   '&&&&&'   ElementSelect2.attr('href');
      valueArr.push([key, data1, data2]);
      }
    }
    ss.getRange(ct 2, 8, valueArr.length, 3).setValues(valueArr);
    
  }
  SpreadsheetApp.getActive().getRange('Soccerway!M2:N').copyTo(SpreadsheetApp.getActive().getRange('Dados Importados!D2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.getActive().getRange('Soccerway!L2:N').copyTo(SpreadsheetApp.getActive().getRange('Soccerway!H2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Reference: