Skip to content

Instantly share code, notes, and snippets.

@mactkg
Forked from masuidrive/main.js
Last active June 20, 2018 08:43
Show Gist options
  • Save mactkg/7e7ce19a1651c173f310ed62ea00d92a to your computer and use it in GitHub Desktop.
Save mactkg/7e7ce19a1651c173f310ed62ea00d92a to your computer and use it in GitHub Desktop.
Google SpreadsheetからBigQueryを呼び出すスクリプト
/**
* Google Spreadsheet向けBigQuery取り込みスクリプト
* http://toreta.blog.jp/archives/20649904.html
* License: MIT 2014- Toreta, Inc.
*
* runAllQueries() をトリガーで毎日実行してください
* Queries, Single row queries, Dataの三つのシートを作って下さい
* Queries, Single row queriesのシートには実行するクエリを書きます
* A列にクエリ名、B列にクエリです。
* conuntなどの集約関数で1行しか返らないクエリは「Single row queries」、それ以外は「Queries」に書いて下さい
*/
/* 実行するProject ID */
var projectId = 'example-log';
/* 現在のシートのクエリを実行 */
function runCurrentSheetQuery() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var current_sheet = spreadsheet.getActiveSheet();
if(current_sheet.getName() == "Single row queries" || current_sheet.getName() == "Data") {
runSingleRowQuery();
}
else {
var queries_sheet = spreadsheet.getSheetByName("Queries");
for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
if(queries_sheet.getRange(i, 1).getValue() == current_sheet.getName()) {
runQuery(queries_sheet.getRange(i, 2).getValue(), current_sheet, queries_sheet.getRange(i, 3).getValue().toString());
}
}
}
}
/* シートの全クエリを実行 */
function runAllQueries() {
runSingleRowQuery();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var queries_sheet = spreadsheet.getSheetByName("Queries");
for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
var sheet_name = queries_sheet.getRange(i, 1).getValue();
var sheet = spreadsheet.getSheetByName(sheet_name);
if(sheet == null) {
sheet = spreadsheet.insertSheet();
sheet.setName(sheet_name);
}
runQuery(queries_sheet.getRange(i, 2).getValue(), sheet, queries_sheet.getRange(i, 3).getValue().toString());
}
}
/* クエリを実行 */
function runQuery(query, sheet, title) {
var request = {
query: query,
useLegacySql: false // standard SQLを使うようにする
};
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
if (rows) {
// clear exists data and set title
sheet.clear();
sheet.appendRow([title]);
// Append the headers.
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
if(headers[j].substr(-3)=="_at") {
data[i][j] = cols[j].v
if(data[i][j] < 10000000000000) {
data[i][j] = new Date(data[i][j]);
}
else if(data[i][j] < 10000000000000*1000) {
data[i][j] = new Date(data[i][j] / 1000);
}
}
}
}
sheet.getRange(3, 1, rows.length, headers.length).setValues(data);
} else {
Logger.log('No rows returned.');
}
};
/* 1行クエリを実行 */
function runSingleRowQuery() {
// Replace this value with the project ID listed in the Google
// Developers Console project.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var queries_sheet = spreadsheet.getSheetByName("Single row queries");
var result_sheet = spreadsheet.getSheetByName("Data");
for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
var request = {
query: queries_sheet.getRange(i, 2).getValue()
};
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
result_sheet.getRange(i, 2).setValue(rows[0].f[0].v);
result_sheet.getRange(i, 1).setValue(queries_sheet.getRange(i, 1).getValue());
result_sheet.getRange(i, 3).setValue(queries_sheet.getRange(i, 3).getValue());
}
};
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "このシートの内容を更新",
functionName : "runCurrentSheetQuery"
},
{
name : "全シートの内容を更新",
functionName : "runAllQueries"
}];
spreadsheet.addMenu("BigQuery", entries);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment