[Google] Apps script를 DB처럼 사용하기(doPost, doGet)

회사에서 여러 업무를 하다 보면 자동화가 필요한 부분들이 있습니다. 현재 진행하는 업무에서 필요한 부분일 수도 있고 아닐 수도 있지만, 저의 경우에는 마케팅팀에서 특정 자료를 활용할 수 있도록 요청을 받았습니다. 해당 자료는 현재 사용 중인 Admin에서 데이터를 확인하는 것이 아닌 스프레드 시트에서 데이터를 확인하고 싶다는 요청을 받았습니다.

 

현재 Admin은 CRM처럼 사용할 수 있는 부분은 제한적이다 보니 스프레드 시트를 CRM처럼 사용하기를 원하는 부분이라고 생각했습니다. 그래서, 여러가지 방안을 생각하다 같이 일하는 프론트 분께서 Apps Script를 DB처럼 사용할 수 있다는 정보를 주셨고, Apps Script로 마케팅팀의 요구사항을 해결하였습니다.

해당 글은 해결하면서 겪었던 부분들과 어떤식으로 해결하는지를 작성한 글입니다.

 

---

1. doGet 함수

- apps script를 이용시 시트에 있는 값을 읽는 방법입니다.

- doGet를 선언해야지 사용할 수 있으며, 해당 함수는 임의의 다른 함수로 수정해서 사용이 불가합니다.

 

1-1. 스프레드 시트에서  apps script 클릭

 

1-2. 초기 화면 확인 후 코드를 작성

 

 

1-3. doGet 함수 작성

- 제가 작성한 코드는 시트의 값을 읽는 부분이 아닌 url을 입력하면 hello World를 보여주는 코드입니다.

function doGet(e) {
  return HtmlService.createHtmlOutput('<h5>hello World</h5>');
}

 

1-4. 우측 상단 배포 버튼 클릭 및 새배포 선택

 

1-5. 톱니 바퀴 웹 앱 선택

1-6. 구성에 내용 작성

- 설명은 원하시는 텍스트를 작성하시면 됩니다.

- 웹앱에서 다음 사용자 인증 정보로 실행은 현재 스프레드 시트를 개설한 사람의 이메일로 지정해야 됩니다.

- 액세스 권한이 있는 사용자에는 모든 사용자로 지정해 주시면 됩니다

1-7. 배포를 하면 url을 얻을 수 있습니다.

- 해당 url을 복사 후 주소창에 붙여넣으면 hello World를 확인할 수 있습니다.

 

 

2. doPost 함수

- body로 데이터를 전달하여 시트에 값을 저장할 수 있도록 합니다.

- doGet과 동일하게 해당 함수명을 변경해서는 사용할 수 없습니다.

 

2-1. 1-1과 1-2를 동일하게 진행하시면 됩니다. 만약 동일한 시트에서 진행한다면 doPost 함수만 생성해 주시면 됩니다.

 

2-2. 인수로 받은 e값을 JSON.parse를 이용하여 할당합니다.

let spreadsheetApp = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/{시트주소}/edit");

function doPost(e) {
  const {path, ...data} = JSON.parse(e.postData.contents)

  if(path =="test"){
    return test(data);
  }
}

- spreadsheetApp에서 시트 주소라고 입력한 부분은 스프래드 시트 url 주소입니다.

   - url에서 d/ 이후부터 /edit 전까지가 제가 작성하려고 하는 시트의 주소입니다. 

- 제가 path로 분리한 이유는 하나의 시트에 Post 요청으로 저장할 데이터가 여러 개 일 수 있다는 가정을 하였습니다.

- 이후에 다른 시트에 저장이 필요하면 path로 분리를 하면 될 것이라고 생각하였습니다.

 

2-3. test 함수 작성

- test 함수에는 구현하려는 로직을 작성하였습니다.

let userSheet = spreadsheetApp.getSheetByName("유저_정보"); 

function test(userData){
  const createdAt = new Date();

  const {userId, userName, userPhoneNumber} = userData;
  try{
    userSheet.appendRow([createdAt,userId,userName,userPhoneNumber]);
    return ContentService.createTextOutput(`User added successfully`)
      .setMimeType(ContentService.MimeType.TEXT);
//	return ContentService  // JSON 형태
//          .createTextOutput(JSON.stringify({"result":"success", "id": userId}))
//          .setMimeType(ContentService.MimeType.JSON);
  }catch(e){
    return ContentService.createTextOutput(`Error:${e}`)
      .setMimeType(ContentService.MimeType.TEXT);
  }
}

- spreadsheetApp.getSheetByName("시트이름")

   - 저장을 원하는 시트의 이름을 입력합니다.

- userSheet.appendRow([]) 가 시트에 값을 입력합니다.

- ContentService는 response를 전달합니다.(JSON 형태로도 전달이 가능합니다.)

 

저의 경우는 원하는 값을 찾아서 update까지 하는 로직을 구현하였습니다.

/***필요 없으시다면 넘어가셔도 됩니다.***/ 

2-4. 원하는 값을 찾아서 update

let userSheet = spreadsheetApp.getSheetByName("유저_정보"); 

function findUserByPhoneNumber(phoneNumber){
  
  const range = userSheet.getRange("C:C"); // C 열로 범위 지정
  const values = range.getValues(); // C열의 모든 값 불러오기

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] == phoneNumber) {
      return i + 1; // 해당 전화번호의 행 번호 반환
    }
  }
  return -1;
}


function test(userData){
  const createdAt =  new Date();

  const {userId, userName, userPhoneNumber} = userData;
  try{
  	const row = findUserByPhoneNumber(userPhoneNumber);
    if(row<0){
    	userSheet.appendRow([createdAt,userId,userName,userPhoneNumber]);
    }else{
    	userSheet.getRange(row,1,1,4).setValues([createdAt,userId,userName,userPhoneNumber]);
    }
    return ContentService.createTextOutput(`User added successfully`)
      .setMimeType(ContentService.MimeType.TEXT);
  }catch(e){
    return ContentService.createTextOutput(`Error:${e}`)
      .setMimeType(ContentService.MimeType.TEXT);
  }
}

- 동일한 전화번호가 있다면 row를 전달하고 아니라면 -1을 전달

- row를 전달했다면, getRange(시작 행, 몇개의 행, 몇 번 컬럼 부터, 몇 개 컬럼 까지)로 지정해 줍니다.

- getRange 지정후 setValues로 값을 저장해줍니다.

 

전화번호로 create, update를 할 수 있는 로직을 작성하였습니다.

/***전체코드***/

let spreadsheetApp = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/{시트주소}/edit");

function doPost(e) {
  const {path, ...data} = JSON.parse(e.postData.contents)

  if(path =="test"){
    return test(data);
  }
}

let userSheet = spreadsheetApp.getSheetByName("유저_정보"); 

function findUserByPhoneNumber(phoneNumber){
  
  const range = userSheet.getRange("C:C"); // C 열로 범위 지정
  const values = range.getValues(); // C열의 모든 값 불러오기

  for (let i = 0; i < values.length; i++) {
    if (values[i][0] == phoneNumber) {
      return i + 1; // 해당 전화번호의 행 번호 반환
    }
  }
  return -1;
}


function test(userData){
  const createdAt =  new Date();

  const {userId, userName, userPhoneNumber} = userData;
  try{
  	const row = findUserByPhoneNumber(userPhoneNumber);
    if(row<0){
    	userSheet.appendRow([createdAt,userId,userName,userPhoneNumber]);
    }else{
    	userSheet.getRange(row,1,1,4).setValues([createdAt,userId,userName,userPhoneNumber]);
    }
    return ContentService.createTextOutput(`User added successfully`)
      .setMimeType(ContentService.MimeType.TEXT);
  }catch(e){
    return ContentService.createTextOutput(`Error:${e}`)
      .setMimeType(ContentService.MimeType.TEXT);
  }
}

 

---

스크립트를 작성하면서 느낀 점은 기본 폴더에 doPost, doGet을 작성하고 파일별로 나눠놓은 것이 괜찮겠다고 생각하였습니다.

이는 하나의 파일에 여러 가지 함수가 함께 있다면 가독성이 떨어진다고 생각하였습니다.

제가 생각하는 예시 구조입니다.

- 좌측을 보면 Code에는 공통함수를 최대한 작성

- 나머지 Test, FormattedDate는 기능을 정의한 함수로 분리하는 게 좋을 거 같다는 생각을 하였습니다.

 

또 script 작성 시 주의사항은 js에서 사용하는 비교연산자가 다르다는 것입니다.

js - ===, !== 사용

gs - ===, !== 사용 X ( ==, != 로 사용가능)

이점 주의하시면서 스크립트 작성하실 수 이도록 해주시면 좋을 거 같습니다.

 

제일 중요한 부분이 배포를 하지 않으면 테스트도 해볼 수 없습니다. 코드를 수정하고 테스트하시고 싶다면 배포를 꼭 하시는 것을 추천드립니다. - 제가 배포를 하지 않아서 다음 스크립트 함수(doGet)를 찾을 수 없습니다.라는 워딩을 계속 봤습니다.......(버전도 버전관리에서 새배포로해서 배포를 진행하셔야 됩니다!!!!!!)

 

 

 

 

특정 문제나 궁금한 점을 검색하며 찾아보고 작성한 글입니다. 혹시라도 부정확한 정보를 전달드릴 수 있습니다. 틀린 부분이 있으면 댓글을 남겨주세요

 

https://slashpage.com/taesangeom/dk58wg2ejwvjvmnqevxz

doGet 등 Apps Script 함수 - Programming - taesangeom

 

https://www.clien.net/service/board/cm_app/18400096

구글스프레드시트를 웹에서 호출하는 방법? : 클리앙

https://blog.naver.com/goglkms/221323220030

구글 앱 스크립트 - DoGet(e), 구글 시트 내용 추가입력

https://kutar37.tistory.com/entry/%EA%B5%AC%EA%B8%80-%EC%8A%A4%ED%94%84%EB%A0%88%EB%93%9C%EC%8B%9C%ED%8A%B8-API-%ED%99%9C%EC%9A%A9%ED%95%98%EA%B8%B0-INSERT

구글 스프레드시트 API 활용하기 : INSERT

https://rollingpig.tistory.com/3

스프레드시트 스크립트편집기를 이용하여 데이터 받아오기 - follow(3)

https://yermi.tistory.com/entry/GAS-%EA%B5%AC%EA%B8%80-%EC%95%B1-%EC%8A%A4%ED%81%AC%EB%A6%BD%ED%8A%B8-doGet%EA%B3%BC-doPost-%EC%82%AC%EC%9A%A9-%EB%B0%A9%EB%B2%95-%EA%B5%AC%EA%B8%80-%EC%95%B1-%EC%8A%A4%ED%81%AC%EB%A6%BD%ED%8A%B8%EB%A1%9C-%EC%99%B8%EB%B6%80-%EC%9A%94%EC%B2%AD-%EC%B2%98%EB%A6%AC%ED%95%98%EA%B8%B0

[GAS] 구글 앱 스크립트 doGet과 doPost 사용 방법 [구글 앱 스크립트로 외부 요청 처리하기]

https://blog.naver.com/cyanine/221581264894

Google Apps Script를 사용해 텔레그램 봇 만들기 -5-