on
Google App Script + Sheet
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( `[email protected]`, "New Student Alert", `name : ${user.engname}
email : ${user.email}
skype : ${user.skype_id}
` ); // 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 = " 안녕하세요, 감사합니다.
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(`[email protected]`,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 } }
728x90
반응형
from http://blog.voyagerss.com/179 by ccl(A) rewrite - 2021-10-08 14:01:17