えくせる おりこん - 経済統計練習帳

var namex=new Array();
var codex=new Array();
var number;
 
var dic=new Array();
var code=new Array();
var vs=new Array();
 
function plus() {
 
 var id="1z_eYrNLjkZRWVA68-eD9x6poZpd3pm0I4il2MMGYjPU";
 
 var ex1 = SpreadsheetApp.openById(id);
 var sheet = ex1.getSheetByName("data");  
 
  n=sheet.getLastRow();
 
 
 
  var v=sheet.getRange(1,1,n,2).getValues();
 
  dicnumber=n-1;
  for(s=1;s<dicnumber+1;s++){
  dic[s]=v[s][0];
  code[s]=v[s][1];
  }
 
  sql("1BCXvu_E0VeIFM_WN-SHx5zlLzzMr8Kgc8Qsi1weV",3,4);
 
 
  for(s=1;s<number+1;s++){
    h=0;
    for(sx=1;sx<dicnumber+1;sx++){
      if(namex[s]==dic[sx])h=100; 
    }
  if(h<50)dicnumber=dicnumber+1;
 if(h<50)dic[dicnumber]=namex[s];
  if(h<50)code[dicnumber]=codex[s];
  }
 
 Logger.log(dicnumber);
 
  for(s=0;s<dicnumber+1;s++){
  vs[s]=new Array();
  }
  vs[0][0]="name";
  vs[0][1]="code";
 
  for(s=1;s<dicnumber+1;s++){
  vs[s][0]=dic[s];
  vs[s][1]=code[s]; 
  }
 
   sheet.getRange(1,1,dicnumber+1,2).setValues(vs);
 
 
}
 
 
function sql(idx,n1,c1) {
 
 
 var sql = 'SELECT * FROM ' +idx;
 
 var result = FusionTables.Query.sqlGet(sql, {hdrs: false});
 
 var r=result.rows;
 
  sx=0;
    for(var i in r){
    c2=""+r[i][c1];
    if(c2.indexOf("NaN")>-1)c2="";
    if(c2.length>0)sx=sx+1;
    if(c2.length>0)namex[sx]=r[i][n1];
    if(c2.length>0)codex[sx]=r[i][c1];  
    }
 
 number=sx;
 
 
 
}