Google App Script + Sheet

 

Google Forms → Sheets

설문 응답시 → Sheets 내용으로 추가

AppScript

javascript 유사한 script code

interpreter 방식

Source Code

Trigger

• 이벤트 기반
  
  
• 시간 기반
  
  

실행 이력

Source Code

• main.gs

function addUser() {
var userList = getAllRowData();
var user = getMaxRowData();  
addNewStudent(user);

const url = 'http://test.com/api/ed/user' // POST URL 
var options = {
'method' : 'post',
'contentType': 'application/json',
'payload' : JSON.stringify(user)
};

UrlFetchApp.fetch(url, options)
.then(() => {
  this.$notify({
    title: 'Success',
    message: 'Created Successfully',
    type: 'success',
    duration: 2000
  })
})
}

• spread.gs

  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/sheets_key/edit");

  function getAllRowData(){ 
    const listSheet = ss.getSheetByName("student list");
    var rows = listSheet.getRange(`A2:S${listSheet.getMaxRows()}`).getValues(); 
    var result = [];
    for (var i = 0; i < rows.length; i++){
      var row = rows[i];
      var userData = new UserData(row, i);
      if (row[0] != '')  result.push(userData);
    }
    return JSON.stringify(result);
  }

  function getMaxRowData(){
    const listSheet = ss.getSheetByName("student list");
    var maxRows = 0;
    var result = [];
    var rows = listSheet.getRange(`A2:S${listSheet.getMaxRows()}`).getValues(); 

    for (var i = 0; i < rows.length; i++){
      var row = rows[i];
      if (row[0] == '') {   
        maxRows = i - 1;
        row = rows[maxRows];  
        // result.push(new UserData(row, maxRows));
        var rowNum = maxRows + 2;
        result = new UserData(row, rowNum); 
        break;
      } 
    }
    return result;
  }

  function addNewStudent(user){
    user = getMaxRowData()
    var alertEmail = GmailApp.sendEmail(
      `abigailmaureen1993@gmail.com`, 
      "New Student Alert", 
      `name : ${user.engname} \n email : ${user.email} \n skype : ${user.skype_id} \n` );

    // sendMsgAskingExt(user);     
    addNewStudentFomular(user);
  }

  function addNewStudentFomular(user){ 
    const listSheet = ss.getSheetByName("student list");

    listSheet.getRange(`O${user.rowNum}`).setFormula(`=WORKDAY.INTL(N${user.rowNum},19)`);
    listSheet.getRange(`Q${user.rowNum}`).setFormula(`=IF(L${user.rowNum}="Y",VLOOKUP(C${user.rowNum},$U$2:$V$4,2,FALSE),0)`); 

  }

  // function addNewStudentSheet(user){  
  //   var userData = JSON.parse(user)[0];
  //   var newSheet = ss.insertSheet();
  //   newSheet.setName(`${userData.engname} class sheet`);

  //   // var sourceRange = ss.getSheetByName("student sheet").getRange("A1:K25");
  //   // var targetRange = newSheet.getRange("A1:K25");
  //   // sourceRange.copyTo(targetRange);
  // }

  function generateFeeSheetMonthly(){  
    const feeSheet = ss.getSheetByName("fee history");
    var maxRows = 0;
    var result = [];
    var userList = [];
    userList = getAllRowData();

    // var formatDate = Utilities.formatDate(new Date(user.start_date), "GMT+9", "yyyy-MM-dd"); 
    var colMonth = new Date().getMonth() * 4;

    userList.forEach(user => {
      feeSheet.getRange(`${columnToLetter(colMonth + 1)}${user.rowNum}`).setValue(user.kakao_id);
      feeSheet.getRange(`${columnToLetter(colMonth + 2)}${user.rowNum}`).setValue(user.start_date);
      feeSheet.getRange(`${columnToLetter(colMonth + 3)}${user.rowNum}`).setValue(user.fee);
    })
  }

• pdf.gs

  function onOpen() {
    var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
    SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);  
  }

  function generatePdf(user){
    // DocumentApp.getActiveDocument();
     var attendanceSheet = ss.getSheetByName("attendance sheet");

    attendanceSheet.getRange(`C5`).setValue(user.teacher);
    attendanceSheet.getRange(`G5`).setValue(user.kakao_id);
    attendanceSheet.getRange(`C6`).setValue(user.course); 
    var formatDate = Utilities.formatDate(new Date(user.start_date), "GMT+9", "yyyy-MM-dd"); 
    attendanceSheet.getRange(`G6`).setValue(formatDate); 
    SpreadsheetApp.flush();

    // 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/xxxxxxxxxxxxxxxxxxxxxx/export?';

      const exportOptions =
        'exportFormat=pdf&format=pdf' + // export as pdf
        '&size=A4' + // 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=83536169'; // 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();

      // Save the PDF to Drive. 
      // const nameFile = subject +".pdf"
      // DriveApp.createFile(response.setName(nameFile));
    return response
  }

  function test(){
    var userList = [];
    userList = JSON.parse(getAllRowData());

    // user = getMaxRowData();
    // userList.push(user);

    userList.forEach(user => {
      user.pdf = generatePdf(user);
      var emailResponse = sendEmail(user);
      // Utilities.sleep(2000);
    });
  }

• email.gs

function sendEmail(user) {

   // Subject of the email message
   const subject = user.kakao_id + '_attendance sheet';

   // Email Text. You can add HTML code here - see ctrlq.org/html-mail
   const body = " 안녕하세요,   감사합니다. \n Thanks";

   // Send the PDF file as an attachement 
   var result = GmailApp.sendEmail(user.email, subject, body, {
     htmlBody: body,
     attachments: [{
           fileName: subject + ".pdf",
           content: user.pdf.getBytes(),
           mimeType: "application/pdf"
       }]
   });

   var result2 = GmailApp.sendEmail(`en9door@gmail.com`,subject,body,{
       htmlBody: body,
       attachments: [{
       fileName: subject+".pdf",
       content: user.pdf.getBytes(),
       mimeType: "application/pdf"
     }]
   });
   return result;
}

• userData.gs

  class UserData {
    constructor(row, rowNum) {
      this.rowNum = rowNum
      this.id = undefined
      this.timestamp = new Date(row[0])
      this.email =  row[1]
      this.course =  row[2]
      this.starttime =  row[3]
      this.engname =  row[4] 
      this.skype_id =  row[5]
      this.call_type =  row[6]
      this.attend_sheet_flag =  row[7]
      this.phone =  row[8] 

      this.teacher =  row[9]

      this.taking_class_flag = row[11]
      this.kakao_id =  row[12]
      // this.start_day = row[11]
      this.start_date = new Date(row[13])
      this.end_date = new Date(row[14])
      this.end_date_by_teacher = new Date(row[15])
      this.fee =  row[16]
      this.pdf = undefined
      this.excel = undefined
    }
  }