What cycles should it take to go through the array in order to be able to add the missing elements?

Question

Google Drive

It was necessary to get Google Spreadsheet data in the frontend. It worked out.
The table looks like this:
https://docs.google.com/spreadsheets/d/1371WG6WudE...

But when you request the json of this table, Google gives you this stuffing:
https://spreadsheets.google.com/feeds/cells/ 1371WG...

OK. If we attach to the event response that comes with an AJAX request and access srcElement.response.feed.entry, then we will get an array with objects. And each object corresponds to a cell in the table.

Here is an example

var request = new XMLHttpRequest();
    request.responseType = "json";
    request.open("GET", "https://spreadsheets.google.com/feeds/cells/1371WG6WudEXfukqMMakOTJSK_P33cOyXOG69LoN8tyg/1/public/full?alt=json");
    request.onload = (req) => {
    console.log(req.srcElement.response.feed.entry)
    }

    request.send();

Now the fun part. If the cell is not empty, then Google sends it as an object.
But, if the cell is empty, it stupidly does nothing with it!!!

For example, the second row in the table:
id : 1
english: WHAT could possibly explain what's going on?
translation: EMPTY
video: 01
video_format: .mp4

But google only has 4 objects for the second line:

What I failed to do...

I need an array with objects like this:

var cards = [
{id: "1", english: "Some English sentence, if there is one", translation: "Перевод, если он есть", video: "01", video_format: ".mp4"}
]

But I don’t know how to go through the elements of what Google sent, and if some cells are missing, then just remove an empty string in the corresponding field in the object.

Before that, I downloaded a Google spreadsheet in .csv format. Then I converted it online to .json, uploaded .json to the hosting and already accessed it. There were no problems.
It turned out something like this:
https://maxyenot90.tmweb.ru/ACCOUNTING/json-test/

But I would really like to edit something in the Google spreadsheet, and immediately the changes appear on the site.

Answer 1

For example like this:

var result = [];
var request = new XMLHttpRequest();
    request.responseType = "json";
    request.open("GET", "https://spreadsheets.google.com/feeds/cells/1371WG6WudEXfukqMMakOTJSK_P33cOyXOG69LoN8tyg/1/public/full?alt=json");
    request.onload = (req) => {
       let gscell =  req.srcElement.response.feed.entry.map( (e) => e.gs$cell);
       let col_row = parseInt(gscell[gscell.length-1].row)+1;
       let i = 1;
       while (i < col_row) {
          let res_row = gscell.filter( e => parseInt(e.row) == i);
          this_row = {};
           this_row.id = ( res_row.filter( e => parseInt(e.col) == 1).length) ?  res_row.filter( e => parseInt(e.col) == 1)[0].inputValue : null;
           this_row.english = ( res_row.filter( e => parseInt(e.col) == 2).length) ? res_row.filter( e => parseInt(e.col) == 2)[0].inputValue : null;
           this_row.translation = ( res_row.filter( e => parseInt(e.col) == 3).length) ? res_row.filter( e => parseInt(e.col) == 3)[0].inputValue : null;
           this_row.video = ( res_row.filter( e => parseInt(e.col) == 4).length) ?  res_row.filter( e => parseInt(e.col) == 4)[0].inputValue : null;
           this_row.video_format = ( res_row.filter( e => parseInt(e.col) == 5).length) ?  res_row.filter( e => parseInt(e.col) == 5)[0].inputValue : null;
           result.push(this_row);
          i++;
       }    
        console.log(result);
    }

request.send();

Answer 2

Interestingly, the wrong question leads to such complicated consequences.
What you are using is the deprecated Google Data API . You also need to use the Google Sheets API.
The code

const getUrl = (id, range, key) =>
  `https://sheets.googleapis.com/v4/spreadsheets/${id}/values/${range}?key=${key}`;
const arrToCollect = (array) =>
  array
    .slice(1)
    .map(
      (_, ri) =>
        array[0].reduce((ah, h, ci) => ((ah[h] = array[ri + 1][ci]), ah), {}),
      []
    );

(async () => {
  const id = '1xTXNtfabGIiFR9PdOQonmnlSPbhGcj_2Geo1v0cq4Gw';
  const range = 'Sheet';
  // Limited to the use of the contributor.pw/* domain
  const key = 'AIzaSyCt4F7Z8cVDqivNcO3slXewThZurJ4gJNY';
  let res = {};
  try {
    res = await fetch(getUrl(id, range, key));
    const data = JSON.parse(await res.text());
    console.log(data.values);
    console.log(arrToCollect(data.values));
  } catch (err) {
    console.error(err);
  }
})();

Comments
getUrl- a function for forming the address of the request to the API.
arrToCollect- function of converting a two-dimensional array into an array of objects. I just copy it, that's why it's so dense. Details https://github.com/contributorpw/google-apps-scrip... Unfortunately, due to blocking, I can't send you a direct link to the site. See (if not too lazy) https://apps-script-snippets.contributor (dot) pw/snippets/common_js/2darray_to_collection/
In this example, you will need to create a project in the Google Cloud Console and generate an API key to restrict access and quantity requests.
Result

Try it live https://github.com/contributorpw/contributorpw/tre.... Unfortunately, due to Mop's bloated CSF, the link will require another link to be clicked.