SQL-like запросы к Google Spreadsheets
Наряду с традиционными для электронных таблиц функциями (повторяющими Excel), в Google Spreadsheets есть некоторое количество дополнительных. Одна из них, под названием "=QUERY()", позволяет рассматривать любую часть таблицы как базу данных и осуществлять к ней простые запросы на языке, напоминающим SQL.
Особенно полезно, что возвращаемые данные доступны не только внутри таблиц, но и по запросу через внешний URL (т.е. таблицу можно использовать как готовый DB сервер для сайта или мобильного приложения.
Итак, допустим, у нас есть простая табличка:
Ниже, в свободной ячейке, напишем, к примеру, так:
=QUERY(A1:C9,"select A,count(B) where B < date '2012-12-05' group by A order by count(B)")
Первый параметр у QUERY определяет что считать базой данных, второй - собственно запрос. Есть ещё необязательный третий - можно указать, сколько строчек в исходных данных считать заголовками столбцов (по умолчанию - догадывается сам).
Соответственно, получим посчитанное и отсортированное количестве собак, лягушек и котов зарегистрированных до 5 декабря 2012 года.
Обратите внимание, что над данными появилось слово "count". Это - название поля. Оно добавляется в качестве заголовка для колонок автоматически. Можно сделать чтобы это выглядело более цивилизованно, добавив в запрос 'label':
=QUERY(A1:C9,"select A,count(B) where B < date '2012-12-05' group by A order by count(B) label A 'Beasts', count(B) 'Quantity' ")
Получится почти красиво:
Поскольку язык запросов примитивный, alias'ы не предусмотрены. Именно поэтому в order by и в label не слишком красиво указана строчка "count(B)" - здесь это всего лишь строчка.
Все доступные функции перечислены в документации, но сразу скажу что, конечно, JOIN-ов здесь нет. Кроме того, в одном запросе можно обращаться только к одному листу (необязательно текущему).
И ещё один важный момент - если в качестве базы данных указаны не просто ячейки, а результат работы некоей другой функции, то внутри запроса к столбцам нужно обращаться уже не по их именам (A,B,C,...) а как Col1,Col2,Col3,...
К примеру:
=QUERY(FILTER(A1:C9;C1:C9=0);"select Col1,Col2,Col3 where Col2 < date '2012-12-05' ")
Здесь мы сначала при помощи FILTER выбираем только строчки в которых последний столбец равен 0, а уже к тому что осталось, применяем select, ограничивающий еще и по дате. Получаем:
Теперь пару слов о том, как можно всем этим воспользоваться с пользой для своего сайта/приложения.
Как-то я уже писал о доступе к данным Google Spreadsheets. Здесь всё очень похоже, просто ещё указывается сам SQL запрос. URL выглядит примерно так (если у вас Google Apps аккаунт):
https://docs.google.com/a/domain.ru/spreadsheet/tq?tq=_sql_query_&key=_key_
Здесь _sql_query_ - urlencoded запрос. Т.е. select A будет выглядеть как select%20A
key - идентификатор таблицы, который можно получить сделав её публичной (File / Publish to the Web и далее внимательно посмотреть в появившийся URL)
В ответ на запрос select A,B,C к исходной таблице мы получим обыкновенный jsonp вида:
google.visualization.Query.setResponse(
{"version":"0.6",
"status":"ok",
"sig":"497643005",
"table":{"cols":[{"id":"A","label":"","type":"string","pattern":""},{"id":"B","label":"","type":"date","pattern":"dd/MM/yyyy"},{"id":"C","label":"","type":"number","pattern":"#0.###############"}],
"rows":[
{"c":[{"v":"cat"},{"v":new Date(2012,10,27),"f":"27/11/2012"},{"v":1.0,"f":"1"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,10,28),"f":"28/11/2012"},{"v":0.0,"f":"0"}]},
{"c":[{"v":"dog"},{"v":new Date(2012,10,29),"f":"29/11/2012"},{"v":0.0,"f":"0"}]},
{"c":[{"v":"frog"},{"v":new Date(2012,10,30),"f":"30/11/2012"},{"v":0.0,"f":"0"}]},
{"c":[{"v":"frog"},{"v":new Date(2012,11,1),"f":"01/12/2012"},{"v":1.0,"f":"1"}]},
{"c":[{"v":"frog"},{"v":new Date(2012,11,2),"f":"02/12/2012"},{"v":0.0,"f":"0"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,11,3),"f":"03/12/2012"},{"v":1.0,"f":"1"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,11,4),"f":"04/12/2012"},{"v":0.0,"f":"0"}]},
{"c":[{"v":"cat"},{"v":new Date(2012,11,5),"f":"05/12/2012"},{"v":0.0,"f":"0"}]}
]}}
);
Проблема, которая может возникнуть - данные отдаются только в jsonp. Т.е. их нельзя получить просто в json - параметр alt=json здесь не работает (в отличие от случая, когда просто получаем данные из таблицы). Довольно странно.
Другой неочевидный момент - некоторый функционал в запросах не работает изнутри таблиц (только снаружи). К примеру - format и option.
Конечно, здесь показан наиболее простой и быстрый способ сделать запрос. Можно и иначе - см. google.visualization.Query