How to protect the positions to place the data in the worksheet when some values are not retrieved?

Question

I'm using the CheerioGS library:

ID → 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0
Project → https://github.com/tani/cheeriogs

My full code (I added it in its entirety so that they can use it in tests):

function importdataweb() {
    const sheet = SpreadsheetApp.getActive().getSheetByName('Page_One');
    sheet.getRange('A1:Z' + sheet.getMaxRows()).clear({contentsOnly: true, skipFilteredRows: true});
    const url = 'http://www.futebolnatv.com.br/jogos-hoje/';
    const contentText = UrlFetchApp.fetch(url).getContentText();
    const $ = Cheerio.load(contentText);
  
    let elements = $('tbody > tr > th > h4 > b')
    elements.each((index, value) => {
      sheet.getRange(index+1, 1).setValue($(value).text().trim())
    })
    
    let elements_1 = $('tbody > tr > td > div:nth-child(1)')
    elements_1.each((index, value) => {
      sheet.getRange(index+1, 2).setValue($(value).text().trim())
    })
    
    let elements_2 = $('tbody > tr > td > div:nth-child(2)')
    elements_2.each((index, value) => {
      sheet.getRange(index+1, 3).setValue($(value).contents().last().text().trim())
    })
    
    let elements_3 = $('tbody > tr > td > div:nth-child(3)')
    elements_3.each((index, value) => {
      sheet.getRange(index+1, 4).setValue($(value).contents().last().text().trim())
    })
    
    let elements_4 = $('tbody > tr > td b:nth-child(2)')
    elements_4.each((index, value) => {
      sheet.getRange(index+1, 5).setValue($(value).text().trim())
    })
}

The result is:

Some values in the last column for some reason are not collected, so the position of some of these values in the worksheet is in the wrong place.

For example: If the first value is not collected but the second is, the second will be in row 1 of the column, then it would be wrong.

How could I add a dash - in the lines that the value is not collected?

Answer 1

Instead of using Array.prototype.forEach and Range.setValue for filling cell by cell one column at a time, build an Array of Array of values, then add all the values at once by using Range.setValues.

In order to do this, instead of grabbing one cell (td) from all tables (tbody) at once, grab the whole table (tbody) and check if the required cell / content exists , if not add an empty string ('') to the corresponding position in the Array that is being build.

Resources

• https://developers.google.com/apps-script/guides/support/best-practices?hl=en

Answer 2

You want to iterate those tr's. This will give a 2d array (untested)

$('tr').get().map(tr => $(tr).find("h4,.col-md-12").get().map(el => $(el).text()))