[Apps Script]구글 스프레드 시트에 외부 데이터 넣기 - 2

지난 글에 이어서 javascript에서 ajax로 spread sheet에 데이터를 넣는 방법입니다. 

제일 먼저  javascript에서의 요청을 받을 함수와 url을 apps script에서 설정해주어야 합니다. 개편된 편집기 기준입니다.

개편 된 편집기에서 파일 생성

스크립트 파일을 하나 생성해주세요. 이름은 크게 상관없지만 저는 requestHandler로 설정했습니다.

다음 사진 처럼 myFunction이 있는 스크립트가 생성되었습니다. 이제 외부에서 데이터를 받아 시트를 수정할 수 있게 몇가지 함수를 추가해 줄겁니다.

스크립트 생성시 초기 함수

우선 myFunction을 포함한 모든 내용을 지워주세요. 그리고 아래 내용을 복사해 붙여넣으시면 됩니다.

var SCRIPT_PROP = PropertiesService.getScriptProperties();

function doPost(e) {
	return requestHandler(e);
}

function doGet(e) {

	return requestHandler(e);
}

function requestHandler(e){
    //데이터 파싱
    var data = JSON.parse(e.parameter.aa);
    
    //스프레드 시트 객체 지정
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    
    var sheet = doc.getSheets()[0]; // 첫번째 시트 선택
    // var sheet = doc.getSheetByName({시트이름}); 시트명으로 선택
    
    var nextRow = sheet.getLastRow()+1; //데이터가 있는 마지막행의 다음행
    
    //2차원 배열 기준값 설정
    // 시작행 시작열 행길이 열길이
    sheet.getRange(nextRow,1,data.length,data[0].length).setValues(data);

}

function setup() {
	var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key",doc.getId());
}

그리고 상단 메뉴에서 setup함수를 실행시켜야 합니다. 권한 요청창이 뜬다면 권한을 허용하고 난 후 다시 함수를 실행해주시면 되겠습니다.

함수 실행이 완료되었다면. 우측 상단에 배포 > 배포를 진행해주세요. 유형은 웹 앱으로 설정해주시면 됩니다.

설정이 완료되었을 때 나오는 url을 따로 저장해 두고서 javascript로 넘어가겠습니다.

html에 버튼을 하나 생성해주시고 js파일이나 스크립트 영역에 다음 내용을 추가합니다.

/*############구글 테스트################*/

$(document).on('click','#googleTest',function(){
	var data = new Array();
	
	for(var i=0; i<10; i++){
		var array = [i+1+"번째","passersbyrabbit"];
		
		data.push(array);
	}
	
	var jsonData = JSON.stringify(data);
	googleWrite(jsonData);
});


function googleWrite(data){
	  $.ajax({
      		// 앱 웹으로 배포시 저장했던 url
		  url : "https://script.google.com/macros/s/AKfycbzOj7lAxWbBBKNUdumu6R98M6_rSwbOOc4bdqxakmq8jLwjiHY/exec",
		  data : {aa:data},  
		  type : "POST",
		  dataType : 'jsonp',
	  });
}

/*############구글 테스트################*/

 

appssript의 getrange함수가 이차원 배열을 받으므로 편의성을 위하여 이차원 배열의 데이터를 넣어주도록 하겠습니다. 객체배열이나 일반 객체도 받을 수 있지만 별도의 row별 가공이 필요하므로 그 부분은 개인적으로 처리해주시면 됩니다. 추가적으로 로컬 환경에서 테스트를 진행했기 때문에 크로스 도메인 문제를 방지하기 위하여 jsonp타입으로 데이터를 전송했습니다.

스크립트도 작성했다면 html에서 만들어 놓은 버튼을 클릭해 봅시다. 다음과 같이 데이터가 들어왔으면 성공입니다.

결과

 

하지만 이 방법의 경우에는 보낼 수 있는 데이터의 양이 그리 많지 않기 때문에 10개 미만의 행 데이터를 전송할 때 사용하시거나 파라미터만 보내 apps script에서 url을 호출하여 사용하는 것을 추천드립니다.

추가적으로 doGet이나 doPost로 외부 데이터를 받았을 경우 로그가 찍히지 않는데 임의적으로 시트에 로그를 찍는 방법입니다.

시트 하단부에서 시트를 하나 추가해주세요. 이름은 log로 설정합니다.

apps script의 doPost/doGet함수가 있는 스크립트에 다음 함수를 추가해주세요.

function myLog(str){
   var doc = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = doc.getSheetByName("log");
  sheet.appendRow([new Date(),str]);
  
}

requestHandler함수나 doPost 및 doGet 함수에서 받은 파라미터를 JSON.stringify함수를 사용하여 위의 함수에 넣어주도록 추가해주면 외부 데이터를 받았을 때도 로그를 확인할 수 있습니다.

//ex)
function doGet(e){
	myLog(e);
    myLog(e.parameters);
    myLog(JSON.stringify(e.parameters));
    myLog(JSON.stringify(e.parameters.aa));
}

 

이상으로 google apps script를 활용한 외부 데이터 sheet에 추가시키는 방법에 대한 설명을 마무리 짓도록 하겠습니다.