[script] Google 스프레드시트 스크립트 정리

 

스프레드시트로 매크로 작업할 일이 있어서 작업하다가 구글 스크립트 api는 처음이라 기록해 둡니다.

전체시트

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
특정 시트

var DB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DB');
현재 시트

var sheet = SpreadsheetApp.getActive();
​

다른파일

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/" + docName + "/edit");
​

데이터 마지막 행

eRow = DB.getDataRange().getLastRow();
행, 열 바꾸어 붙여넣기

sheets[i].getRange(1, 2, sheets[i].getLastRow(), sheets[i].getLastColumn()).copyTo(rng, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
메세지 띄우기

Browser.msgBox("완료")
수식 넣기

  sheet.getRange("'발급'!$F$6:$F$"+endRow).setFormulaR1C1('=R[0]C[-2]*R[0]C[-1]');
  sheet.getRange("'발급'!$G$6:$G$"+endRow).setFormulaR1C1('=R[0]C[-1]*0.1');
배열에 값 담기

  for (i = 1; i <= gr.getNumRows(); i++) {
    //Browser.msgBox(gr.getNumRows());
      if(gr.getCell(i,4).getValue()>0){
        //gr.getCell(i,4).setNote('Last modified: ' + new Date())
        row=[];    // 새 아이템 생성
        row.push(endRow + k++);    // 데이터 번호
        row.push(s + x);    // 전체 발주번호
        row.push(sheet.getRange("'발급'!$D$3").getValue());
        row.push(gr.getCell(i,1).getValue());    // 상품번호
        row.push(gr.getCell(i,2).getValue());    // 상품명
        row.push(gr.getCell(i,4).getValue());    // 수량
        row.push(gr.getCell(i,3).getValue());    // 단가
        row.push(gr.getCell(i,5).getValue());    // 금액
        row.push(gr.getCell(i,5).getValue() * 0.1);    // 세액
        row.push(gr.getCell(i,7).getValue());    // 비고
        row.push(Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd"));    // 날짜
        items.push(row);    // 아이템 추가
      }
  }
배열값을 셀에 넣기

  for (i = 0, l = items.length; i < l; i += 1) {
      sheet.getRange("A" + (13 + i)).setValue(i + 1);
      sheet.getRange("D" + (13 + i)).setValue(items[i][4]);
      sheet.getRange("M" + (13 + i)).setValue(items[i][5]);
      sheet.getRange("P" + (13 + i)).setValue(items[i][6]);
      sheet.getRange("AD" + (13 + i)).setValue(items[i][9]);   
  }
특정시트 PDF로 메일 보내기

function emailSpreadsheetAsPDF(email, subject, body, gid) {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  const docName = "1-VanuGkorxUy2aCh0uuIPozHWlf6U3BX420T4tsqyvU";
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/" + docName + "/edit");

  // We are going to get the email address from the cell "B7" from the "Invoice" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  //const value = "program1472@naver.com";    //ss.getSheetByName("Invoice").getRange("B7").getValue();
  //const email = value.toString();

  //// Subject of the email message
  //const subject = 'Your Invoice';

  //// Email Text. You can add HTML code here - see ctrlq.org/html-mail
  // const body = "Sent via Generate Invoice from Google Form and print/email it";
  
  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = "https://docs.google.com/spreadsheets/d/" + docName + "/export?";
  //const gid = "1547651416";
  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=false' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=' + gid; // the sheet's Id. Change it to your sheet ID.
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid  number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  // Generate the PDF file
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: subject + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }],
      noReply: true,
      name: "올투그린",    // 회사명
      replyTo: "samatg@aserpacific.com"    // 답장받을 이메일
    });

  // Save the PDF to Drive. The name of the PDF is going to be the name of the Company (cell B5)
  const nameFile = ss.getSheetByName("발주서").getRange("C1").getValue().toString() +".pdf"
  DriveApp.createFile(response.setName(nameFile));
}
​

지정한 셀 값 지우기

sheet.getRange("A13:T27").clearContent();
메모 삽입

sheet.getCell(i,4).setNote('Last modified: ' + new Date())
Today

s = "BTG-" + Utilities.formatDate(new Date(), "GMT+1", "yyyyMMdd-");
​

이정도만 다룰줄 알면 왠만한 매크로 작업은 가능할 것 같습니다.

​

엑셀보다 불편한 부분은 

1. 사용자 폼 만들기가 쉽지 않다는 것입니다.

2. 디버깅이 쉽지 않다

​

좋은점 그외 너무너무 좋습니다.

우선 query 함수부터 막강한 함수들이 구글 스프레드시트로 전향해야 할 이유가 될 것 같습니다.