[google spreadsheet & app script] PDF ์ฒจ๋ถ€ํ•˜์—ฌ ๋ฉ”์ผ ๋ณด๋‚ด๊ธฐ
๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
๋””์ง€ํ„ธ ๊ฟ€ํŒ

[google spreadsheet & app script] PDF ์ฒจ๋ถ€ํ•˜์—ฌ ๋ฉ”์ผ ๋ณด๋‚ด๊ธฐ

by ์ผ์ƒ์„ ๊ณต์œ ํ•ฉ๋‹ˆ๋‹ค 2022. 2. 3.

์Šคํ”„๋ ˆ๋“œ์‹œํŠธ์— ์ฃผ๊ธฐ์ ์œผ๋กœ ๋น„์Šทํ•œ ํ˜•ํƒœ์˜ ๋ณด๊ณ ์„œ๋‚˜ ๋ฆฌํฌํŠธ๋ฅผ ์ž‘์„ฑํ•˜์—ฌ ๊ณต์œ ํ•  ์ผ์ด ์ƒ๊ธฐ๊ธฐ ๋งˆ๋ จ์ž…๋‹ˆ๋‹ค. ํ•ด๋‹น ์†Œ์Šค๋Š” ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ๋ฅผ PDF๋กœ ๋ณ€ํ™˜ํ•˜์—ฌ ๋ฉ”์ผ์— ์ฒจ๋ถ€ํŒŒ์ผ๋กœ ์ „๋‹ฌํ•˜๋Š” ๋ฐฉ์‹์ž…๋‹ˆ๋‹ค. ์ถ”๊ฐ€์ ์œผ๋กœ ๋ฉ”์ผ ๋ณธ๋ฌธ์€ html๋กœ ์ž‘์„ฑํ•˜์—ฌ ๋„ฃ์–ด์•ผ ๋ณด๊ธฐ๊ฐ€ ์ข‹์Šต๋‹ˆ๋‹ค. ํ•จ๊ป˜ ์•Œ์•„๊ฐ€ ๋ด…์‹œ๋‹ค.

์•ฑ์Šคํฌ๋ฆฌํŠธ ์ƒ์„ฑ

๊ตฌ๊ธ€ ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ์—์„œ ํ™•์žฅํ”„๋กœ๊ทธ๋žจ > Apps Script ๋ฉ”๋‰ด๋ฅผ ํด๋ฆญํ•ฉ๋‹ˆ๋‹ค.

 

์Šคํ”„๋ ˆ๋“œ์‹œํŠธ๋ฅผ PDF๋กœ ๋ณ€ํ™˜ํ•˜๊ธฐ ์†Œ์Šค

//make pdf
function makeFilePDF(spreadsheet, orderAgencyName, reportDate) {

  var sid = SpreadsheetApp.getActive().getId(); //ํ˜„์žฌ ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ ํŒŒ์ผ ๊ฐ€์ ธ์˜ค๊ธฐ
  var ssid = spreadsheet.getSheetId(); //์Šคํ”„๋ ˆ๋“œ์‹œํŠธ ํŒŒ์ผ์•ˆ์— ์ƒ์„ธ ์‹œํŠธ์•„์ด๋”” ๊ฐ€์ ธ์˜ค๊ธฐ

  var theurl = 'https://docs.google.com/spreadsheets/d/'
    + sid //the file ID
    + '/export?exportFormat=pdf&format=pdf'
    + '&size=A4'
    + '&portrait=false'
    + '&fitw=true'
    + '&scale=4'//1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
    + '&top_margin=0'
    + '&bottom_margin=0.3'
    + '&left_margin=0.1'
    + '&right_margin=0.5'
    + '&sheetnames=true&printtitle=false'
    + '&pagenum=false'
    + '&gridlines=false'
    + '&fzr=true'
    + '&horizontal_alignment=CENTER'  //LEFT/CENTER/RIGHT
    + '&vertical_alignment=MIDDLE'       //TOP/MIDDLE/BOTTOM
    + '&gid=' 
    + ssid

  var token = ScriptApp.getOAuthToken();
  var docurl = UrlFetchApp.fetch(theurl, { headers: { 'Authorization': 'Bearer ' + token } });

  //ํŠน์ • ํด๋” ์ƒ์„ฑํ•ด์„œ ์Œ“๊ธฐ๋กœ ๋ณ€๊ฒฝํ•˜๋ฉด ์ข‹์ง€ ์•Š์„๊นŒ?
  var fileName = orderAgencyName + 'xxx ๋ฆฌํฌํŠธ' + reportDate + '.pdf';


  var fileid = DriveApp.createFile(docurl.getBlob()).setName(fileName).getId();
  var tempfile = DriveApp.getFileById(fileid);

  var thisFile = DriveApp.getFileById(SpreadsheetApp.getActive().getId());
  var folders = thisFile.getParents();
  var folderId;
  while (folders.hasNext()) {
    var folder = folders.next();
    folderId = folder.getId();
    break;
  }
  
  //temp folder๋กœ ์ด๋™
  var daforder = DriveApp.getFolderById(folderId);
  tempfile.moveTo(daforder);

  return tempfile;
}

์œ„ ์†Œ๋ฅด๋ฅผ Code.gs์— ์ ์šฉํ•ฉ๋‹ˆ๋‹ค. 

์†Œ์Šค์— ๋Œ€ํ•ด ๊ฐ„๋žตํžˆ ์„ค๋ช…ํ•˜์ž๋ฉด , ํ˜„์žฌ ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ์˜ ํŒŒ์ผ์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ๊ทธ ํŒŒ์ผ์˜ ํŠน์ • ์‹œํŠธ ๊ณ ์œ  ๋ฒˆํ˜ธ๋„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. ๊ฐ€์ ธ์˜จ ๋งํฌ๋กœ pdf ํŒŒ์ผ์„ ๋งŒ๋“ค๊ธฐ ์œ„ํ•ด ์–ด๋ ค๊ฐ€์ง€ ์„ค์ •์„ ์ ์šฉํ•ด๋ด…๋‹ˆ๋‹ค. ์ด ์„ค์ •๋“ค์€ ๋ณดํ†ต ์ธ์‡„ ๋ฐฉ์‹์— ๋”ฐ๋ฅธ ์„ค์ •๊ฐ’์ž…๋‹ˆ๋‹ค. ๊ฐ€๋กœ/์„ธ๋กœ, ์—ฌ๋ฐฑ, ๋น„์œจ ๋“ฑ์— ๋Œ€ํ•œ ์„ค์ •์ด๋‹ˆ ๊ถ๊ธˆํ•  ๊ฒฝ์šฐ ๋‹ค๋ฅธ ๊ฒ€์ƒ‰์„ ํ†ตํ•ด ํ™•์ธ ๋ถ€ํƒ๋“œ๋ฆฝ๋‹ˆ๋‹ค. ์ด๋ ‡๊ฒŒ ์„ค์ •ํ•œ url์„ OAuthToken ์ •๋ณด๋ฅผ ํ†ตํ•˜์—ฌ ๋ณ€ํ™˜ ํ›„ ํ•ด๋‹น ํŒŒ์ผ์˜ ํŒŒ์ผ๋ช… ๋ฐ ํ™•์žฅ์ž๋ฅผ ์ง€์ •ํ•˜์—ฌ Drive์— ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. 

 

๊ตฌ๊ธ€ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์‚ฌ์šฉํ•˜์—ฌ ๋ฉ”์ผ ๋ณด๋‚ด๊ธฐ ์†Œ์Šค

๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์„ค๋ช…  : https://developers.google.com/apps-script/reference/gmail/gmail-app

  //To : ํŒ€์žฅ๋‹˜ CC: ์ฐธ์กฐ์ž From: ๋ณธ์ธ
  
  GmailApp.sendEmail( toEmail, MailSubject , MailBody, { 
    from: fromMail , 
    cc: CCEmail, 
    htmlBody: MailMessage ,
    name : fromName ,
    attachments: [file.getAs(MimeType.PDF)] });

๋ฉ”์ผ์„ ์ผ๋ฐ˜ ํ…์ŠคํŠธ๋กœ ๋„ฃ๊ฒŒ ๋˜๋ฉด์€ space ๋ฐ ๋‹จ๋ฝ ๊ตฌ๋ถ„์ด ์–ด๋ ต์Šต๋‹ˆ๋‹ค. ๊ทธ๋ ‡๊ธฐ ๋•Œ๋ฌธ์— ๊ผญ html ํ˜•์‹์œผ๋กœ ์ž‘์„ฑํ•˜์—ฌ htmlBody ํ•„๋“œ์— ๋„ฃ์–ด ๋ณด๋ƒ…๋‹ˆ๋‹ค. MimeType๋„ pdf๋กœ ๊ผญ ์ง€์ •ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.  Mail์„ ๊ฐœ์ธ ๋ฉ”์ผ์ด ์•„๋‹Œ ํšŒ์‚ฌ ๊ณต์šฉ ๋ฉ”์ผ ๋ณด๋‚ด๊ณ  ์‹ถ์€ ๊ฒฝ์šฐ์—๋Š” ์•„๋ž˜์™€ ๊ฐ™์ด ์„ค์ • ์กฐ๊ฑด์ด ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค. 

ํ•„์ˆ˜ ์‚ฌํ•ญ : SMTP ์„ค์ • ํ›„ ํƒ€ ์ด๋ฉ”์ผ ์ž๋™ ์—ฐ๊ฒฐ ํ•˜๊ธฐ 

  • gmail ๊ณ„์ • ์žˆ์–ด์•ผํ•จ
  • gmail > ์„ค์ • (๋งํฌ)
  • ์ด๋ฉ”์ผ ์ถ”๊ฐ€ ํ›„ ์ธ์ฆ ํ•˜๊ณ  ์™„๋ฃŒ! (์•„๋ž˜์‚ฌ์ง„ ์ˆœ์ฐจ์ ์œผ๋กœ)

์œ„์™€ ๊ฐ™์ด ์„ค์ •์ด ๋˜์—ˆ์œผ๋ฉด, ์ด์ œ๋Š” ๊ตฌ๊ธ€ ์Šคํ”„๋ ˆ๋“œ ์‹œํŠธ์—์„œ ์Šคํฌ๋ฆฝํŠธ ๋ฒ„ํŠผ์„ ์ถ”๊ฐ€ํ•˜์—ฌ ๋ฉ”์ผ์„ ๋ณด๋‚ด๊ฑฐ๋‚˜, ํŠธ๋ฆฌ๊ฑฐ๋ฅผ ๊ฑธ์–ด์„œ ์ฃผ๊ธฐ์ ์œผ๋กœ ํ•ด๋‹น ์‹œํŠธ์— ๋Œ€ํ•œ ๋ฉ”์ผ์„ ๋ฐœ์†กํ•ฉ๋‹ˆ๋‹ค.

 

ํŠธ๋ฆฌ๊ฑฐ ์„ค์ • ๋ฐ ๋ฐฐํฌ

1. ํŠธ๋ฆฌ๊ฑฐ ์ถ”๊ฐ€ํ•˜๊ธฐ(์‹œ๊ณ„ ๋ชจ์–‘ ํด๋ฆญํ•˜๊ธฐ)

* ํŠธ๋ฆฌ๊ฑฐ ์ถ”๊ฐ€ํ•  ์‹œ, ์ด๋ฒคํŠธ ์†Œ์Šค ์„ ํƒ์„ ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ → ์‹œ๊ฐ„๊ธฐ๋ฐ˜์œผ๋กœ ๋ณ€๊ฒฝ ํ›„ ๋ฆฌํฌํŠธ ๋ฐœํ–‰ ์‹œ๊ฐ„์— ๋งž๊ฒŒ ์ฃผ๊ธฐ๋ฅผ ์„ค์ •ํ•˜์—ฌ ์ €์žฅํ•ฉ๋‹ˆ๋‹ค.

2. ๋ฐฐํฌํ•˜๊ธฐ

Apps script์— ์†Œ์Šค ์ ์šฉ์ด ๋œ ์ƒํƒœ๋กœ ๋ฐฐํฌ ๋ฒ„ํŠผ์„ ๋ˆ„๋ฅด๊ณ  ์„ค๋ช…์„ ๊ธฐ์žฌํ•˜๊ณ  ๋ฐฐํฌ ํ•ฉ๋‹ˆ๋‹ค.

 

 

ํ˜น์‹œ๋‚˜ ๋ถˆ์•ˆํ•˜๋‹ค๋ฉด ๊ผญ 5๋ฒˆ ํ…Œ์ŠคํŠธ ํ•ด๋ณด๊ณ  ์ง„ํ–‰ํ•˜์‹œ๊ธธ ๋ฐ”๋ž๋‹ˆ๋‹ค. ๋ฌธ์˜ ์‚ฌํ•ญ์ด ์žˆ์œผ์‹œ๋‹ค๋ฉด ๋Œ“๊ธ€๋กœ ๋‚จ๊ฒจ์ฃผ์‹œ๊ฑฐ๋‚˜ ๋ณ„๋„๋กœ ํ‹ฐ์Šคํ† ๋ฆฌ ํ•˜๋‹จ์— ์žˆ๋Š” ์นด์นด์˜คํ†ก ์ฑ„๋„์— ์ ‘์†ํ•˜์—ฌ ์ฑ„ํŒ…์ฃผ์‹œ๋ฉด ๋‹ต๋ณ€๋“œ๋ฆฌ๊ฒ ์Šต๋‹ˆ๋‹ค.

๋Œ“๊ธ€