Working around error in a looping when there are no values in the column

Question

function soccerway() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Soccerway");
  
  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);
  }
}

The fact is that when there are no values in Column H, the code will throw an error because in line 29 the number of lines in the range needs to be at least 1, but using:

try {

} catch(e) {

}

Would make the code very large and repetitive because I would need to rewrite all the steps again inside to catch(e).

Is there another simpler and faster way that I can use?

My Spreadsheet and Script:
https://docs.google.com/spreadsheets/d/1kXC-aQEViFmL0KC-UG0YEpjmJq69gpSeH3HbbSetI4M/edit?usp=sharing

Additional information:

Whenever I activate the code I want the new data always be published in blank lines, I don't want it to overwrite the non blank cells.

Answer 1

I assume your Spreadsheet every cell in F2:F31 either has a url or it is empty. In cells H2:H you have a value or not, and based on this you want to run a function on all rows where column H contains a value.

The following code outputs the following (from the API)

• Col H = url
• Col I = team 1 (formatted according to your code)
• Col I = team 1 (formatted according to your code)
• Col K = status (based on the current function exectution)

You can simplify your code by:

function getTeams(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getSheetByName("Soccerway");

  // stop the function here if A2 is not ATIVO
  if( ss.getRange("A2").getValue() !=  "ATIVO" ) return

  const sourceRange = ss.getRange("F2:F31")
  const sourceVals = sourceRange.getValues()
  let startRow = 2


  // Run through each existing row / link
  sourceVals.forEach( (row, index) => {

    // Url is in Column A
    const url = row[0]

    // Current row number to output to
    const rowNum = startRow+index

    // Output will be in columns H:K
    const targetRange = ss.getRange( rowNum, 8, 1, 3)

    // Status will be ouput to column L
    const statusCell = ss.getRange(rowNum, 11)

    // skip if no url exists
    if( !url ) {
      statusCell.setValue("URL Missing")
      return 
    }

    // skip if values exist already
    let skip = false
    targetRange.getValues().forEach( row => {
      if( row[0] || row[1] ) skip = true
    })
   
    if( skip ){
      statusCell.setValue("Field skipped because values existed already")
      return
    } 

    // call the api
    const dataFromAPI = fetchDataForURL( url )
    
    // skip if no response from api
    if( !dataFromAPI || !dataFromAPI.url  || !dataFromAPI.data1 || !dataFromAPI.data2 ){
      statusCell.setValue("No Data from API")
      return
    } 
   
    // write response to sheet, after column H (I:J) 
    targetRange.setValues( [[dataFromAPI.url, dataFromAPI.data1, dataFromAPI.data2]] )
  });

  function fetchDataForURL( url ) {
    if( !url ) return

    var contentText = UrlFetchApp.fetch( url ).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');
      
    return {
      url: url,
      data1: data1,
      data2: data2
    }
  }
}
//