sainyuu++ - 経済統計練習帳

var artist=new Array();
var code=new Array();
var dic_name=new Array();
var dic_key=new Array();
var dic_code=new Array();
 
 
function myFunction() {
 
 
 
oricon();
 
Logger.log(dicnumber);
 
var idp="1fuIfnIKC8HSjzk6W5K0r8I2l3j4gWI22DncpfxBqWsI";
var exp = SpreadsheetApp.openById(idp);
var shp = exp.getSheetByName("data");  
 
var artistx=new Array();
var codex=new Array();
 
tx=0;
for(tr=2;tr<13;tr++){
 
onga=shp.getRange(tr,2).getValues();
 
sql(onga);
 
 
 
for(s=1;s<number+1;s++){
sx=dic_key.indexOf(artist[s]);
if(sx<0)tx=tx+1;
if(sx<0)artistx[tx]=artist[s];
if(sx<0)codex[tx]=code[s];
}
 
 
}
 
Logger.log(tx);
 
var artistp=new Array();
var codep=new Array();
var artist_key=new Array();
 
 
artist_key.push("noname");
n=0;
for(s=1;s<tx+1;s++){
sx=artist_key.indexOf(artistx[s]);
if(sx<0)n=n+1;
if(sx<1)artist_key.push(artistx[s]);
if(sx<0)codep[n]=codex[s];
if(sx<0)artistp[n]=artistx[s];
}
 
 
for(s=1;s<n+1;s++){
insert(artistp[s],codep[s]);
}
 
 
 
 
 
}
 
function oricon() {
 
 var idx="16zlbESM_0PL9DVz7BpKayLwLdAMFTxHFQKoPxbfL";
 
  var sql = 'SELECT artist,code FROM ' +idx;
 
 var result = FusionTables.Query.sqlGet(sql, {hdrs: false});
 
 var r=result.rows;
 
 dic_key.push("noname");
 
 dicnumber=0;
  for(i in r){
  sx=dic_key.indexOf(r[i][0]);
  if(sx<1)dic_key.push(r[i][0]);
  if(sx<1)dicnumber=dicnumber+1;
  if(sx<1)dic_name[dicnumber]=r[i][0];
  if(sx<1)dic_code[dicnumber]=r[i][1];
  }
 
 
 
}
 
function sql(idx) {
 
 
 var sql = 'SELECT artist,code FROM ' +idx;
 
 var result = FusionTables.Query.sqlGet(sql, {hdrs: false});
 
 var r=result.rows;
 
 
  sx=0;
    for(var i in r){
    c2=""+r[i][1];
c1=""+r[i][0];
if(c1.indexOf("[")>-1)c2="";
if(c1.indexOf("コメント出演")>-1)c2="";
if(c1.indexOf("曲のみ")>-1)c2="";
if(c1.indexOf("'")>-1)c2="";
 if(c2.indexOf("NaN")>-1)c2="";
    if(c2.length>0)sx=sx+1;
    if(c2.length>0)artist[sx]=r[i][0];
    if(c2.length>0)code[sx]=r[i][1];  
    }
 
 number=sx;
 
 
 
}
 
function insert(n1,c1) {
 
 var idx="16zlbESM_0PL9DVz7BpKayLwLdAMFTxHFQKoPxbfL"; 
 
var sql = "INSERT INTO "+idx+ "  (artist,code)  VALUES ('"+n1+"',"+c1+")";
 
 Logger.log(sql);
 
 
 var res = FusionTables.Query.sql(sql);
 
}