반응형
요구사항으로
Google Sheets 를 통해 보고서 형식의 조회/입력 양식 개발건이 들어왔다.
조회는 원래 문서번호에 dropdown을 생성하여 변경 시 조회하도록 구성하였으나,
Trigger의 속도가 느려, 문서 번호를 Sidebar에서 선택하는 방식으로 수정하였다.
조회 시 : BigQuery에서 데이터를 select 한 후 각각의 셀에 값 할당.
저장 시 : 조회할 영역별로 데이터를 저장.
// 미팅목적
const meetingInfo = sheet.getRange("E13:BY15").getValues();
insert query문을 string으로 생성하여 저장하도록 구한함
// 미팅목적
for (i in meetingInfo) {
valueText += meetingInfo[i][0] + '", "' // 성상
+ meetingInfo[i][8] + '", "' // 세부성상
+ meetingInfo[i][18] + '", "'; // 미팅목적 agenda
}
1. 보고서를 선택할 수 있는 Sidebar 열기
/**
* 문서번호 선택 Sidebar 열기
* @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
*/
function getReportData(sheetUrl, docNo) {
openSideBar(sheetUrl, "doc");
}
2. SideBar 생성
/**
* Side bar를 열어서 검색
* @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
* @param {string} gubun = 구분자 ( member / bp )
*/
function openSideBar(sheetUrl, gubun) {
const masterSheetName = reportListSheetName;
const title = "문서 선택";
const masterRange = "A11:F";
const sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(masterSheetName);
const data = sheet.getRange(masterRange + sheet.getLastRow()).getValues();
var loopHtml = "";
var param, displayValue;
// 문서선택 할 경우, 중복제거하여 리스트 표출
if ( gubun == "doc" ) {
const map = new Map();
for ( const character of data ) {
map.set(JSON.stringify(character), character);
}
const distinctData = [...map.values()];
for ( j in distinctData ) {
param = distinctData[j][0];
displayValue = `${distinctData[j][0]} / ${common.convertDateFormat(distinctData[j][1], "-")} / ${distinctData[j][5]}`;
loopHtml += '<div class="itemKeys"><li style=list-style:none;>';
loopHtml += '<a onClick="selectVal(\\''+param+'\\')">';
loopHtml += '<font class="itemName" size="2">' + displayValue + '</font></a>';
loopHtml += '</li></div>';
}
// 고객, 직원 선택 시
} else {
for (i in data) {
if ( gubun == "member" ) {
param = data[i][0] + "_" + data[i][1] + "_" + data[i][3] + "_" + data[i][4];
displayValue = "(" + data[i][0] + ") " + data[i][1] + " " + data[i][3];
} else if ( gubun == "bp" ) {
param = data[i][0] + "_" + data[i][1];
displayValue = data[i][0] + " (" + data[i][1] + ")";
}
loopHtml += '<div class="itemKeys"><li style=list-style:none;>';
loopHtml += '<a onClick="selectVal(\\''+param+'\\')">';
loopHtml += '<font class="itemName" size="2">' + displayValue + '</font></a>';
loopHtml += '</li></div>';
}
}
var html = HtmlService.createHtmlOutput('<html> '
+ ' <head> '
+ ' <style> '
+ ' .itemKeys {padding-bottom:5px;padding-top:5px;cursor: pointer;} '
+ ' .itemKeys:hover {background:#ECECEC;padding-bottom:7px;padding-top:7px;cursor: pointer;} '
+ ' </style> '
+ ' <script type="text/javascript"> '
+ ' function closeModal() { google.script.host.close(); }'
+ ' function goToSheet(val) { google.script.run.setSelectedValue(val, "' + sheetUrl + '", "' + gubun + '"); }'
+ ' function selectVal(val) { '
+ ' goToSheet(val); '
+ ' } '
+ ' function filter() { '
+ ' var value, name, item, i; '
+ ' value = document.getElementById("value").value.toUpperCase().trim(); '
+ ' item = document.getElementsByClassName("itemKeys"); '
+ ' for( i=0; i<item.length; i++ ) { '
+ ' name = item[i].getElementsByClassName("itemName"); '
+ ' if ( name[0].innerHTML.toUpperCase().indexOf(value) > -1 ) { '
+ ' item[i].style.display = "flex"; '
+ ' } else { '
+ ' item[i].style.display = "none"; '
+ ' } '
+ ' } '
+ ' } '
+ ' </script> '
+ ' </head> '
+ ' <body> '
+ ' <div style=position:fixed;background-color:white;width:100%;top:0;line-height:2rem;>'
+ ' <input onkeyup="filter()" type="text" id="value" placeholder="검색어 입력" style="width: 150px;"> '
+ ' <input type="button" value="닫기" onclick="closeModal()" /> '
+ ' </div> '
+ ' <br />'
+ ' <div>'
+ loopHtml
+ ' </div>'
+ ' </body> '
+ ' </html> ')
.setTitle(title)
.setWidth(500).setHeight(800);
SpreadsheetApp.getUi().showSidebar(html);
}
3. Sidebar 닫은 후 event 처리
/**
* 사이드바 선택 후 처리
* @param {string} val = 선택된 값
* @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
* @param {string} gubun = 구분자 ( member / bp )
* @param {string} plant_code = 해당 function을 호출한 sheet의 사업장코드
*/
function setSelectedValueFromClient(val, sheetUrl, gubun, plant_code) {
const html = HtmlService.createHtmlOutput("<script>google.script.host.close();</script>");
SpreadsheetApp.getUi().showSidebar(html);
const sheetName = reportSheetName;
const sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
const vals = val.split("_");
if ( gubun == "doc" ) {
setReportData(sheetUrl, null, val, plant_code);
}
}
4. 리포트 데이터 조회해서 셀에 값 채우기
const reportSheetName = "reportSheet";
const tb_report = "reportTable";
/**
* 데이터 조회해서 값 채우기
* @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
* @param {obj} spreadSheet = 해당 function을 호출한 sheet
* @param {string} docNo = 조회할문서번호
* @param {string} plant_code = 사업장번호
*/
function setReportData(sheetUrl, spreadSheet, docNo, plant_code) {
try {
// 조회를 위한 변수 설정
const sheetName = reportSheetName;
const datasetId = GLOBAL_DATASET_ID;
const tableId = tb_report;
var sheet;
if ( common.isNull(spreadSheet) ) {
sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
} else {
sheet = spreadSheet;
}
const columns = 'doc_num, write_date, check_date, ... mti_memo';
const whereState = 'doc_num = "' + docNo + '"';
const queryString = 'SELECT ' + columns + ' FROM `'
+ GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` '
+ ' WHERE status = "Y" AND ' + whereState ;
const getValues = common.queryExcute(queryString);
const values = getValues.f;
// 값채우기 시작
sheet.getRange("AR2:BB2").setValue(values[0].v); // 문서번호
sheet.getRange("BC2:BI2").setValue(values[1].v); // 작성일자
sheet.getRange("BJ2:BP2").setValue(values[2].v); // 승인일자
sheet.getRange("BR2:BY2").setValue(values[3].v); // 팀장
...
sheet.getRange("E18:R18").setValue(values[18].v); // 고객기본정보 - 지역
sheet.getRange("S18:AR18").setValue(values[19].v); // 고객기본정보 - 고객명
sheet.getRange("AS18:BA18").setValue(values[20].v); // 고객기본정보 - 고객코드
sheet.getRange("BB18:BI18").setValue(values[21].v); // 고객기본정보 - 고객유형
...
sheet.getRange("A79:BY82").setValue(values[240].v); // 팀장검토결과
// 값채우기 끝
// number format
const formatInfo = ['AB25:AF34'
, 'AH25:AL34'
, 'AX25:BB34'
, 'BM25:BQ34'
, 'AE44:AI48'
, 'AK44:AO48'
, 'AR44:AU48'
, 'AY70:BC74'
, 'BE70:BI74'
];
for ( var i in formatInfo ) {
const applyRange = formatInfo[i];
sheet.getRange(applyRange).setNumberFormat("#,##0").setHorizontalAlignment("right");
}
const rangeUnitTon = ['AG25:AG34' // 고객별거래현황 - 총배출량
, 'AM25:AM34' // 거래량 - 총배출량
, 'AJ44:AJ48' // 가격제안 - 반입량
, 'BD70:BD74' // 경쟁영업정보 - 반입량
];
const rangeUnitWonPerTon = ['BC25:BD34' // 고객별거래현황 - 처리단가
, 'BR25:BS34' // 고객별거래현황 - 운반단가
, 'AP44:AQ48' // 가격제안 - 반입단가
, 'BJ70:BK74' // 경쟁영업정보 - 반입단가
];
const rangeUnitYear = "AY44:AY48"; // 가격제안 - 계약기간
if ( values[21].v == "배출처" ) {
const nextCell = sheet.getRange("BJ18:BQ18"); // 기존신규
const nextNextCell = sheet.getRange("BR18:BY18"); // 영업단계
const validationRange1 = "X5:X60"; // 기존신규
const validationRange2 = "Y5:Y60"; // 영업단계
const masterSheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(masterSheetName);
const targetRange1 = masterSheet.getRange(validationRange1);
const targetRange2 = masterSheet.getRange(validationRange2);
const validationRule1 = SpreadsheetApp.newDataValidation().requireValueInRange(targetRange1).build();
const validationRule2 = SpreadsheetApp.newDataValidation().requireValueInRange(targetRange2).build();
nextCell.setDataValidation(validationRule1);
nextNextCell.setDataValidation(validationRule2);
}
// 단위 세팅
sheet.getRangeList(rangeUnitTon).setValue("톤");
sheet.getRangeList(rangeUnitWonPerTon).setValue("원/톤");
sheet.getRange(rangeUnitYear).setValue("년");
} catch (error) {
var msg01 = error.message;
common.alertMessage("❌ Error", "Error Message : " + msg01);
Logger.log(msg01);
}
}
5. 리포트 저장하기
/**
* 저장하기
* @param {string} sheetUrl = 해당 function을 호출한 sheet의 URL
* @param {string} plant_code = 해당 function을 호출한 sheet의 사업장코드
*/
function saveReportData(sheetUrl, plant_code) {
try {
const sheetName = reportSheetName;
const datasetId = GLOBAL_DATASET_ID;
const tableId = tb_report;
const sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
// 작성자 정보 필수값 체크
const writerInfo = sheet.getRange("E7:AA7").getValues();
if ( common.isNull(writerInfo[0][0]) ) {
common.alertMissParam("작성자 정보");
openSideBar(sheetUrl, "member");
return;
}
// 고객명 필수값 체크
const customerInfoUp = sheet.getRange("E18:BY18").getValues();
if ( common.isNull(customerInfoUp[0][14]) ) {
common.alertMissParam("고객 정보");
openSideBar(sheetUrl, "bp");
return;
}
// 미팅목적
const meetingInfo = sheet.getRange("E13:BY15").getValues();
// 고객기본정보 아래
const customerInfoDown = sheet.getRange("E22:BY22").getValues();
// 고객별 거래현황
const customerDealInfo = sheet.getRange("E25:BY34").getValues();
// 고객진단정보
const customerDiagnosis = sheet.getRange("E37:BY37").getValues();
// 가격제안
const priceOffer = sheet.getRange("E44:BY48").getValues();
// 가격외제안
const priceOfferOther = sheet.getRange("E51:BY51").getValues();
// 후속계획
const followPlan = sheet.getRange("E57:BY58").getValues();
// 경쟁업체정보
const competitorInfo = sheet.getRange("E64:BY66").getValues();
// 경쟁영업정보
const competitiveSalesInfo = sheet.getRange("E70:BY74").getValues();
// 팀장 검토 결과
const leaderComment = sheet.getRange("A79:BY82").getValues();
// 문서번호
var docNo = sheet.getRange("AR2:BB2").getValue();
// 작성일자
const saveTime = common.getDateTime("BQ");
var write_date = saveTime.substring(0, 10);
var base_year = write_date.substring(0,4);
// 로그인유저 정보
const userInfo = common.getUserInfo();
var values;
// 수정일 경우
if ( !common.isNull(docNo) ) {
var res = common.confirmMessage("⚠️확인", docNo + " 를 수정하시겠습니까?");
if ( res == false ) {
common.alertMessage("⚠️알림", "저장을 취소하였습니다.");
return;
}
const columns = "write_date, check_date, checker, create_dt, create_user";
const whereState = 'doc_num = "'+docNo+'"';
const queryString = 'SELECT ' + columns + ' FROM `'
+ GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` '
+ ' WHERE status = "Y" AND ' + whereState ;
const getValues = common.queryExcute(queryString);
values = getValues.f;
// update
const updateQuery = 'UPDATE `' + GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` SET '
+ ' status = "N" '
+ ' , update_dt ="' + saveTime + '" '
+ ' , update_user ="' + userInfo.userId + '" '
+ ' WHERE status = "Y" AND ' + whereState ;
const updateResult = common.queryExcute(updateQuery);
if ( !updateResult ) {
common.alertMessage("❌ 실패", "데이터 처리 실패. 관리자 문의 필요.");
return;
}
}
var queryString = 'SELECT IFNULL(MAX(CAST(SUBSTR(doc_num, 9, 4) AS INT64)) + 1, 1) AS maxno FROM `'
+ GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId
+ '` WHERE status = "Y" AND plant_code = "' + plant_code + '" AND base_year = "' + base_year + '"';
const maxDocNo = common.queryExcute(queryString);
const insertState = "INSERT INTO `" + GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` VALUES '
const plantName = plant_code=="AB" ? "A사업장" :
(plant_code=="AC" ? "B사업장" :
(plant_code=="AD" ? "C사업장" :
(plant_code=="AA" ? "D사업장" :
"E사업장" )));
var check_date = "null";
var checker = "";
var update_dt = "null";
var update_user = "";
var create_dt;
if ( common.isNull(docNo) ) {
docNo = plantName + "-" + base_year + "-" + common.lpad(maxDocNo.f[0].v, 4, "0");
create_dt = saveTime;
create_user = userInfo.userId;
} else {
write_date = values[0].v; // 작성일 엎어치기
check_date = common.isNull(values[1].v) ? "null" : `"${values[1].v}"`; // 확인일 엎어치기
checker = values[2].v; // 확인자 엎어치기
create_dt = values[3].v; // 등록일 엎어치기
create_user = values[4].v; // 등록자 엎어치기
update_dt = `"${saveTime}"`; // 수정일
update_user = userInfo.userId; // 수정자
}
var valueText = '("';
valueText += docNo + '", "' // 신규문서번호
+ base_year + '", "' // 기준년도
+ plant_code + '", "' // 사업장코드
+ write_date + '", ' // 작성일
+ check_date + ', "' // 확인일
+ checker + '", "'; // 확인자
// 작성자정보
valueText += writerInfo[0][0] + '", "' // 사번
+ writerInfo[0][8] + '", "' // 이름
+ writerInfo[0][13] + '", "' // 직책
+ writerInfo[0][18] + '", "' // 직급
+ plantName + ' 사업장", "'; // 소속매립장
// 미팅목적
for (i in meetingInfo) {
valueText += meetingInfo[i][0] + '", "' // 성상
+ meetingInfo[i][8] + '", "' // 세부성상
+ meetingInfo[i][18] + '", "'; // 미팅목적 agenda
}
// 고객기본정보 위
valueText += customerInfoUp[0][0] + '", "' // 지역
+ customerInfoUp[0][14] + '", "' // 고객명
+ customerInfoUp[0][40] + '", "' // 고객코드
+ customerInfoUp[0][49] + '", "' // 고객유형
+ customerInfoUp[0][57] + '", "' // 기존신규
+ customerInfoUp[0][65] + '", "'; // 영업단계
...
// 후속계획
for (i in followPlan) {
valueText += (common.isNull(followPlan[i][0]) ? 'null' : `"${common.convertDateFormat(followPlan[i][0], "-")}"`) + ', "' // 재방문일정 - DATE
+ followPlan[i][7] + '", "' // 목적agenda
+ followPlan[i][28] + '", "' // 영업계획
+ followPlan[i][50] + '", '; // 지원요구사항
}
...
// 팀장 검토 결과
valueText += leaderComment[0][0] + '", "'; // 팀장검토결과
// 기타
valueText += 'Y" , "' // status
+ create_dt + '", "' // create_dt
+ create_user + '", ' // create_user
+ update_dt + ', "' // create_dt
+ update_user + '" ' // create_user
valueText += ')';
var insertResult = common.queryExcute(insertState + valueText);
if ( !insertResult ) {
common.alertMessage("❌ 실패", "데이터 처리 실패. 관리자 문의 필요.");
} else {
common.alertMessage("✔️ 성공", "저장에 성공하였습니다.");
sheet.getRange("AR2:BB2").setValue(docNo);
sheet.getRange("BC2:BI2").setValue(write_date);
}
} catch (error) {
var msg01 = error.message;
common.alertMessage("❌ Error", "Error Message : " + msg01);
Logger.log(msg01);
}
}
반응형
'GCP > Apps Script' 카테고리의 다른 글
| Apps Script로 MSSQL 데이터를 분단위 trigger로 sync 유지하기 (0) | 2022.12.27 |
|---|---|
| 관리용 Apps Script로 여러개의 Google Sheets 템플릿 관리하기 (0) | 2022.12.21 |
| Apps Script로 JDBC를 이용해 Oracle 데이터 가져오기 (0) | 2022.09.19 |
| Apps Script로 간단한 웹페이지 배포하기 (1) | 2022.09.19 |
| Apps Script로 Google Sheets에서 Date 정보 가공하기 (0) | 2022.09.16 |