事前準備としてAPIを利用できるようにしておきます
Fusion TableのデータをSQLで抽出するコードの例
コード.gsfunction select_data(){ var sql = 'SELECT Text FROM ' + table_id + " WHERE Number = 1"; var result = FusionTables.Query.sqlGet(sql); Logger.log(result); } |
select_data()を実行すると以下のように指定したデータがログに出ます
試してみたこと
LIKEで取得してみる
var sql = 'SELECT Text FROM ' + table_id + " WHERE Number LIKE '%O%'";
NOT LIKEは使えない
CONTAINSを使ってみる
var sql = 'SELECT TEXT FROM ' + practice_table_id + " WHERE TEXT CONTAINS 'テキスト'";
DOES NOT CONTAINSを使ってみる
var sql = 'SELECT TEXT FROM ' + practice_table_id + " WHERE TEXT DOES NOT CONTAINS 'テキスト'";
MATCHESを使ってみる
var sql = 'SELECT TEXT FROM ' + table_id + " WHERE TEXT MATCHES '%テキスト%'";
NOT MATCHESを使ってみる
var sql = 'SELECT TEXT FROM ' + table_id + " WHERE TEXT NOT MATCHES '%テキスト%'";
AND検索
var sql = 'SELECT Text FROM ' + table_id + " WHERE Text LIKE '%テキスト%' AND Text LIKE '%テキスト2%'";
OR検索
サポートされていないようです
Row and Query SQL Referenceに以下のように記載されていました
OR is not supported. (If you are looking for OR, consider the IN operator. It will do the trick in certain queries).
INでやってみる(%は使えない)
var sql = 'SELECT TEXT FROM ' + practice_table_id + " WHERE TEXT IN ('テキスト', 'テキスト2')";
var sql = 'SELECT Text FROM ' + table_id + " WHERE Text = 'テキスト'";
TextとNumberをSELECT
var sql = 'SELECT Text, Number FROM ' + table_id + " WHERE TEXT LIKE '%テキスト%'";
参考
Query: sqlGet
https://developers.google.com/fusiontables/docs/v2/reference/query/sqlGet
Fusion Tables Service
https://developers.google.com/apps-script/advanced/fusion-tables
Fusion Tables API v2 (revision 26)
https://developers.google.com/resources/api-libraries/documentation/fusiontables/v2/java/latest/com/google/api/services/fusiontables/Fusiontables.Query.Sql.html
Filter with "matches" operator
https://support.google.com/fusiontables/answer/171204?hl=en
Row and Query SQL Reference
Query: sqlGet
https://developers.google.com/fusiontables/docs/v2/reference/query/sqlGet
Fusion Tables Service
https://developers.google.com/apps-script/advanced/fusion-tables
Fusion Tables API v2 (revision 26)
https://developers.google.com/resources/api-libraries/documentation/fusiontables/v2/java/latest/com/google/api/services/fusiontables/Fusiontables.Query.Sql.html
Filter with "matches" operator
https://support.google.com/fusiontables/answer/171204?hl=en
Row and Query SQL Reference
Reserved words
AND
ASC
AS
BY
CASE
CIRCLE
CONTAINS
CONTAIN
CREATE
DELETE
DESCRIBE
DESC
DOES
DROP
ENDS
EQUAL
FROM
GROUP
IGNORING
IN
INSERT
INTO
JOIN
LATLNG
LEFT
LIKE
LIMIT
MATCHES
NEAR
NOT
OFFSET
ORDER
OUTER
POLYGON
RECTANGLE
ROWID
SELECT
SET
SHOW
SKIP
ST_DISTANCE
ST_INTERSECTS
STARTS
TABLES
TABLE
TO
UPDATE
VALUES
VIEW
WHERE
WITH
ID
NUMBER
DOCID
STRING