めす００ - 経済統計練習帳

var namex=new Array();
var codex=new Array();
 
var point=new Array();
var id=new Array();
 
function myFunction() {
 
 
   var sql = 'SELECT * FROM ' +"1IaPdTjaV7iy_lCA6LzMFtFW1W0kwP-ziqnlxNrcH";
 
  var result = FusionTables.Query.sqlGet(sql, {hdrs: false});
 
 
  var r=result.rows;
 
  sx=0;
    for(var i in r){
    sx=sx+1;
      namex[sx]= r[i][1]; 
      codex[sx]= ""+r[i][2]; 
  }
 
 
  dicnumber=sx;
 
 
 
 
    sql = 'SELECT * FROM ' +"1Za6b1nBoE8UJEYeFjG5D62LV6-w-5atQTnc3ode2";
 
  result = FusionTables.Query.sqlGet(sql, {hdrs: false});
 
  r=result.rows;
 
    sx=0;
    for(var i in r){
    sx=sx+1;
   point[sx]= r[i][1]; 
   id[sx]=""+r[i][3]; 
   if(id[sx].indexOf("N")>-1)id[sx]="0000000000";  
    }
 
  number=sx;
 
  var a=new Array();
 
  for(s=1;s<dicnumber+1;s++){
  a[s]=new Array();
  }
 
  for(s=1;s<dicnumber+1;s++){
  for(sx=1;sx<50;sx++){
  a[s][sx]="";
  }}
 
 
   Logger.log(number);
  for(tr=1;tr<dicnumber+1;tr++){
  str=""+codex[tr];
  if(str.length<2)str="45123456789";
  Logger.log(str+","+namex[tr]);
  sx=0;
  for(s=1;s<number+1;s++){
  if(str==id[s])sx=sx+1;
  if(str==id[s])a[tr][sx]=point[s];  
  }
  }
 
 
  var vs=new Array();
 
  for(s=0;s<dicnumber+1;s++){
  vs[s]=new Array();
  } 
  vs[0][39]="";
 
  for(s=1;s<dicnumber+1;s++){
  vs[s][0]=namex[s];
  } 
 
  for(s=1;s<dicnumber+1;s++){
  for(sx=1;sx<40;sx++){
  vs[s][sx]=a[s][sx];
  } }
 
 
 
 
 
   idx="1c5OnldC3j86T4AirjuepE8dAplM73MTEyqlJ_ZTRl7M";
  ex = SpreadsheetApp.openById(idx);
 
 
  var shx = ex.getSheetByName("data");
 
  shx.getRange(1,1,dicnumber+1,40).setValues(vs);
 
 
 
}