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.
CodePudding user response:
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)