[google apps script]구글스프레드시트를 내 PC 특정 폴더에 주기적으로 엑셀파일로 다운 받기

구글스프레드시트 파일을 본인 PC 특정 폴더에 저장하고 싶다는 문의가 들어와 열심히 생각해봤지만 어려워 약간 편법으로 만들어봤다. 조건이 원래는 구글스프레드시트의 작성된 행까지만 나오게 해달라고 하였지만 우선 내 선에서는 불가능하였다.

아래는 다운받는 스프레드시트 파일의 명을 실행되는 날짜를 붙여서 다르게 하고, 해당 파일을 특정 폴더로 이동시키는 방법이다.

1. google apps script 소스 (code.js)

function doGet(e){
  return createDataUrl();
}

function createDataUrl() {
  var type = "xlsx";
  var date = Utilities.formatDate(new Date(),"GMT+0900","yyyy-MM-dd HH:mm:ss");

  date = date.replaceAll("-","");
  date = date.substring(0,9);


  const mimeTypes = { xlsx: MimeType.MICROSOFT_EXCEL};
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = `https://docs.google.com/spreadsheets/export?exportFormat=${type}&id=스프레드시트ID입력`;
  var blob = UrlFetchApp.fetch(url, {
    headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
  }).getBlob();

  var html = HtmlService.createTemplate('<body></body><script>const a=document.createElement("a");document.body.appendChild(a),a.download="<?= filename ?>",a.href="<?= data ?>",a.click();</script>');
  html.data = "data:${mimeTypes[type]};base64," + Utilities.base64Encode(blob.getBytes());
  html.filename = `${ss.getName() + String(date)}.${type}`;

  return html.evaluate();
}

만약 위 소스에서 파일명을 동일하게 항상 다운 받고 싶을 경우 date 부분을 다 지원주고 파일이름 설정한 부분 소스를 아래와 같이 바꿔주면 된다.

html.filename = "동일한이름.xlsx";

2. html 파일 만들기 (Index.html)

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    
  </body>
</html>

잘 실행되는 액션을 하기 위해서 임의로 만들어주는 html 정도로 해석했다. (웹앱에서 실행)

3. 웹 앱으로 배포 하기

오른쪽 상단에 있는 배포 > 새 배포 버튼을 누른다.

그러면 아래와 같이 웹 앱을 액세스하는 사용자 / 구글 계정이 있는 모든 사용자를 선택하고 배포 버튼을 클릭하면 아래와 같은 URL이 생성된다. 생성된 URL은 복사하여 아래 스크립트에 사용한다.

4. 윈도우 PC에 파일 이동하는 스크립트 파일 만들어 스케줄 예약하기

사전에 아래 링크를 보면 .bat 파일 만드는 방법 및 작업 스케줄러 설정하는 방법이 있다. 위에 트리거 실행 시간과 맞춰 이후 시간대로 설정해주면 된다.

[Windows] 정해진 시간에 특정파일 실행 후 단축키 실행 , 매크로 사용 꿀팁

이번 포스팅은 정해진 시간에 특정파일 실행 후 단축키도 자동으로 입력하는 매크로 사용을 더 효율적으로 할 수 있는 꿀팁을 공유하고자 합니다. 스크립트 작성 방법과 윈도우에서 실행하는

odaily.tistory.com

배치 파일 소스 (.bat)

@echo off

::웹페이지 실행
 start "위에서 복사한 웹 앱 URL"

:: 파일 다운에 필요한 시간으로 설정해주는 것이 좋음 (넉넉하게)
timeout 10

::파일명에 날짜구분을 위한
SET yyyymmdd=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%
REM SET yyyymmdd=%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%

:: FILENAME
SET filePath=C:\excelFileTest%yyyymmdd%.xlsx

echo %filePath% 파일 이동

:: file 폴더 이동 (앞이 다운받은 폴더, 뒤가 옮길 폴더)
move %filePath% C:\test


::KEY 종료, pause 일 경우 도스 창이 남으므로 정상적으로 실행이 되는게 확인이 된다면 exit
pause

구글 앱 스크립트를 사용하는 만큼, 꼭 인터넷 기본 앱 설정은 chrome으로 설정되어 있어야 하며 본인 id가 로그인 되어있어야 한다. 추가적으로 파일 이동이 된 후 띄어진 웹페이지를 종료 해야 한다면 pause 전에 아래 코드를 추가한다.

단축키 보내기 (.bat)

::파일이 저장될 위치와 이름을 변수로 저장했습니다::파일 이름은 snedkey.vbs입니다.
set file="%temp%\sendkey.vbs"

::vbs 파일을 만듭니다.
(echo Set objShell = WScript.CreateObject("WScript.Shell"^)
echo objShell.SendKeys "%{F4}") > %file%

::만든 vbs 파일을 실행시킵니다.
::vbs 파일은 wscript로 실행합니다.
wscript %file% | more

::만들었던 vbs파일을 삭제 합니다.
del /q %file%

결과

이렇게 할 경우 파일이 이동되는 것을 확인 할 수 있다. 6월 초에 문의 들어왔는데 나름 쉽게 해보려했는데 원하던 방향대로 안되서 너무 슬펐던 작업이다. 그래도 이렇게 하면 내가 주기적으로 파일을 다운받을 수 있다는 것을 알게 된 시간이었다.

저작자표시 비영리 변경금지

'디지털 꿀팁' 카테고리의 다른 글

[Mac] 터미널에서 finder 열기 , 맥 사용법 (5)	2022.07.04
[Windows] 정해진 시간에 특정파일 실행 후 단축키 실행 , 매크로 사용 꿀팁 (1)	2022.06.28
[엑셀] 수식 입력하고 서식 없이 복사하기 (0)	2022.06.27

🌷일상을 공유합니다

[google apps script]구글스프레드시트를 내 PC 특정 폴더에 주기적으로 엑셀파일로 다운 받기

1. google apps script 소스 (code.js)

2. html 파일 만들기 (Index.html)

3. 웹 앱으로 배포 하기

4. 윈도우 PC에 파일 이동하는 스크립트 파일 만들어 스케줄 예약하기

결과

'디지털 꿀팁' 카테고리의 다른 글

댓글

티스토리툴바

[google apps script]구글스프레드시트를 내 PC 특정 폴더에 주기적으로 엑셀파일로 다운 받기

1. google apps script 소스 (code.js)

2.  html 파일 만들기 (Index.html)

3. 웹 앱으로 배포 하기

4. 윈도우 PC에 파일 이동하는 스크립트 파일 만들어 스케줄 예약하기

결과

'디지털 꿀팁' 카테고리의 다른 글

관련글

댓글

티스토리툴바

2. html 파일 만들기 (Index.html)