증권 데이터 읽는 구글 스크립트

구글 스크립트

스크립트 메뉴

다음 화면의 시트의 내용에 따라 실행

중요한 주의사항

// -----------------------------------------------------------------

구글스크립트 실행 일회당 6분 제한 :  (하루 6시간 제한도있다)

// -----------------------------------------------------------------

네이버 증권 데이터 읽기

// --------------------------------------------------------------------------
/* *
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
// -------------------------------------------------------------------------- 
// V3.0 NAVER - 외국인 지분 ALL - 구글스크립트 실행 6분 제한 (하루 6시간 제한도있다)
// --------------------------------------------------------------------------
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name : "종목데이터 All   - Update : V1.1 ",functionName : "updateStockData"},
    {name : "종목데이터 One   - Update : V1.0 ",functionName : "updateStockOne"},
    {name : "공매도데이터 All - Update : V1.1 ",functionName : "updateShortData"},
    {name : "외국인지분 One   - NAVER  : V3.1 ",functionName : "readStockDataOne"},
    {name : "외국인지분 All   - NAVER  : V3.0 ",functionName : "readStockDataAll"}
  ];
  sheet.addMenu("Script Center Menu", entries);
};
// --------------------------------------------------------------------------
// Read NAVER 증권 - 외국인 보유율 데이터 - One stock : D4 = 종목 & E4 = 코드
// --------------------------------------------------------------------------
function readStockDataOne(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSheet = ss.getSheetByName("ScriptSheet");    // Script & source sheet
  var tSheet = ss.getSheetByName("StockShareTrace");
  if (tSheet == null) {
    ss.insertSheet("StockShareTrace");
    tSheet = ss.getSheetByName("StockShareTrace");
  }
  var StockName = sSheet.getRange("D4").getValue(); // 종목이름
  var StockSheet = ss.getSheetByName(StockName);
  if (StockSheet == null) {
    //activeSpreadsheet.deleteSheet(tSheet);
    ss.insertSheet(StockName);
    StockSheet = ss.getSheetByName(StockName);
  }  
  sSheet.getRange("F1").setValue(StockName + " 작업 중 ...");
  sSheet.getRange("F3").setValue("거래량 데이터 읽는 중 ...");
  readOneStock(tSheet,sSheet);
  sSheet.getRange("F3").setValue("빈 줄 지우는 중 ...");
  DeleteEmptyRows(tSheet,sSheet);
  sSheet.getRange("F3").setValue("거래량 계산 중 ...");
  DoCalcu(tSheet,sSheet);
  DoHeaderLine(tSheet,sSheet);
  GetShortSale(tSheet,sSheet);

  sSheet.getRange("F3").setValue(StockName + " 데이터 복사 중 ...");
  CopySheet(tSheet,StockSheet);
  sSheet.getRange("F3").setValue("작업완료!!");  
};

공매도 데이터 읽기

// --------------------------------------------------------------------------
// Read Paxnet - 공매도 데이터 : D4 = 종목 & E4 = 코드
// --------------------------------------------------------------------------
function updateShortData(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSheet = ss.getSheetByName("ScriptSheet");    // Script & source sheet
  var tSheet = ss.getSheetByName("StockShareTrace");
  if (tSheet == null) {
    ss.insertSheet("StockShareTrace");
    tSheet = ss.getSheetByName("StockShareTrace");
  }
  var i = 0;
  var TotalLineNo = sSheet.getRange("H4").getValue();
  if(TotalLineNo <= 0) TotalLineNo = 21;             // one page all  
  do{
    var StockName = sSheet.getRange(1+i, 1, 1, 1).getValue();
    sSheet.getRange(1+i, 2, 1, 1).copyTo(sSheet.getRange('D4:E4'));
    //var StockCode = sSheet.getRange(1+i, 2, 1, 1).getText();
    if(StockName == null || StockName == "") break;
    var StockSheet = ss.getSheetByName(StockName);
    if (StockSheet == null) {
      ss.insertSheet(StockName);
      StockSheet = ss.getSheetByName(StockName);
    }
    sSheet.getRange("F1").setValue(i+1 + " " + StockName + " 작업 중 ...");
    sSheet.getRange("D4").setValue(StockName);
    //sSheet.getRange("E4").setValue(1+i, 2, 1, 1);
    sSheet.getRange("F3").setValue(StockName + " 공매도 데이터 읽는 중 ...");
    GetShortSale(StockSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 처리완료.");
    i++;
  }while(StockName != null && StockName != "" && i < 20);
  sSheet.getRange("F3").setValue("작업완료!!");  
};

네이버 증권 단일 종목 데이터 업데이트

// --------------------------------------------------------------------------
// Read NAVER 증권 - 종목데이터 업데이트 : D4 = 종목 & E4 = 코드
// --------------------------------------------------------------------------
function updateStockData(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSheet = ss.getSheetByName("ScriptSheet");    // Script & source sheet
  var tSheet = ss.getSheetByName("StockShareTrace");
  if (tSheet == null) {
    ss.insertSheet("StockShareTrace");
    tSheet = ss.getSheetByName("StockShareTrace");
  }
  var i = 0;
  var TotalLineNo = sSheet.getRange("H4").getValue();
  sSheet.getRange("D3").setValue(1);                // Do only One page
  if(TotalLineNo <= 0) TotalLineNo = 21;            // one page all  
  do{
    var StockName = sSheet.getRange(1+i, 1, 1, 1).getValue();
    sSheet.getRange(1+i, 2, 1, 1).copyTo(sSheet.getRange('D4:E4'));
    //var StockCode = sSheet.getRange(1+i, 2, 1, 1).getText();
    if(StockName == null || StockName == "") break;
    var StockSheet = ss.getSheetByName(StockName);
    if (StockSheet == null) {
      ss.insertSheet(StockName);
      StockSheet = ss.getSheetByName(StockName);
    }
    sSheet.getRange("F1").setValue(i+1 + " " + StockName + " 작업 중 ...");
    sSheet.getRange("D4").setValue(StockName);
    //sSheet.getRange("E4").setValue(StockCode);
    sSheet.getRange("F3").setValue(StockName + " 거래량 데이터 읽는 중 ...");
    readOneStock(tSheet,sSheet);
    sSheet.getRange("F3").setValue("빈 줄 지우는 중 ...");
    DeleteEmptyRows(tSheet,sSheet);
    sSheet.getRange("F3").setValue("거래량 계산 중 ...");
    DoCalcu(tSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 데이터 삽입추가 중 ..." + TotalLineNo);
    InsCopyStockData(tSheet,StockSheet,TotalLineNo);
    DoHeadCopy(StockSheet,sSheet);
    //DoHeaderLine(StockSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 처리완료.");
    i++;
  }while(StockName != null && StockName != "" && i < 20);
  sSheet.getRange("F3").setValue("작업완료!!");    
};
// --------------------------------------------------------------------------
// Read NAVER 증권 - 종목데이터 업데이트 : D4 = 종목 & E4 = 코드
// --------------------------------------------------------------------------
function updateStockOne(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSheet = ss.getSheetByName("ScriptSheet");    // Script & source sheet
  var tSheet = ss.getSheetByName("StockShareTrace");
  if (tSheet == null) {
    ss.insertSheet("StockShareTrace");
    tSheet = ss.getSheetByName("StockShareTrace");
  }
  var i = 0;
  var TotalLineNo = sSheet.getRange("H4").getValue();
  sSheet.getRange("D3").setValue(1);                // Do only One page
  if(TotalLineNo <= 0) TotalLineNo = 21;            // one page all  
  do{
    var StockName = sSheet.getRange("D4").getValue();
    //sSheet.getRange(1+i, 2, 1, 1).copyTo(sSheet.getRange('D4:E4'));
    //var StockCode = sSheet.getRange(1+i, 2, 1, 1).getText();
    if(StockName == null || StockName == "") break;
    var StockSheet = ss.getSheetByName(StockName);
    if (StockSheet == null) {
      ss.insertSheet(StockName);
      StockSheet = ss.getSheetByName(StockName);
    }
    sSheet.getRange("F1").setValue(i+1 + " " + StockName + " 작업 중 ...");
    sSheet.getRange("D4").setValue(StockName);
    //sSheet.getRange("E4").setValue(StockCode);
    sSheet.getRange("F3").setValue(StockName + " 거래량 데이터 읽는 중 ...");
    readOneStock(tSheet,sSheet);
    sSheet.getRange("F3").setValue("빈 줄 지우는 중 ...");
    DeleteEmptyRows(tSheet,sSheet);
    sSheet.getRange("F3").setValue("거래량 계산 중 ...");
    DoCalcu(tSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 데이터 삽입추가 중 ..." + TotalLineNo);
    InsCopyStockData(tSheet,StockSheet,TotalLineNo);
    DoHeadCopy(StockSheet,sSheet);
    //DoHeaderLine(StockSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 처리완료.");
    i++;
  }while(StockName != null && StockName != "" && i <= 1);
  sSheet.getRange("F3").setValue("작업완료!!");    
};

실행루틴 : 종목데이터 HTML 읽고 복사

// --------------------------------------------------------------------------
// StockShareTrace 데이터를 해당 종목 시트에 삽입
// insertRowsAfter(afterPosition, howMany)
// 빈줄 삽입 -> 맨위줄을 아래 마지막 빈줄로 복사 -> 맨 윗줄부터 새데이터 복사 
// --------------------------------------------------------------------------
function InsCopyStockData(_sSheet,_insSheet,insRows){

  var sRange = _sSheet.getDataRange();                 // Get full data range 
  var nRows2add = insRows;
  if(insRows <= 0) nRows2add = sRange.getNumRows()-2;  // 0 이면 모든 줄 추가
  _insSheet.insertRowsAfter(3, nRows2add);             // 헤더제외
  //Utilities.sleep(1000);
  var sourceRange = _insSheet.getRange(3,1, 1, 13);  
  var targetRange = _insSheet.getRange(3+nRows2add,1, 1, 13)
  sourceRange.copyTo(targetRange);
  //Utilities.sleep(1000);
  sourceRange = _sSheet.getRange(3, 1, nRows2add, 13); 
  targetRange = _insSheet.getRange(3, 1,nRows2add, 13); 
  sourceRange.copyTo(targetRange);
  
};
// --------------------------------------------------------------------------
// 증권데이터 읽기
// --------------------------------------------------------------------------
function readOneStock(_tSheet,_sSheet){
  _sSheet.getRange("F2").setValue(" ...");      // 진행상황 표시 셀
  _sSheet.getRange("F3").setValue(" ...");
  _sSheet.getRange("H2").setValue(" ...");      // 진행상황 표시 셀
  var range = _sSheet.getRange("D3");           // D3 - 마지막 페이지 번호
  var EndPageNo = range.getValue();
  var StartPageNo = 1;
  var rowIndex = 1;
  _tSheet.clear({contentsOnly: true});          // Clear the Google Sheet before copy
  _sSheet.getRange("C6").setValue("=importhtml(C5,E2,E3)"); 
  do{
    var Addr1 = _sSheet.getRange("D1");         // URL
    var Addr2 = _sSheet.getRange("D2");         // Stock CODE
    var URLstring = Addr1.getValue() + Addr2.getValue() + "&page=" + StartPageNo;
    _sSheet.getRange("F2").setValue("페이지 = " + StartPageNo); 
    _sSheet.getRange("C5").setValue(URLstring); 
    if(StartPageNo == 1){                      // 첫 페이지인 경우 헤더 줄 복사함
      var sourceRange = _sSheet.getRange("D6:K7"); 
      var targetRange = _tSheet.getRange(_tSheet.getLastRow()+1, 4);
      sourceRange.copyTo(targetRange);
      var Addr3 = _sSheet.getRange("D2");       // 종목코드
      _tSheet.getRange("C1").setValue("종목코드 = " + Addr3.getValue());
      _tSheet.getRange("C2").setValue("--");
    }
    var sRange = _sSheet.getDataRange();        // Get full range of data
    _sSheet.getRange("H2").setValue("읽은 줄수 = " + sRange.getNumRows());
    var sourceRange = _sSheet.getRange("C9:K40");  
    var targetRange = _tSheet.getRange(_tSheet.getLastRow()+1, 3, _sSheet.getLastRow(), 9); // 2017.08.07
    //var targetRange = tSheet.getRange(_tSheet.getLastRow()+1, 3, _sSheet.getLastRow(), sSheet.getLastColumn());
    sourceRange.copyTo(targetRange);
    rowIndex = rowIndex + 30;
    StartPageNo = StartPageNo + 1;
  }while(StartPageNo <= EndPageNo);
};

빈 줄 지우기

// --------------------------------------------------------------------------
function DeleteEmptyRows(_tSheet, _sSheet) {
  var rows = _tSheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[2] == 0 || row[2] == '') {
      _tSheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
      //_sSheet.getRange("F3").setValue(i + " 번째 줄 삭제중 ...");
    }
  }
};
// --------------------------------------------------------------------------
// 불요 '+' 기호삭제, 거래량 계산후 추가, 매입평단가 계산 
// 빈줄 제거후 호출요망 ... 머 확인은 한다...
// https://webapps.stackexchange.com/questions/104157/auto-find-and-replace-in-google-sheets-with-scripts
// https://www.quora.com/How-do-I-replace-text-in-a-spreadsheet-with-Google-Apps-Script
// --------------------------------------------------------------------------
function DoCalcu(_tSheet,_sSheet){
  var rows = _tSheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var price, Korpart, Forpart, korTemp, forTemp;
  for (var i = 2; i <= numRows - 1; i++) {    
    var row = values[i];
    if (row[2] != 0 || row[2] != '') {
      price = row[3];
      row[7] = row[7].toString().replace("+", "");         // 기관 '+' 제거
      row[8] = row[8].toString().replace("+", "");         // 외국인
      Korpart = row[7].toString().replace(",", "");        // 기관 ',' 제거
      Forpart = row[8].toString().replace(",", "");        // 외국인
      _tSheet.getRange(i+1,12).setValue(price * Forpart);  // 외국인 매입가격 * 수량
      _tSheet.getRange(i+1,13).setValue(price * Korpart);  // 기관 매입가격 * 수량
      _tSheet.getRange(i+1,9).setValue(Forpart);           // 외국인 수량
      _tSheet.getRange(i+1,8).setValue(Korpart);           // 기관 매입수량
      //_sSheet.getRange("G3").setValue(i + " 번째 줄 계산중 ... 거래량=" + Forpart);
    }
  }
};

팍스넷 공매도 데이터 읽기

// --------------------------------------------------------------------------
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns
// var range = sheet.getRange(1, 1, 3, 3);
// var count = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getNumColumns();  range: C2:G8
// '=importhtml("http://paxnet.moneta.co.kr/stock/stockIntro/shortSale/shortSaleList.jsp?code=000660&p_curr_page=1","table",1)
// 단순히 종목시트에 공매도 추출 스크립트를 적어 넣음 : 데이터 복사없음
// --------------------------------------------------------------------------
function GetShortSale(_tSheet,_sSheet){     

  _sSheet.getRange("F3").setValue("공매도 데이터 ...");
  var Addr2 = _sSheet.getRange("D2");     // 종목코드
  var Addr1 = _sSheet.getRange("S4");     // URL : paxnet.moneta.co.kr
  var Addr3 = _sSheet.getRange("S5");     // ShortSale Code (공매도 데이터)
  var Addr4 = _sSheet.getRange("S2");     // 형식 :"table"
  var Addr5 = _sSheet.getRange("S3");     // 옵션 인덱스
  var URLstring = Addr1.getValue() + Addr2.getValue() + "&p_curr_page=1";
  _sSheet.getRange("S6").setValue(URLstring); 
  // 아래 두라인은 sSheet에 공매도 데이터를 읽어들인 후에 tSheet로 복사하는 함수임
  //_sSheet.getRange("Q1").setValue("=importhtml(N6,N2,N3)");                    
  //_sSheet.getRange("Q:V").copyTo(_tSheet.getRange("Q1"), {contentsOnly:true}); 
  // 아래 함수는 'StockShareTrace' 시트로 공매도 자료 읽는 식을 넣음
  _tSheet.getRange("Q1").setValue("=importhtml(\"" + URLstring + "\",\"" + Addr4.getValue() + "\"," + Addr5.getValue() + ")"); 
  //var range = sheet.getRange(1, 1, 3, 3);
};

평균 매입가 계산하기

// --------------------------------------------------------------------------
// 평균 구입가격 계산식 복사 (소스시트 -> 임시시트)
// --------------------------------------------------------------------------
function DoHeadCopy(_tSheet,_sSheet){
  _sSheet.getRange("N1:P5").copyTo(_tSheet.getRange("N1:P5"));
};
// --------------------------------------------------------------------------
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns
// var range = sheet.getRange(1, 1, 3, 3);
// --------------------------------------------------------------------------
function DoHeaderLine(_tSheet,_sSheet){

  var Addr1 = _tSheet.getRange("H1");
  var Addr2 = _tSheet.getRange("H2");
  _tSheet.getRange("H1").setValue(Addr1.getValue()+Addr2.getValue());
  _tSheet.getRange("H2").setValue("");
  var Addr1 = _tSheet.getRange("I1");
  var Addr2 = _tSheet.getRange("I2");
  _tSheet.getRange("i1").setValue(Addr1.getValue()+Addr2.getValue());
  _tSheet.getRange("i2").setValue("");
  var Addr1 = _tSheet.getRange("j1");
  var Addr2 = _tSheet.getRange("j2");
  _tSheet.getRange("j1").setValue(Addr2.getValue());
  _tSheet.getRange("j2").setValue("");
  var Addr1 = _tSheet.getRange("k1");
  var Addr2 = _tSheet.getRange("k2");
  _tSheet.getRange("k1").setValue(Addr2.getValue());
  _tSheet.getRange("k2").setValue("");

  // 평균 매입가격 정보창 작성
  _tSheet.getRange("N1").setValue("평균매입");
  _tSheet.getRange("O1").setValue("외국인");
  _tSheet.getRange("P1").setValue("기관");
  _tSheet.getRange("N2").setValue(" 60일");
  _tSheet.getRange("N3").setValue("180일");
  _tSheet.getRange("N4").setValue("360일");
  _tSheet.getRange("N5").setValue("720일");  
  // 외인 평단가 계산 : I / L
  _tSheet.getRange("O2").setValue("=sumif(L3:L63,\">0\", L3:L63)/sumif(L3:L63,\">0\",  I3:I63)");
  _tSheet.getRange("O3").setValue("=sumif(L3:L183,\">0\",L3:L183)/sumif(L3:L183,\">0\",I3:I183)");
  _tSheet.getRange("O4").setValue("=sumif(L3:L363,\">0\",L3:L363)/sumif(L3:L363,\">0\",I3:I363)");
  _tSheet.getRange("O5").setValue("=sumif(L3:L723,\">0\",L3:L723)/sumif(L3:L723,\">0\",I3:I723)");
  // 기관 평단가 계산 : H / M
  _tSheet.getRange("P2").setValue("=sumif(M3:M63,\">0\", M3:M63)/sumif(M3:M63,\">0\",  H3:H63)");
  _tSheet.getRange("P3").setValue("=sumif(M3:M183,\">0\",M3:M183)/sumif(M3:M183,\">0\",H3:H183)");
  _tSheet.getRange("P4").setValue("=sumif(M3:M363,\">0\",M3:M363)/sumif(M3:M363,\">0\",H3:H363)");
  _tSheet.getRange("P5").setValue("=sumif(M3:M723,\">0\",M3:M723)/sumif(M3:M723,\">0\",H3:H723)");
};
// --------------------------------------------------------------------------
// Read NAVER 증권 데이터 - 외국인 보유율 데이터 - ALL : A1:B10 = 종목블록
// var count = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getNumColumns();  range: C2:G8
// --------------------------------------------------------------------------
// 구글스크립트 실행 6분 제한 (하루 6시간 제한도있다)
// --------------------------------------------------------------------------
function readStockDataAll(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSheet = ss.getSheetByName("ScriptSheet");    // Script & source sheet
  var tSheet = ss.getSheetByName("StockShareTrace");
  if (tSheet == null) {
    //activeSpreadsheet.deleteSheet(tSheet);
    ss.insertSheet("StockShareTrace");
    tSheet = ss.getSheetByName("StockShareTrace");
  }
  var i = 0;
  do{
    var StockName = sSheet.getRange(1+i, 1, 1, 1).getValue();
    sSheet.getRange(1+i, 2, 1, 1).copyTo(sSheet.getRange('D4:E4'));
    //var StockCode = sSheet.getRange(1+i, 2, 1, 1).getValue();
    if(StockName == null || StockName == "") break;
    var StockSheet = ss.getSheetByName(StockName);
    if (StockSheet == null) {
      //activeSpreadsheet.deleteSheet(tSheet);
      ss.insertSheet(StockName);
      StockSheet = ss.getSheetByName(StockName);
    }
    sSheet.getRange("F1").setValue(i+1 + " " + StockName + " 작업 중 ...");
    sSheet.getRange("D4").setValue(StockName);
    //sSheet.getRange("E4").setValue(StockCode);
    sSheet.getRange("F3").setValue(StockName + " 거래량 데이터 읽는 중 ...");
    readOneStock(tSheet,sSheet);
    sSheet.getRange("F3").setValue("빈 줄 지우는 중 ...");
    DeleteEmptyRows(tSheet,sSheet);
    sSheet.getRange("F3").setValue("거래량 계산 중 ...");
    DoCalcu(tSheet,sSheet);
    DoHeaderLine(tSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 공매도 데이터 읽는 중 ...");
    GetShortSale(tSheet,sSheet);
    sSheet.getRange("F3").setValue(StockName + " 데이터 복사 중 ...");
    CopySheet(tSheet,StockSheet);
    i++;
  }while(StockName != null && StockName != "" && i < 20);
  sSheet.getRange("F3").setValue("작업완료!!");  
};

시트 데이터 복사

// --------------------------------------------------------------------------
// --------------------------------------------------------------------------
// StockShareTrace = _sSheet,    대상 종목시트 = _tSheet
// --------------------------------------------------------------------------
function CopySheet(_sSheet,_tSheet){           
  var sRange = _sSheet.getDataRange();         // Get full range of data
  var sourceRange = _sSheet.getRange(1, 1, _sSheet.getLastRow(), _sSheet.getLastColumn()); 
  var targetRange = _tSheet.getRange(1, 1, 1, 1); 
  sourceRange.copyTo(targetRange);
};
// --------------------------------------------------------------------------
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns
// var range = sheet.getRange(1, 1, 3, 3);
// https://stackoverflow.com/questions/16090447/paste-special-values-google-apps-script
// --------------------------------------------------------------------------
function CopyRange(source,destination) {
  var sourceSheet = source.getSheet();
  var destSheet = destination.getSheet();
  var sourceData = source.getValues();
  var dest = destSheet.getRange(
    destination.getRow(),        // Top row of destination
    destination.getColumn(),     // left col of destination
    sourceData.length,           // # rows in source
    sourceData[0].length);       // # cols in source (elements in first row)
  dest.setValues(sourceData);
  //source.clear();
};
// --------------------------------------------------------------------------
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns
// http://stackoverflow.com/questions/11058019/delete-a-row-in-google-spreadsheets-if-value-of-cell-in-said-row-is-0-or-blank
// http://stackoverflow.com/questions/36116050/delete-row-in-google-sheets-if-certain-word-is-found-in-cell
/**
 * Deletes rows in the active spreadsheet that contain 0 or
 * a blank valuein column "C". 
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
// Maximum run time of Google script per user or per account
// https://stackoverflow.com/questions/36804160/maximum-run-time-of-google-script-per-user-or-per-account
// https://www.google.co.kr/search?newwindow=1&q=google+script+exceeded+maximum+execution+time&sa=X&ved=0ahUKEwipj6XhrtHVAhVDk5QKHbOFCGMQ1QIIaCgD&biw=1137&bih=929
// "google script exceeded maximum execution time"
// 1 hour toal/day, 6 min/run
// The maximum allowed time for your script to run continuously is 6 minutes. 
// If it exceeds that limit, GAS would throw the "Exceeded maximum execution time" exception.
// --------------------------------------------------------------------------
function DeleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[2] == 0 || row[2] == '') {
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};
// --------------------------------------------------------------------------
function test_moveRange() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var source = sourceSheet.getRange("A7:C10");
  var destination = destSheet.getRange("C4:H2");
  moveRange(source,destination);
};
// --------------------------------------------------------------------------
// https://webapps.stackexchange.com/questions/75967/create-a-function-to-copy-range-values-only-to-a-new-tab-at-specified-interval
// --------------------------------------------------------------------------
function backup(){
  /* Edit the vars below this line for your needs */
  var sourceSheet  = "Sheet2" ;  // Enter the name of the sheet with the source data
  var sourceRange = "A1:B20" ; // Enter the range of the cells with the source data
  var targetSheet = "Sheet3" ; // Enter the name of the target sheet  
  var targetRange = "A2:B21" ; // Enter the range of cells you wish to copy data to. Note this must be same size as source range.
  /* No need to edit below this point */  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sourceSheet);
  var values = sheet.getRange(sourceRange).getValues();
  ss.getSheetByName(targetSheet).getRange(targetRange).setValues(values);
};
// --------------------------------------------------------------------------
//  https://developers.google.com/apps-script/understanding_triggers 
/*
function onEdit(e){
  var copyFromRange = 'Sheet1!A13:A'; // no row for second cell reference
  var copyToRangeStart = 'Sheet2!A3';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}*/
// --------------------------------------------------------------------------
/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
};
// --------------------------------------------------------------------------
// https://stackoverflow.com/questions/26480857/how-do-i-replace-text-in-a-spreadsheet-with-google-apps-script
// --------------------------------------------------------------------------
// http://www.blackcj.com/blog/2015/05/18/cell-number-formatting-with-google-apps-script/
// https://developers.google.com/apps-script/reference/spreadsheet/range#getNumRows()
// '=importhtml("http://finance.naver.com/item/frgn.nhn?code=066575&page=2","table",3)
// https://ctrlq.org/code/20239-copy-google-spreadsheets
// http://stackoverflow.com/questions/34583106/google-script-how-to-move-or-copy-multiple-rows-to-a-target-sheet
// var URLstring = "=importhtml(\"" + Addr1.getValue() + Addr2.getValue() + "&page=" + StartPageNo + "\",\"table\",3)";
//var sheet = ss.getSheets()[0];
//var sSheet = ss.getSheetName();            // source sheet
//var sSheetName = SpreadsheetApp.getActiveSheet().getName();
//var sSheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
//var sSheet = ss.getSheetByName(sSheetName);   // source sheet
// --------------------------------------------------------------------------
// https://support.google.com/docs/answer/3093340?hl=en&vid=0-1300781271247-1502455291332
// --------------------------------------------------------------------------
// IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")
// --------------------------------------------------------------------------
// --------------------------------------------------------------------------
// copy data from Google Sheet A to Google Sheet B
// Credit: @chrislkeller
// https://ctrlq.org/code/20239-copy-google-spreadsheets
//function cloneGoogleSheet(ssA, ssB) {
  //var sss = SpreadsheetApp.openById(ssA); // source doc
  //var ss = sss.getSheetByName('Source spreadsheet');// source sheet
  //var SRange = ss.getDataRange();// Get full range of data
  //var A1Range = SRange.getA1Notation();// get A1 notation identifying the range
  //var SData = SRange.getValues();// get the data values in range
  //var tss = SpreadsheetApp.openById(ssB); // target spreadsheet
  //var ts = tss.getSheetByName('Target Spreadsheet');  // target sheet
  //ts.clear({contentsOnly: true});// Clear the Google Sheet before copy
  //ts.getRange(A1Range).setValues(SData); // set the target range to the values of the source data
//};
// --------------------------------------------------------------------------
// --------------------------------------------------------------------------
/*
  for (var i = 2; i <= numRows - 1; i++) {
    var oldValue="";
    var newValue="";
    var cellsChanged = 0;

    for (var row=0; row<data.length; row++) {
      for (var item=0; item<values[row].length; item++) {
        oldValue = data[row][item];
        newValue = (data[row][item]).toString().replace("+", "");
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[row][item] = newValue;
        }
      }
    }
  }*/
// --------------------------------------------------------------------------