/**
* A special function that runs when the spreadsheet is open.
*/
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: '설문지 데이터 변환', functionName: 'convertData_'},
{name: '데이터 표 갱신', functionName: 'refreshTable_'}
];
spreadsheet.addMenu('특성 스카우트', menuItems);
convertData_();
}
function convertData_() {
var spreadsheet = SpreadsheetApp.getActive();
var dataSheet = spreadsheet.getSheets()[1], convertedDataSheet = spreadsheet.getSheets()[2];
var lastDataTimestamp = dataSheet.getRange(dataSheet.getLastRow(), 1).getValue();
var lastConvertedDataTimestamp = convertedDataSheet.getRange(convertedDataSheet.getLastRow(), 1).getValue();
if( lastConvertedDataTimestamp instanceof Date && lastDataTimestamp.getTime() == lastConvertedDataTimestamp.getTime() ) return;
var firstNewDataRow = 0;
if ( lastConvertedDataTimestamp instanceof Date ) {
for( firstNewDataRow = dataSheet.getLastRow(); dataSheet.getRange(firstNewDataRow, 1).getValue() > lastConvertedDataTimestamp; firstNewDataRow-- )
continue;
} else
firstNewDataRow = 2;
Logger.log( firstNewDataRow+'행부터 기록 시작' );
var newConvertedData = [ ];
var toStandard = function(num) {
if ( num % 100 == 0)
num /= 100
return num;
};
for ( var row = firstNewDataRow; row <= dataSheet.getLastRow(); row++ ) {
Logger.log( row+'행 기록 시작' );
values = dataSheet.getRange( row, 1, 1, 2 + 3*30 ).getValues()[0];
var timestamp = values[0];
var name = values[1];
for ( var c = 2; c < 2 + 3*30 && values[c] !== '' && values[c+1] !== '' && values[c+2] !== '' ; c += 3 )
newConvertedData.push( [ timestamp, name, toStandard(values[c]), toStandard(values[c+1]), toStandard(values[c+2]) ] );
}
Logger.log(newConvertedData.length);
if ( newConvertedData.length != 0 ) {
convertedDataSheet.insertRowsAfter( convertedDataSheet.getLastRow(), newConvertedData.length );
convertedDataSheet.getRange( convertedDataSheet.getLastRow()+1, 1, newConvertedData.length, 5).setValues( newConvertedData );
}
spreadsheet.getSheets()[3].sort(2);
}
function refreshTable_() {
var spreadsheet = SpreadsheetApp.getActive();
var idols = [
'테토라', '하지메', '토모야', '히나타', '미도리', '토리', '시노부', '유우타', '미츠루', '츠카사',
'스바루', '호쿠토', '마코토', '소마', '아도니스', '코가', '리츠', '마오', '유즈루', '아라시',
'케이토', '에이치', '카오루', '이즈미', '치아키', '쿠로', '와타루', '카나타', '레이', '나즈나'
];
Logger.log(idols[0]);
var tableSheet = spreadsheet.getSheets()[0], dataSheet = spreadsheet.getSheets()[2];
var data = dataSheet.getRange(2,2,dataSheet.getLastRow()-1,4).getValues();
var recipes = [ ];
var ct = 0;
for ( var i=0; i< data.length; i++) {
var key = ("0" + (data[i][1]+0)).substr(-2,2)+("0" + (data[i][2]+0)).substr(-2,2)+("0" + (data[i][3]+0)).substr(-2,2);
//Logger.log(key);
if ( recipes[key] == undefined ) {
recipes[key] = [ ];
recipes[key]['F'] = data[i][1];
recipes[key]['C'] = data[i][2];
recipes[key]['A'] = data[i][3];
ct++;
}
if ( recipes[key][data[i][0]] == undefined ) {
recipes[key][data[i][0]] = 1;
}
else
recipes[key][data[i][0]]++;
}
range = tableSheet.getRange(2,2,tableSheet.getLastRow()-1,3+idols.length);
values = range.getValues();
for ( var i = 0; i < values.length; i++) {
var key = ("0" + (values[i][0]+0)).substr(-2,2)+("0" + (values[i][1]+0)).substr(-2,2)+("0" + (values[i][2]+0)).substr(-2,2);
// Logger.log(i + ' ' + values[i][0]);
var j = 3;
for ( var k = 0; k < idols.length ; k++ ) {
if ( recipes[key] == undefined ) continue;
if ( recipes[key][idols[k]] == undefined )
values[i][j++] = 0;
else
values[i][j++] = recipes[key][idols[k]];
}
}
range.setValues(values);
tableSheet.sort( 35, false );
spreadsheet.getSheets()[3].sort(2);
}
|