GoogleスプレットシートからBigQueryにデータをインポートするGoogleAppScript（GAS）

使い始める前に共通設定
サービスに「BigQuery」を入れてください

サンプルコード/main

//★★グローバル定数・ここを書き換える★★
//テーブル定義書スプシID
const sheet_openByIde_bq_setting  = スプシのID

//テーブル定義書のシート名
const sheet_name_bq_setting ="BQ設定用"

//テーブル定義書設定のセル
const projectIdCells = "D2" //プロジェクトID
const datasetIdCells = "E2" //データセットID

//テーブル定義書の行列番号
const tb_definition_row = 5　//開始行
const tb_definition_name_col = 2　//カラム名の列
const tb_definition_type_col = 3　//データ型の列

function doGet() {
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function main() {

  //設定
  const bq_setting_spreadsheet = SpreadsheetApp.openById(sheet_openByIde_bq_setting);//セッティング_スプシオブジェクト
  const bq_setting_sheet = bq_setting_spreadsheet.getSheetByName(sheet_name_bq_setting);//セッティング_シートを指定（BQ設定用)

  //BQ側のID
  const projectId = bq_setting_sheet.getRange(projectIdCells).getValue()//プロジェクトID
  const datasetId = bq_setting_sheet.getRange(datasetIdCells).getValue()//データセットID

  let sheet_name //スプシのシート名
  let tableId //ビッククエリのテーブル名

  //BQ設定用シートから、「シート名」「テーブル名」を配列で取得
  let parameter_arry =　get_parameter_arry(bq_setting_sheet)

  //ループ①BQ設定用シートの値読み込み

  for(let item of parameter_arry){

    setting_sheet_name = "【定義】" + item[0];//シート名
    sheet_name = item[0];//シート名
    tableId = item[1];//bqのテーブル名

    //■bqテーブル削除
    drop_table_exist(tableId,projectId,datasetId) 

    let bq_setting_target_sheet = bq_setting_spreadsheet.getSheetByName(setting_sheet_name);//セッティング_定義書シートを指定

  　//テーブル定義の最下行を取得
    let bq_setting_target_last_row = 
    bq_setting_target_sheet.getRange(tb_definition_row, tb_definition_name_col).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()

    let name//bqへの新規テーブル設定用name
    let type//bqへの新規テーブル設定用name
    let fields_array = []//bqへの新規テーブル設定用array

    //ループ②テーブル定義書
    for (let my_row = tb_definition_row; my_row <= bq_setting_target_last_row; my_row++) {
      name = bq_setting_target_sheet.getRange(my_row,tb_definition_name_col).getValue()
      type = bq_setting_target_sheet.getRange(my_row,tb_definition_type_col).getValue()

      //例：{name: 'date_monthly', type: 'DATE'},//対象年月日
      fields_array.push({name,type})//bq用定義
    }//ループ②終わり

    //■新規テーブル追加
    tables_insert(tableId,fields_array,projectId,datasetId) 


    //■スプシ⇒BQ
    let sheet = bq_setting_spreadsheet.getSheetByName(sheet_name);//シートオブジェクト
    sreadsheet_to_bq(sheet,tableId,projectId,datasetId)

    //if(!exit_sub()) return;//exitsub
    //console.log("途中強制終了"); //exitsub確認用メッセージ

  }//ループ①終わり


}

///////////////////////////////////////////////////////////////////////////////////////////////////

function get_parameter_arry(sheet){
  //変数の宣言
  let last_row = sheet.getRange(1,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();//最下行の取得
  var two_dimension_array =　[]//二次元配列
  //行方向のループ※１行目は見出し行なので無視

  for (let my_row = 2; my_row <= last_row; my_row++) {
      let array = []//一次元配列
      //列方向のループ
      for (let my_col = 1; my_col <= 2; my_col++) {//2列分を配列に取得
        array.push(sheet.getRange(my_row,my_col).getValue());//一次元配列に追加
      }
      two_dimension_array.push(array)//二次元配列に追加
  }
  return two_dimension_array//戻り値
}

///////////////////////////////////////////////////////////////////////////////////////////////////

サンプルコード/共通コード

/////////////////////////////////////////////////////////////////////////////////
//開発用・途中中断用
function exit_sub(){
  return false;
}
/////////////////////////////////////////////////////////////////////////////////

/////////////////////////////////////////////////////////////////////////////////
//bqテーブル削除
function drop_table_exist(tableId,projectId,datasetId){

  //洗い替えのためテーブル削除 DROP TABLE IF EXIST※BigQueryにTruncate Table文は存在しません。
  //try catchはerrorキャッチです。
  try {
    BigQuery.Tables.remove(projectId, datasetId, tableId);
  } catch(e) {
    console.log(e);
  }
}
/////////////////////////////////////////////////////////////////////////////////


/////////////////////////////////////////////////////////////////////////////////
//テーブルを新規追加
function tables_insert(tableId,fields_array,projectId,datasetId){

  BigQuery.Tables.insert({
    tableReference: { projectId, datasetId, tableId },
    schema: {
      fields: fields_array
    }
  }, projectId, datasetId);
}
/////////////////////////////////////////////////////////////////////////////////

//スプシ→bq

function sreadsheet_to_bq(sheet,tableId,projectId,datasetId) {


  //最下行を取得
  let last_row = sheet.getRange(1,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()
　//最右行を取得
  let last_col = sheet.getRange(1,1).getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn()

  //シートをtableとして取得
  const range = sheet.getRange(1,1,last_row,last_col);//範囲を指定
  const table = get_table(range);//シート内の内容をtableとして取得

  ////console.log(table)//確認用
  //レコード追加のジョブ
  const data = Utilities.newBlob(table);
  try {
    BigQuery.Jobs.insert({
      configuration: {
        load: {
          destinationTable: { projectId, datasetId, tableId }
        }
      }
    }, projectId, data);

  } catch(e) {
    console.log(e);
  }
}



/////////////////////////////////////////////////////////////////////////////////



//シート内の内容をtableとして取得

function get_table(range) {
  let data = range.getValues();
  let text = '';
  //行方向のループ
  for (let i = 1; i < data.length; i++) {
      //最後の行以外は改行コード込み
      if (i < data.length-1) {
        text += data[i].join(',') + '\n';
      }
      else {
        text += data[i].join(',');
      }
  }
  return text;
}