Google Apps Script that updates spreadsheet and shows chart based on BigQuery query

BQ to SpreadSheet.js

// replace with project number
var projectNumber = 'xxxxxxx'; 

// replace with Sheets's ID
var ssid = "xxxxxxx";

var ss = SpreadsheetApp.getActiveSpreadsheet();


function getSpreadsheetId(){
  Logger.log(ss.getId());
}

function onOpen() {
  var menuEntries = [ 
    {name: "Run query in this line", functionName:"queryLine"},
    {name: "refresh weatherstation data", functionName : "updateWeatherStation"}
  ];
  ss.addMenu('BigQuery', menuEntries);
}
function queryLine(){
  var row=SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
  if(row && row>1){
    //   SpreadsheetApp.getUi().alert("["+row+"]");
    var sheetname=SpreadsheetApp.getActiveSheet().getRange(row, 1).getValue();
    if(!sheetname || ""==sheetname){
      sheetname="temp";
    }
    var query=SpreadsheetApp.getActiveSheet().getRange(row, 2).getValue();
    var userQueryCache=SpreadsheetApp.getActiveSheet().getRange(row, 3).getValue();
    if(!userQueryCache){
      userQueryCache=false;
    }
    var useLegacySql=SpreadsheetApp.getActiveSheet().getRange(row, 5).getValue();
    Logger.log("useLegacySql : "+useLegacySql);
    if(useLegacySql==true){
      useLegacySql=true;
      Logger.log("changed1");
    }else if(useLegacySql===""){
      useLegacySql=true;
      Logger.log("changed2");
    }
    Logger.log("useLegacySql : "+useLegacySql);
    
    Logger.log(query);
    runQuery(query, sheetname, userQueryCache,useLegacySql, true)
    // SpreadsheetApp.getUi().alert("["+row+"]"+name+":"+query);
  }
}

function getSheet(sheetname){
  var sheet=SpreadsheetApp.openById(ssid).getSheetByName(sheetname);
  //var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
  if(!sheet){
    //sheet=SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetname);
    sheet=SpreadsheetApp.openById(ssid).insertSheet(sheetname);
  }
  return sheet;
}
function test(){
  var query=SpreadsheetApp.getActiveSheet().getRange("B30").getValue();
  runQuery(query, "test", false);
}
function test2(){
  var query=getSheet("config").getRange("B59").getValue();
  var tab=getSheet("config").getRange("A59").getValue();
  var useLegacySql=getSheet("config").getRange("E59").getValue();
  runQuery(query, tab, false, useLegacySql,useLegacySql , true);
}
function updateWeatherStation(){
  var query=getSheet("config").getRange("B58").getValue();
  var tab=getSheet("config").getRange("A58").getValue();
  var useLegacySql=getSheet("config").getRange("E58").getValue();
  runQuery(query, tab, false, useLegacySql,false );
}

function runQuery(query, sheetname, useQueryCache, useLegacySql, opensidebar){
  Logger.log("#runQuery : useLegacySql = "+useLegacySql);
  Logger.log("#runQuery : useQueryCache = "+useQueryCache);
  Logger.log("#runQuery : query = "+query);
  Logger.log("#runQuery : opensidebar = "+opensidebar);
  var queryResults;
  var startdate=new Date();
  // Inserts a Query Job with an optional timeoutMs parameter.
  try {
    var resource = {query: query, timeoutMs: 1000, useQueryCache: useQueryCache};
    if(useLegacySql!=null){
      resource.useLegacySql=useLegacySql;
    }
    queryResults = BigQuery.Jobs.query(resource, projectNumber);
  } catch (err) {
    Logger.log(err);
    Browser.msgBox(err);
    return;
  }
  while (queryResults.getJobComplete() == false) {
    try {
      queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
      
      // If the Job is still not complete, sleep script for
      // 3 seconds before checking result status again
      if (queryResults.getJobComplete() == false) {
        Utilities.sleep(3000);
      }
    }catch (err) {
      Logger.log(err);
      Browser.msgBox(err);
      return;
    }
  }
  var finishdate=new Date();
  if(!queryResults.totalBytesProcessed){
    queryResults.totalBytesProcessed=0;
  }
  Logger.log("opensidebar : "+opensidebar);
  if(opensidebar==true){
    showSidebar(startdate,finishdate, queryResults.totalBytesProcessed, queryResults.cacheHit);
  }
  // Update the amount of results
  var resultCount = queryResults.getTotalRows();
  var resultSchema = queryResults.getSchema();
  
  var resultValues = new Array(resultCount);
  var tableRows = queryResults.getRows();
  
  // Iterate through query results
  if(tableRows){
    for (var i = 0; i < tableRows.length; i++) {
      var cols = tableRows[i].getF();
      resultValues[i] = new Array(cols.length);
      // For each column, add values to the result array
      for (var j = 0; j < cols.length; j++) {
        resultValues[i][j] = cols[j].getV();
      }
    }
    var sheet=getSheet(sheetname);
    
    sheet.clear();
    sheet.clearContents();
    sheet.clearFormats();
    sheet.clearNotes();
    sheet.setFrozenRows(1);
    
    var headers=new Array();
    for (var i=0;i< resultSchema.getFields().length;i++){
      headers.push(resultSchema.getFields()[i].getName());
    }
    //Logger.log(headers);
    sheet.appendRow(headers);
    sheet.getRange(2, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
    sheet.activate();
  }
  
}
function showSidebar(startdate,finishdate,totalBytesProcessed,cacheHit) {
  var t = HtmlService.createTemplateFromFile('sidebar');
  t.startdate=Utilities.formatDate(startdate, "JST", "HH:mm:ss.SSS");
  t.finishdate=Utilities.formatDate(finishdate, "JST", "HH:mm:ss.SSS");
  t.duration=finishdate-startdate;
  if(totalBytesProcessed>=1000000000000){
    t.totalBytesProcessed= Utilities.formatString('%0.00f T', Number(totalBytesProcessed/1000000000000));
  }else if (totalBytesProcessed>=1000000000){
    t.totalBytesProcessed= Utilities.formatString('%0.00f G', Number(totalBytesProcessed/1000000000));
  }else if (totalBytesProcessed>=1000000){
    t.totalBytesProcessed= Utilities.formatString('%0.00f M', Number(totalBytesProcessed/1000000));
  }else if (totalBytesProcessed>=1000){
    t.totalBytesProcessed= Utilities.formatString('%0.00f K', Number(totalBytesProcessed/1000));
  }
  if(!t.totalBytesProcessed){
    t.totalBytesProcessed=0;
  }
  t.cacheHit=cacheHit;
  t=t.evaluate().setWidth(300).setTitle('BigQuery Stats').setSandboxMode(HtmlService.SandboxMode.IFRAME);
  
  SpreadsheetApp.getUi().showSidebar(t);
}