[주식관리] Google sheets 매일 평가액/수익률 자동기록 오류 수정(sleep 함수)

매일 같이 변하는 주가 및 수익률을 기록하기 위해서 google sheets의 Apps scripts를 이용하여 아래 코드를 작성하였다. 

이것을 Apps script의 trigger 기능을 이용하여 매일 오후 5시마다 자동으로 기록하도록 하였다. 

function recordHistory() {
  var A = SpreadsheetApp.getActiveSpreadsheet(); // 현재 활성화된 스프레드 시트에 대한 정보를 A에 할당
  var B = A.getSheetByName("History"); // 현재 할당된 변수 A의 Sheet 중 "History"시트를 B(시트)변수에 할당
 // 3행까지 갈 때 모든 셀값이 정상적으로 계산되어야 함. 
  var C = B.getRange("A2:M2");  // 현재 할당된 변수 B시트(History 시트)의 A2~J2 범위를 C에 할당
  var D = C.getValues(); // C의 값을 D에 배열형태로 저장한다.
  D[0][0] = new Date();  // D의 첫번째 셀에는 현재 시간을 저장한다.
   // D는 2차원 형태의 배열이자만 C가 1행의 데이터만 D에 저장했기 때문에 실제로는 1차원 저장형태로 나타난다.. 
  B.appendRow(D[0]); // 시트의 가장 마직막 행에 D의 첫번째 행의 값을 기록한다.
};

2021.03.28 - [주식/Google sheets] - 주식 관리 Sheet에 매일 평가액/수익률 기록하기 (Feat. Google sheets script)

주식 관리 Sheet에 매일 평가액/수익률 기록하기 (Feat. Google sheets script)

 

처음에는 작동을 잘 했지만 시간이 갈수록 기록이 쌓이고 종목이 많아져서 기록함에 문제가 발생하였다.

• 거래기록에서 환율을 googlefinance함수를 이용해서 가지고 오는데, 거래기록이 많아짐에 따라 시트 값이 채워지는데 시간이 걸림
• 배당액 정보를 국내/국외 종목 모두에서 가지고 오는데, 가끔 정보를 가지고 오는 속도가 느려질때가 있음.
• 기타 알수 없는 이유로 거래기록 함수( recordHistory() )가 실행되는 순간 시트에 값이 덜 채워져 셀값이 에러상태로 기록이 됨. 

기

잠시 생각해본 끝에 임기 응변으로 3행 다음에 잠시 실행을 멈추는 기능을 찾아보기로 함.

Apps scripts가 JAVA인지 C인지도 모르는 나로써는 구글로 검색...

의식의 흐름대로 검색 구글에서 "google sheets sleep"을 해 봤더니 첫 페이지에 원하는 함수가 나옴...

https://www.google.co.kr/search?q=google+sheets+sleep

google sheets sleep - Google 검색

 

Apps scripts 코드에 Utilities.sleep 구문 추가 인수로 밀리초 입력하면 코드 실행 중간에 인수만큼 멈추었다 실행됨.

 

시트값 업데이트하는데 얼마나 걸리는지 알 수 없어서 그냥 최대값(300초)로 설정. 

어차피, 자동으로 실행될테니 300초 이상이어도 상관없음.

function recordHistory() {
  var A = SpreadsheetApp.getActiveSpreadsheet(); // 현재 활성화된 스프레드 시트에 대한 정보를 A에 할당
  var B = A.getSheetByName("History"); // 현재 할당된 변수 A의 Sheet 중 "History"시트를 B(시트)변수에 할당
  Utilities.sleep(300000); //300초 멈추었다 다음 행 수행
  var C = B.getRange("A2:M2");  // 현재 할당된 변수 B시트(History 시트)의 A2~J2 범위를 C에 할당
  var D = C.getValues(); // C의 값을 D에 배열형태로 저장한다.
  D[0][0] = new Date();  // D의 첫번째 셀에는 현재 시간을 저장한다.
   // D는 2차원 형태의 배열이자만 C가 1행의 데이터만 D에 저장했기 때문에 실제로는 1차원 저장형태로 나타난다.. 
  B.appendRow(D[0]); // 시트의 가장 마직막 행에 D의 첫번째 행의 값을 기록한다.
};

아직까지 오류는 발견되지 않음.