ある値に最も近い値を BigQuery のテーブルから取得するGAS

前提

・ある数値に最も近い値を別のテーブルから取得したい
・取得対象のテーブルは BigQuery に存在する
・今回はある数値よりも小さい数値しか考えない（仮に大きい数字があったとしても同じような方法で対応可能）

実装

行・列のインデックスは各自のシートに合わせて変更すること。

function calcApproximation() {
 //プロジェクトID設定
 const projectId = 'プロジェクトID';
 //スプレッドシートの設定
 //近似値を求める値リストのシートをシート名称から取得
 const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("value_list");
 //出力シートのインスタンス作成。今回はValue List の横に値を出力する
 const result_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("value_list");
 //Settingsシートから計算範囲を取得
 const setting_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
 var start_row = setting_sheet.getRange("B1").getValue();
 var end_row = setting_sheet.getRange("B2").getValue();
 // start_row ~ end_row の範囲のデータを取得
 // end_row がnullの場合は最下部まで。start_row がnullの場合のエラー処理などは割愛
 if(end_row == ""){
   var data = ss.getRange(start_row, 3, ss.getLastRow).getValues();
 }else{
   var data = ss.getRange(start_row, 3, end_row-start_row+1).getValues();
 }
 Logger.log(data);
 Logger.log(data.length);
 //最後にsetRange()で列単位にシートを更新する為の二次元配列の準備
 var nearestScoreArray = [];
 // データの行数分繰り返す(今回はヘッダー行数は考慮しない)
 for(i = 0; i <= data.length-1 ; i++) {
   // 基準値よりも小さく最も近い値を取得するクエリ
   // なお、GAS から BQ API 実行する時はFROM句にプロジェクトIDやバッククォートは不要
   // それと、長いテキスト value を折り返して記載する際にはバックスラッシュ
   var sql = 
   "SELECT score FROM [dataset名称].[テーブル名称]\
    WHERE score <= "
   + data[i] +
   " ORDER BY score DESC LIMIT 1"
   Logger.log(sql)
   // BigQuery API の実行
   let query_results;
   let resource = {
    query : sql,
    timeoutMs: 1000000
   };
   try {
     query_results = BigQuery.Jobs.query(resource, projectId);
   } catch (err) {
     Logger.log(err);
     Browser.msgBox(err);
     return;
   }
   Logger.log(query_results);
   // BigQuery API のレスポンスから Value を取得
   let lower_score = query_results.rows[0].f[0].v;
   Logger.log(lower_score);
   // 最終的に setValues で列ごと更新するために２次元配列にリストの形でpushする
   nearestScoreArray.push([lower_score]);
   Logger.log(nearestScoreArray);
 }
 // 更新範囲の取得
 let updateRange = result_sheet.getRange(start_row,5, end_row-start_row+1)
 // 更新処理
 updateRange.setValues(nearestScoreArray);
}

//起動時のmenu追加
function onOpen() {
 let menus = [{name:'実行',functionName:'calcApproximation'}];
 ss.addMenu('BigQuery',menus);
}

[参考サイト]
GASでBigqueryからデータを取得して分析に活用する

ちなみに、BIgQuery API のレスポンス

BIgQuery API のレスポンスは以下のような形をしている。

{totalRows=1, kind=bigquery#queryResponse, cacheHit=true, jobReference={projectId=[プロジェクトID], location=US, jobId=job_ほげほげほげほげ}, jobComplete=true, rows=[{f=[{v=47201}]}], totalBytesProcessed=0, schema={fields=[{mode=NULLABLE, type=INTEGER, name=score}]}}