Parse With Firebaseapp For Loop

I don't know if this method is correct but I' trying to learn how to parse data content. I have this list of IDs in column A The IDs start from 3rd row 886370 886371 886369 886322

Solution 1:

Probably you need something like this:

function alfa_test() {

  letspreadsheet= SpreadsheetApp.getActiveSpreadsheet();
  letsheet= spreadsheet.getSheetByName('alfatest');
  letstartRow=3;
  letlastRow= sheet.getLastRow();
  letnumRows= lastRow - startRow + 1;

  // get the range and get 2d array from the rangeletrange= sheet.getRange(startRow,1,lastRow,1);
  letdata= range.getValues().flat();

  // output arrayletoutput= [];

  // iterate trough 2d arrayfor (vari=0; i < data.length; i++) {

    letid= data[i];
    leturl='http://api.scorebing.com/v4/race/view?token=&race_id=' + id;
    
    // new row for output arrayletvalues=newArray(18).fill('');

    // try to get responsetry {
      letresponse= FirebaseApp.getDatabaseByUrl(url);
      letdataRes= response.getData();
      letdataSF= dataRes.race.race_start_first;
      letdataL= dataRes.sp;

    values = [
        dataSF.host_sp,
        dataSF.tie_sp,
        dataSF.guest_sp,
        dataSF.rangfen_host_sp,
        dataSF.rangfen_handicap,
        dataSF.rangfen_guest_sp,
        dataSF.daxiao_up_sp,
        dataSF.daxiao_handicap,
        dataSF.daxiao_low_sp,
        dataL.half_bet[0].host_sp,
        dataL.half_bet[0].tie_sp,
        dataL.half_bet[0].guest_sp,
        // dataL.half_rangfen[0].host_sp,  // <--- empty arrays! try to skip them// dataL.half_rangfen[0].handicap,// dataL.half_rangfen[0].guest_sp,// dataL.half_daxiao[0].up_sp,// dataL.half_daxiao[0].handicap,// dataL.half_daxiao[0].low_sp
    ];

    // skip the row if there is no response  
    } catch(e) {}

    // add the new row to the output array
    output.push(values);

  }

  // fill the cells of the sheet with data from the output array
  sheet.getRange(startRow,3,lastRow,output[0].length).setValues(output);

}

But I haven't tried it. Basically all you need for speed is 2d arrays instead of getValue/setValues for every row and try/catch to skip the wrong responses.

Update

Here is the clumsy but working solutions (I have no Firebase, so I used just fetch JSON):

function alfa_test() {

  letspreadsheet= SpreadsheetApp.getActiveSpreadsheet();
  letsheet= spreadsheet.getSheetByName('alfatest');
  letstartRow=3;
  letlastRow= sheet.getLastRow();
  letnumRows= lastRow - startRow + 1;

  // get the range and get 2d array from the rangeletrange= sheet.getRange(startRow,1,numRows,1);
  letdata= range.getValues().flat();

  // output arrayletoutput= [];

  // iterate trough 2d arrayfor (vari=0; i < data.length; i++) {

    letid= data[i];
    leturl='http://api.scorebing.com/v4/race/view?token=&race_id=' + id;

    letvalues= [];
    
    // let response = FirebaseApp.getDatabaseByUrl(url);letresponse= UrlFetchApp.fetch(url);
    letdataRes= JSON.parse(response.getContentText());
    letdataSF= dataRes.race.race_start_first;
    letdataL= dataRes.sp;

    values[0]  = dataSF.host_sp;
    values[1]  = dataSF.tie_sp;
    values[2]  = dataSF.guest_sp;
    values[3]  = dataSF.rangfen_host_sp;
    values[4]  = dataSF.rangfen_handicap;
    values[5]  = dataSF.rangfen_guest_sp;
    values[6]  = dataSF.daxiao_up_sp;
    values[7]  = dataSF.daxiao_handicap;
    values[8]  = dataSF.daxiao_low_sp;

    values[9]  = dataL.half_bet.length     > 0 ? dataL.half_bet[0].host_sp       : 'x';            
    values[10] = dataL.half_bet.length     > 0 ? dataL.half_bet[0].tie_sp        : 'x';            
    values[11] = dataL.half_bet.length     > 0 ? dataL.half_bet[0].guest_sp      : 'x';

    values[12] = dataL.half_rangfen.length > 0 ? dataL.half_rangfen[0].host_sp   : 'x';                
    values[13] = dataL.half_rangfen.length > 0 ? dataL.half_rangfen[0].handicap  : 'x';                  
    values[14] = dataL.half_rangfen.length > 0 ? dataL.half_rangfen[0].guest_sp  : 'x';

    values[15] = dataL.half_daxiao.length  > 0 ? dataL.half_daxiao[0].up_sp      : 'x';              
    values[16] = dataL.half_daxiao.length  > 0 ? dataL.half_daxiao[0].handicap   : 'x';                
    values[17] = dataL.half_daxiao.length  > 0 ? dataL.half_daxiao[0].low_s      : 'x';              

    output.push(values);

  }

  sheet.getRange(startRow,3,numRows,output[0].length).setValues(output);

}

It gets me this table: