Javascript – Compare two json array and update dataSheet with setValues updating empty rows

I want to update my question so as to better clarify my goal. Cooper’s
script works but doesn’t do exactly what I need. In column B I stored
data row by row so I could quickly check and compare the data with the
API. For this reason the comparison I would like to be made between
column B (json output) and the API, if possible. I also need to put
values in empty cells, I don’t need to replace existing values. As I
say later, I have many columns and I can not recreate the entire
array, also because the values could change in the API and be
different from the values I stored

Working Cooper’s answer

I have a datasheet that contains the ordered data. The sheet consists of thousands of rows identified by the order id.
In the second column I have all the data grouped as json string. The new values are added starting from the last row.

This operation works.

My problem starts when I have to update the changes of some values of ids.

The source where I parse data, both for append and update is a json api.

This is my simulation dataSheet:

Start dataSheet

This is my current procedure:

1) Apply a filter to the json source, selecting the elements where "shipped" is yes.

// My API is for business use, so I can't share URL and token.
// Request and download work perfectly

const loadShipped = () => {return jsonApi().filter(k=>k.shipped == "yes")}

I get this output

var shipped = [
 {"id":"x167","shipped":"yes","countItems":"3","destination":"Lyon","delivery":"22/09/2023"}
,{"id":"x9811","shipped":"yes","countItems":"2","destination":"Madrid","delivery":"27/09/2023"}
]

2) Create a json output of column B and apply a filter selecting the elements where "shipped" is null.

const jsDataSheet = () => {
  const colJS = SpreadsheetApp.getActiveSpreadsheet().getRange("B5:B")
                              .getDisplayValues().filter(col=>col[0]);
  const data = `[${colJS.filter(o=>o!='').join(',')}]`;
  const dataSheetJson = [...new Set(JSON.parse(data))];
  return dataSheetJson.filter(k=>!k.shipped)
}

I get this output

var toUpdate = [
 {"id":"x167","shipped":null,"countItems":"3","destination":"Lyon","delivery":null}
,{"id":"x9811","shipped":null,"countItems":"2","destination":"Madrid","delivery":null}
]

cons: The columns are not adjacent and usually the two json have different length and sort.

This is the script I’m using, is very slow, this is the problem for me, I have too much data to update and many cells to fill. For this situation I have reduced the number of elements.

const sheet = SpreadsheetApp.getActiveSpreadsheet();

    for(var i=0;i<toUpdate.length;i++){
      for(var j=0;j<shipped.length;j++){
        if(toUpdate[i].id == shipped[j].id){

          const time = Date.parse(new Date()) / 1000;
          const ship = shipped[j].shipped;
          const dDel = shipped[j].delivery;

          const newJS = 
          JSON.stringify({...toUpdate[i]
          ,timestamp:time
          ,shipped:ship
          ,delivery:dDel
          });

          sheet.getRange(i + 5,2).setValue(newJS)
          sheet.getRange(i + 5,3).setValue(ship)
          sheet.getRange(i + 5,4).setValue(time)          
          sheet.getRange(i + 5,8).setValue(dDel)
        }
      }
    }

This is final result

Final result

The target is to update data once, update string rows and put the values into empty cells, without changing values already added.

shared google link