Google Sheet: How to define the "cols.label" value in JSON export?

Question

I use a google sheet as an endpoint to import data into an html table. Depending on the type of data in the sheet I get a different JSON structure.

To explain the problem, I built a Google sheet with two very simple tables. The only difference between the two tables is the Age field which is numeric in Sheet1 and textual in Sheet2.

Link document

If I use sheets as JSON endpont I get this behavior: Sheet1 exports the header correctly, but Sheet2 shows an empty header (column headers become normal row values).

Sheet1 JSON

{
    "version": "0.6",
    "reqId": "0",
    "status": "ok",
    "sig": "325167901",
    "table": {
        "cols": [
            {
                "id": "A",
                "label": "Name",
                "type": "string"
            },
            {
                "id": "B",
                "label": "Age",
                "type": "number",
                "pattern": "General"
            }
        ],
        "rows": [
            {
                "c": [
                    {
                        "v": "Vittorio"
                    },
                    {
                        "v": 52.0,
                        "f": "52"
                    }
                ]
            }
        ],
        "parsedNumHeaders": 1
    }
}

Sheet2 JSON

{
    "version": "0.6",
    "reqId": "0",
    "status": "ok",
    "sig": "1566616543",
    "table": {
        "cols": [
            {
                "id": "A",
                "label": "",
                "type": "string"
            },
            {
                "id": "B",
                "label": "",
                "type": "string"
            }
        ],
        "rows": [
            {
                "c": [
                    {
                        "v": "Name"
                    },
                    {
                        "v": "Age"
                    }
                ]
            },
            {
                "c": [
                    {
                        "v": "Vittorio"
                    },
                    {
                        "v": "52"
                    }
                ]
            }
        ],
        "parsedNumHeaders": 0
    }
}

Is there any way to fix Sheet2 behavior?

Answer 1

It seems there is a problem, meanwhile what I suggest is to test the JSON endpont labels as follows

const jsonString = `/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1566616543","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"}],"rows":[{"c":[{"v":"Name"},{"v":"Age"}]},{"c":[{"v":"Vittorio"},{"v":"52"}]}],"parsedNumHeaders":0}});`

function jsonWithLabels(jsonString) {
  var json = JSON.parse(jsonString.slice(47,-2))
  var labels = json.table.cols.map(c => c.label)
  return (labels.join('') != '')
}

function test(){
  console.log (jsonWithLabels(jsonString))
}

if false, you need to fetch the labels from row 1

const jsonString = `/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1566616543","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"}],"rows":[{"c":[{"v":"Name"},{"v":"Age"}]},{"c":[{"v":"Vittorio"},{"v":"52"}]}],"parsedNumHeaders":0}});`

  var json = JSON.parse(jsonString.slice(47,-2))
  var labels = json.table.cols.map(c => c.label)
  console.log (labels.join('') != '')

here is a complete script to rebuild the table with and without labels

const jsonString = `/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1566616543","table":{"cols":[{"id":"A","label":"","type":"string"},{"id":"B","label":"","type":"string"}],"rows":[{"c":[{"v":"Name"},{"v":"Age"}]},{"c":[{"v":"Vittorio"},{"v":"52"}]}],"parsedNumHeaders":0}});`
document.getElementById("json").innerHTML = myItems(jsonString.slice(47, -2))

  function myItems(jsonString) {
    var json = JSON.parse(jsonString);
    var table = '<table>';
    var flag = jsonWithLabels(json);
    if (flag) {
      table += '<tr>';
      json.table.cols.forEach(colonne => table += '<th>' + colonne.label + '</th>')
      table += '</tr>';
    }
    json.table.rows.forEach((row, index) => {
      table += '<tr>';
      row.c.forEach(cel => {
        try { var valeur = cel.f ? cel.f : cel.v }
        catch (e) { var valeur = '' }
        if (!flag && index == 0) { table += '<th>' + valeur + '</th>' }
        else { table += '<td>' + valeur + '</td>' }
      })
      table += '</tr>';
    })
    table += '</table>';
    return table
  }
  function jsonWithLabels(json) {
    var labels = json.table.cols.map(c => c.label);
    return (labels.join('') != '')
  }

table {border-collapse: collapse;}
th,td {border: 1px solid black;}

<div id="json">json here</div>