#ajn21のスクリプト

github_ranking.js

function BigQueryLiveDemo() {
  //6219749
  
  var list = BigQuery.Projects.list();
  
  Logger.log(list);
  
  //プロジェクトIDの取得
  var projectId = list.getProjects()[0].getId();
  
  //Datasetsのインスタンス作成
  var datasets = BigQuery
      .newDataset()
      .setDescription("ajnのデモ")
      .setDatasetReference(
        BigQuery.newDatasetReference()
        .setDatasetId("ajn").setProjectId(projectId)
      );
  
  //インサート
  datasets = BigQuery.Datasets.insert(datasets);
  
  //テーブル作成
  var table = BigQuery
      .newTable()
      .setTableReference(
        BigQuery
        .newTableReference()
        .setDatasetId(datasets.getDatasetReference().getDatasetId())
        .setProjectId(projectId)
        .setTableId("githubranking")
      )
      .setFriendlyName("github-ranking")
      .setSchema(
        BigQuery
        .newTableSchema()
        .setFields(
          [
            Utilities.jsonParse(
              BigQuery
              .newTableFieldSchema()
              .setType("string")
              .setName("lang")
            ),
            Utilities.jsonParse(
              BigQuery
              .newTableFieldSchema()
              .setType("integer")
              .setName("count")
            )
          ]
        )
      );
      
  //インサート
  table = BigQuery.Tables.insert(table);
  
  var queryString = "SELECT repository_language , count(repository_url) c FROM [publicdata:samples.github_timeline] group by repository_language order by  c desc";

  var job =
      BigQuery
      .newJob()
      .setJobReference(
        BigQuery
        .newJobReference()
        .setProjectId(projectId)
      )
      .setConfiguration(
        BigQuery
        .newJobConfiguration()
        .setQuery(
          BigQuery
          .newJobConfigurationQuery()
          .setQuery(queryString)
          .setDestinationTable(table.getTableReference())
          .setWriteDisposition("WRITE_TRUNCATE")
        )
      );
  var result = BigQuery.Jobs.insert(job);
  
  var count = 0;
  
  while(true) {
    var insertedJob = BigQuery.Jobs.get(projectId, result.getJobReference().getJobId());
    
    if(insertedJob.getStatus().getState() == "DONE") {
      break;
    }
    
    if(insertedJob.getStatus().getErrors() != null && insertedJob.getStatus().getErrors().length > 0) {
      Logger.log(insertedJob);
      throw new Error("なんかエラーだよ");
    }
    
    if(count > 1000) {
      throw new Error("タイムアウト");
    }
    
    Logger.log(++count);
    Utilities.sleep(1000);
  }
  var queryResult = BigQuery.Jobs.getQueryResults(projectId, insertedJob.getJobReference().getJobId());
  
  
//  var queryResult = Jobs.query(projectId,queryString);
  var list = [];
  
  //2次元配列へ
  for(var i = 0;i < queryResult.getRows().length; i++) {
    var row = queryResult.getRows()[i];
    
    list.push([
      row.getF()[0].getV(),
      row.getF()[1].getV()
    ]);
  }
  var sheet =  SpreadsheetApp.getActive().insertSheet();
  
  sheet.getRange(1,1,1,2).setValue([["Lang" , "Count"]]);
  
  sheet.getRange(2, 1,list.length , 2).setValues(list);
  
  
  //チャートの作成
  var chart = sheet.newChart()
      .addRange(sheet.getDataRange())
      .setChartType(Charts.ChartType.BAR)
      .setPosition(1, 1, 0, 0)
      .build();
    
  sheet.insertChart(chart);
  
  
}