Read Google Sheet Exported JSON by column name in Apps Script

Question

I'm exploring the use of JSON exported Google Sheets as database for Apps Script.

The fetched url follows the structure: https://docs.google.com/spreadsheets/d/DOCUMENTID/gviz/tq?tqx=out:json&gid=SHEETID

JSON.json

{
    "reqId": "0",
    "sig": "000",
    "status": "ok",
    "table": {
        "cols": [
            {
                "id": "A",
                "label": "",
                "type": "string"
            },
            {
                "id": "B",
                "label": "",
                "type": "string"
            },
            {
                "id": "C",
                "label": "",
                "type": "string"
            }
        ],
        "parsedNumHeaders": 0,
        "rows": [
            {
                "c": [
                    {
                        "v": "Data 1-1"
                    },
                    {
                        "v": "Data 1-2"
                    },
                    {
                        "v": "Data 1-3"
                    }
                ]
            },
            {
                "c": [
                    {
                        "v": "Data 2-1"
                    },
                    {
                        "v": "Data 2-2"
                    },
                    {
                        "v": "Data 2-3"
                    }
                ]
            },
            {
                "c": [
                    {
                        "v": "Emails"
                    },
                    {
                        "v": "Ids"
                    },
                    {
                        "v": "Names"
                    }
                ]
            }
        ]
    },
    "version": "0.6"
}

In this function I'm getting returned an array of values for the C column: dataArray = [Data 1-3, Data 2-3]

function getRolePermission(databaseUrl) {

  let databaseParsed = JSON.parse(UrlFetchApp.fetch(databaseUrl).getContentText().match(/(?<=.*\().*(?=\);)/s)[0]);
  let tableLength = Object.keys(databaseParsed.table.rows).length;

  let dataArray = [];
  for (let i = 0; i < tableLength; i++) {
    dataArray.push(databaseParsed.table.rows[i].c[2].v)
  }

  return dataArray;

}

It works well, but I don't know how to make this function more generic, so I call it with (url, headerName) arguments to get an array with the values of a column. Something like:

function getRolePermission(databaseUrl, headerName) {
  // CODE ??
  return dataArray;
}

getRolePermission('https://docs.google.com/spreadsheets/d/1lc...', 'Emails')

to get dataArray = [Data 1-3, Data 2-3], so if I change the order of columns I'm still getting the same results.

score 0 · Accepted Answer · answered Sep 14 '22 at 15:25

dataArray.push(databaseParsed.table.rows[i].c[2].v)

You just need to find the index 2 here to make the function more generic. The table.cols should give you the column headers, but it is not doing that because parsedNumHeaders is 0 in your case. To manually set number of headers, use &headers=1. The url should look like:

https://docs.google.com/spreadsheets/d/DOCUMENTID/gviz/tq?tqx=out:json&gid=SHEETID&headers=1

The response then would look like:

{
  "reqId": "0",
  "sig": "000",
  "status": "ok",
  "table": {
    "cols": [
      {
        "id": "A",
        "label": "Names",
        "type": "string"
      },
      {
        "id": "B",
        "label": "Ids",
        "type": "string"
      },
      {
        "id": "C",
        "label": "Emails",
        "type": "string"
      }
    ],
    "parsedNumHeaders": 1,
    "rows": [
      {
        "c": [
          {
            "v": "Data 1-1"
          },
          {
            "v": "Data 1-2"
          },
          {
            "v": "Data 1-3"
          }
        ]
      },
      {
        "c": [
          {
            "v": "Data 2-1"
          },
          {
            "v": "Data 2-2"
          },
          {
            "v": "Data 2-3"
          }
        ]
      }
    ]
  },
  "version": "0.6"
}

Once you get the headers, you can find the index:

const columnNeeded = databaseParsed.table.cols.findIndex(obj => obj.label === headerName/*"Emails"*/);

Then, use it in your function

dataArray.push(databaseParsed.table.rows[i].c[columnNeeded].v)

Read Google Sheet Exported JSON by column name in Apps Script

1 Answers1