구글 스프레드시트 날짜 시간 자동 계산 입력 함수(스크립트 자동 계산 입력, onEdit, Date, Time 함수)

구글 스프레드시트로 작업 시 날짜를 더하거나 빼주어야 할 때 구글 스크립트를 통해서 해결 할 수 있는 방법입니다.​

첫번째로 입력한 날짜를 더하거나 빼서 자동 입력해주는 함수입니다.

​[ 사용법 ]

1. 메뉴에서 도구 > 스크립트 편집기 클릭하여 Code.gs(코드.gs)로 들어갑니다.

2. 아래 스크립트를 복사 후 붙여 넣기 한 다음 저장해줍니다.

3. 실행되고 있는 시트, 현재 입력 한 값이 날짜이면, 설정한 값대로, 바로 옆 오른쪽 셀에 현재 날짜에 이틀을 더한 후 입력해주고, 그 다음 셀에는 현재 날짜에서 사흘을 뺀 날짜를 입력 해줍니다.

function onEdit(){

// 현재 실행되고 있는 시트 선택
var sheet = SpreadsheetApp.getActive();

// 현재 커서가 있는 셀 선택
var currentCell = sheet.getActiveCell();

// 현재 커서가 있는 셀의 값을 dateOrigin에 할당
var dateOrigin = sheet.getActiveCell().getValue();

//현재 셀 입력이 날짜일 경우
if (dateOrigin instanceof Date) {

//입력 날짜 + 2 해서, 입력 다음 셀에 입력
currentCell.offset(0, 1).setValue(new Date(dateOrigin.setDate(dateOrigin.getDate() +2)));

//맨처음 입력 한 셀의 다음 셀이 현재 셀이 되고, 그 셀에 다시 - 3 한 다음 그 다음 셀에 입력,
//입력되는 날짜는 맨 처음 입력 날짜 하루 전날이 되게 됩니다.
currentCell.offset(0, 2).setValue(new Date(dateOrigin.setDate(dateOrigin.getDate() -3)));

} else {

//날짜가 아닐 경우
return;
 }
}

두번째는 자동으로, 지정한 날짜(아래 예, + 7일, 일주일), 지정한 날짜(아래 예, +21, 삼주일)이 옆에 나란히 입력되게 하는 스크립트입니다.

​[ 사용법 ]

1. 메뉴에서 도구 > 스크립트 편집기 클릭하여 Code.gs(코드.gs)로 들어갑니다.

2. 아래 스크립트를 복사 후 붙여 넣기 한 다음 저장해줍니다.

​

​​

시트 어느 곳에서나 원하는 곳에 날짜를 입력, 예를 들어 '2021-05-01'을 입력하면 아래와 같이 자동으로, 입력한 날짜 + 7, 입력한 날짜 + 21이 되는 날짜가 오른쪽 행에 나란히 입력됩니다.

​dateOrigin: 입력하는 셀의 값을 판별해서 날짜 값인 경우 입력

secondDate: 두번째 컬럼에 들어갈 날짜 값

thirdDate: 세번째 컬럼에 들어갈 값

​onEdit함수: 스프레드시트에 어떤 입력 이벤트가 있으면 그에따라 자동으로 실행(심플 트리거)되게 되어 있는 함수 입니다.

​IF문은 입력 한 내용이 날짜인지를 판별하여 다른 입력이 있을 경우에 실행되지 않음

function onEdit(){

  var sheet = SpreadsheetApp.getActive();   
  var currentCell = sheet.getActiveCell();
  var dateOrigin = currentCell.getValue();
  
  if (currentCell.getValue() instanceof Date) {
    //현재 셀 입력이 날짜일 경우
    Logger.log(currentCell.getValue());
   var secondDate = new Date(currentCell.getValue());
   var thirdDate = new Date(currentCell.getValue());
       secondDate.setDate(dateOrigin.getDate()+7);
       thirdDate.setDate(dateOrigin.getDate()+21);

       currentCell.offset(0, 1).setValue(secondDate);
       currentCell.offset(0, 2).setValue(thirdDate);
   } else {
     //날짜가 아닐 경우
     return;
 }
}

 

세번째는 시트의 첫번째 행(A행)에 값이 입력 혹은 변경 되면, 그 옆 B행에 날짜와 시간을 넣어주는 매크로(스크립트) 함수입니다.

​

[ 사용법 ]

1. 메뉴에서 도구 > 스크립트 편집기 클릭하여 Code.gs(코드.gs)로 들어갑니다.

2. 아래 스크립트를 복사 후 붙여 넣기 한 다음 저장해줍니다.

​아래 스크립트를 응용하면, 출근, 퇴근 시간 등을 자동으로 입력 할 수 있어 근태 관리 등을 스프레드시트로 관리 할 수 있습니다.

 

function onEdit(e) {
    var ss = e.source.getActiveSheet();    
    var currentCell = ss.getActiveCell();
    
    if( currentCell.getColumn() == 1 ) { 
      var nextCell = currentCell.offset(0, 1);
      var newDate = Utilities.formatDate(new Date(),"GMT+09:00", "yyyy. MM. dd hh:mm:ss");
      nextCell.setValue(newDate);
    }
  }