Использование URL-адреса QUERY для группировки данных перед отправкой на панель инструментов скрипта Google Apps
У меня есть Google Spreadsheet, скажем, с ключом 'ABCKEY', и я хотел бы выполнить функцию QUERY (SQL) для данных, прежде чем использовать данные на панели инструментов скрипта Google Apps.
var ss = SpreadsheetApp.openById('ABCKEY');
var mydata = ss.getDataRange();
В этой статье объясняется, как можно использовать функцию QUERY для данных в электронной таблице для получения сгруппированных данных.
Следующий запрос выдает правильные сгруппированные данные, которые я хотел бы использовать в качестве источника данных на панели инструментов скрипта Google Apps:
https://spreadsheets.google.com/a/mybusinessappdomain.com/tq?key=ABCKEY&tq=select%20A%2CC%2Csum(F)%2Csum(G)%20group%20by%20A%2C%20C
Поэтому я хотел бы, по существу, заполнить mydata Переменная выше с результатом вышеуказанного SQL-запроса, который создает строку вывода JSON.
Как это можно сделать?
2 ответа
Подход, который я предлагаю, заключается в следующем:
- использование
UrlFetchApp.fetch()чтобы получить результаты запроса URI в переменную в вашем скрипте. URI запроса возвращает JavaScript, чтобы установить результаты для службы визуализации Google. - Удалите любое постороннее содержимое в результате, оставив только JSON-представление результатов запроса. Это может сделать простое извлечение регулярных выражений, а затем мы можем проанализировать извлеченную строку в объект JSON.
- Декодировать объект JSON в двумерный массив,
mydata, Это требует некоторого понимания того, как таблица представлена в виде объекта JSON в запросе визуализации.
Результат запроса JSON структурирован так:
{
"version": "0.6",
"status": "ok",
"sig": "862651648",
"table": {
"cols": [
{
"id": "A",
"label": "",
"type": "string",
"pattern": ""
},
{
"id": "D",
"label": "Population Density",
"type": "number",
"pattern": "#0.###############"
}
],
"rows": [
{
"c": [
{
"v": "Indonesia"
},
{
"v": 117,
"f": "117"
}
]
},
{
"c": [
{
"v": "China"
},
{
"v": 137,
"f": "137"
}
]
},
{
"c": [
{
"v": "Nigeria"
},
{
"v": 142,
"f": "142"
}
]
},
{
"c": [
{
"v": "Pakistan"
},
{
"v": 198,
"f": "198"
}
]
},
{
"c": [
{
"v": "India"
},
{
"v": 336,
"f": "336"
}
]
},
{
"c": [
{
"v": "Japan"
},
{
"v": 339,
"f": "339"
}
]
},
{
"c": [
{
"v": "Bangladesh"
},
{
"v": 1045,
"f": "1045"
}
]
}
]
}
}
Вы заметите, что table объект состоит из массива cols объекты, которые описывают столбцы в таблице. Для вашего массива интересующая вас часть label для столбца.
После этого table Объект содержит массив rows объекты, каждый с массивом c объекты с данными для каждого столбца в строке. Для вашего массива это v или значение, которое вас интересует. (f содержит форматированную версию тех же данных)
Таким образом, наш анализатор будет сначала перебирать заголовки столбцов, а затем каждую строку таблицы, pushинтересующие значения в двумерный массив, mydata,
В этом примере я обращаюсь к общедоступной электронной таблице, использованной в образце интерактивного кода, приведенном в справочнике по языку запросов, а также использую их пример запроса. Я написал пример, чтобы его можно было легко изменить, чтобы получить доступ к вашей собственной таблице с помощью собственного запроса. Вот код:
// Specify the spreadsheet key and the query to be retrieved
var ssKey = 'pCQbetd-CptGXxxQIG7VFIQ';
var query = encodeURIComponent('SELECT A,D WHERE D > 100 ORDER BY D');
// Build url to peform query
var url = 'http://spreadsheets.google.com/tq?key=%KEY%&tq=%QUERY%'
.replace('%KEY%',ssKey)
.replace('%QUERY%',query);
// Use UrlFetchApp to get the results of the query as a string.
var response = UrlFetchApp.fetch(url);
var content = response.getContentText();
//Logger.log(content);
// Extract the JSON object from the response. Note that the response contains
// multiple lines of Javascript, and that it's the second line that has our
// data table in it.
var regex = /.*google.visualization.Query.setResponse\((.*)\)/g
var jsonContent = regex.exec(content)[1];
Logger.log(jsonContent);
var objectContent = Utilities.jsonParse(jsonContent);
var numCols = objectContent.table.cols.length;
var numRows = objectContent.table.rows.length;
// Decode objectContent into a two-dimensional array.
var mydata = []
// Start with the header row.
mydata[0] = [];
for (var col = 0; col < numCols; col++ ) {
mydata[0].push(objectContent.table.cols[col].label);
}
// Then data rows
for (var row = 0; row < numRows; row++) {
mydata[row+1] = [];
for (var col = 0; col < numCols; col++ ) {
mydata[row+1].push(objectContent.table.rows[row].c[col].v);
}
}
// Done - mydata is now a two-dimensional array with the results of the query
debugger; // If debugging, pause to examine results
В зависимости от того, для чего вы планируете использовать данные на своей панели, вы можете просто использовать table объект после звонка jsonParse(),
Я не думаю, что парсер JSON var objectContent = Utilities.jsonParse(jsonContent) будет работать с ответом на запрос визуализации, имеющим дату в качестве одного из значений в ячейке, например в приведенном ниже примере
{
"v": newDate(2013,
11,
31),
"f": "31-Dec-2013",
"p": {
"style": "color: rgb(0, 0, 0);font-family:Dialog;"
}
}