Category: Google Apps

1. ปิด Google Sheets : GASWS1
2. เมนู Tools > Script Editor…
3. เมนู File > New > Script File
   ตั้งชื่อ: myscript6
4. สร้าง function MailMerge3() ตามนี้

   function MailMerge3() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
    SalarySheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Salary")), 
    TemplateID='1bjpQnJikYMGYNaJQhetpZpkHyjI7iqBqDufprzMSo4k', 
    header = "A1:G1",
    data="A2:G6", 
    dataRows = SalarySheet.getRange(data).getValues(),
    headerRow = SalarySheet.getRange(header).getValues(),
    numColumns = SalarySheet.getRange(header).getNumColumns(),
    emailText = ""; 
   
    for (var i=0 ; i < dataRows.length ; i++) {
    emailText="";
    var id =DriveApp.getFileById(TemplateID).makeCopy("กองคลัง : แจ้งการโอนเงินให้คุณ " + dataRows[i][0] + " " + dataRows[i][1]).getId(),
    doc=DocumentApp.openById(id),
    docBody=doc.getBody();
    for (var j = 0 ; j < numColumns -1 ; j++) { 
    docBody.replaceText('{' + headerRow[0][j] + '}' , dataRows[i][j]); 
    } 
    emailText=docBody.getText();
    doc.saveAndClose();
    
    
    var emailaddress=dataRows[i][2],
    subject = "กองคลัง : แจ้งการโอนเงินให้คุณ " + dataRows[i][0] + " " + dataRows[i][1],
    attachment = DocumentApp.openById(id);
    MailApp.sendEmail( emailaddress, 
    subject ,
    emailText, 
    {
    attachments: [attachment.getAs(MimeType.PDF)]
    }
    ); 
    try { 
   
    dataRows[i][numColumns-1] = new Date(); 
    } catch (e) {
    
    dataRows[i][numColumns-1] = e.message;
    } 
    }
    
    SalarySheet.getRange(data).setValues(dataRows);
   }

5. เมนู File > Save หรือ กดปุ่ม Ctrl+s
6. เมนู Run > MailMerge3

1. เปิด Google Sheets : GASWS1
2. เมนู Tools > Script Editor…
3. เมนู File > New > Script File
   ตั้งชื่อ: myscript6
4. สร้าง function MailMerge2() ตามนี้

   function MailMerge2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
    SalarySheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Salary")), 
    TemplateID='1bjpQnJikYMGYNaJQhetpZpkHyjI7iqBqDufprzMSo4k', 
    header = "A1:G1",
    data="A2:G6", 
    dataRows = SalarySheet.getRange(data).getValues(),
    headerRow = SalarySheet.getRange(header).getValues(),
    numColumns = SalarySheet.getRange(header).getNumColumns();
    
    
   
    for (var i=0 ; i < dataRows.length ; i++) {
    var id =DriveApp.getFileById(TemplateID).makeCopy("กองคลัง : แจ้งการโอนเงินให้คุณ " + dataRows[i][0] + " " + dataRows[i][1]).getId(),
    doc=DocumentApp.openById(id),
    docBody=doc.getBody();
    for (var j = 0 ; j < numColumns -1 ; j++) { 
    docBody.replaceText('{' + headerRow[0][j] + '}' , dataRows[i][j]); 
    } 
    doc.saveAndClose();
    
    var pdfFile = DriveApp.createFile(doc.getAs(MimeType.PDF));
    try { 
   
    dataRows[i][numColumns-1] = new Date(); 
    } catch (e) {
    
    dataRows[i][numColumns-1] = e.message;
    } 
    }
    
    SalarySheet.getRange(data).setValues(dataRows);
   }

5. เมนู File > Save หรือ กดปุ่ม Ctrl+s
6. เมนู Run > MailMerge2

1. เปิด Google Sheets : GASWS1
2. สร้าง Sheet ใหม่ ชื่อ “Salary”
3. ใส่ข้อมูล
   A1 = “Firstname”
   B1=”Lastname”
   C1=”Email”
   D1=”SalaryDetail”
   E1=”Salary”
   F1=”TransferDate”
4. จากนั้น ให้กรอกข้อมูลต่างๆ จำนวน 5 แถว ดังภาพ
   
5. สร้าง Sheet ใหม่ ชื่อ “Template”
6. ใส่ข้อมูล
   A1: เรียน คุณ{Firstname} {Lastname}
   A2: จะมี {SalaryDetail} จำนวน {Salary} บาท โอนให้คุณในวันที่ {TransferDate}
   A3: จึงเรียนมาเพื่อทราบ
   A4: การเงิน
7. เมนู Tools > Script Editor…
8. เมนู File > New > Script File
   ตั้งชื่อ: myscript5
   เขียนโค๊ดตามนี้

   function MailMerge1() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
    SalarySheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Salary")),
    TemplateSheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Template")),
    header = "A1:G1",
    data="A2:G6",
    template="A1:A4",
    dataRows = SalarySheet.getRange(data).getValues(),
    headerRow = SalarySheet.getRange(header).getValues(),
    numColumns = SalarySheet.getRange(header).getNumColumns(),
    templateRows= TemplateSheet.getRange(template).getValues();
    
    for (var i=0 ; i < dataRows.length ; i++) {
    var newText = templateRows[0][0] + "\n" + 
    "\t" + templateRows[1][0] + "\n" +
    "\t" + templateRows[2][0] + "\n" +
    templateRows[3][0] + "\n"; 
    for (var j = 0 ; j < numColumns -1 ; j++) { 
    newText = newText.replace('{' + headerRow[0][j] + '}', dataRows[i][j]); 
    }
    try { 
   
    dataRows[i][numColumns-1] = new Date(); 
    } catch (e) {
    
    dataRows[i][numColumns-1] = e.message;
    }
    
    Logger.log(newText);
    }
    
    
    SalarySheet.getRange(data).setValues(dataRows);
    
   }

9. เมนู File > Save หรือ กดปุ่ม Ctrl+s
10. เมนู Run > MailMerge1
11. ดูผลได้ที่ เมนู View > Logs หรือ กดปุ่ม Ctrl+Enter

มีโจทย์ว่าต้องการคำนวนค่า Indicator ชื่อ RSI ของกองทุนรวมในต่างประเทศ ซึ่งปิดการซื้อขายเวลา 04:00 ของเวลาในประเทศไทย ทางกองทุนจะประกาศราคา (NAV) ในเวลาประมาณ 09:00 แต่ในการตัดสินใจลงทุน จะต้องพิจารณาค่า RSI ซึ่งกองทุนจะคำนวนและประกาศให้ประมาณ 12:00 ซึ่งบางทีก็หลงลืม และไม่ทันการ

การคำนวนค่า RSI ใช้ค่าการเปลี่ยนแปลงของราคา แบ่งเป็น Gain และ Loss แล้วมาหาค่าเฉลี่ยและเข้าสูตรคำนวน (ไม่ขอกล่าวถึง หากต้องการทราบข้อมูลเพิ่มเติม อ่าน http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:relative_strength_index_rsi)

สรุปคือ หากต้องการทราบค่า RSI ก่อนที่ทางกองทุนจะประกาศให้ ก็ต้องคำนวนเอง โดยจดค่าการเปลี่ยนแปลงของราคาในแต่ละวันซึ่งเป็นงานที่น่าเบื่อ จึงต้องหาวิธีลดภาระ

แนวคิดคือ เว็บไซต์ http://www.bloomberg.com/quote/WGHCEPA:ID ซึ่งจะประกาศราคา NAV ในเวลา 09:00 นั้น หากเปิด HTML ดูจะพบว่าข้อมูลที่ต้องการจะอยู่ในรูปแบบของ

...
<meta itemprop="price" content="46.92">
...
<meta itemprop="priceChange" content="0.16">
...
<meta itemprop="priceChangePercent" content="0.34">
...
<meta itemprop="quoteTime" content="2015-05-11T00:59:30-0400">

จากนั้น สร้าง Function ตามนี้

function getWGHCEPA(){
 var content=UrlFetchApp.fetch("http://www.bloomberg.com/quote/WGHCEPA:ID");
 //'<meta itemprop="priceChange" content="0.16">';
 var data = content.getContentText().match(/meta itemprop="(.*)" content="(.*)"/mg );
 
 var id="xxxxxxxxxxxxxxxxxxxx Your file ID xxxxxxxxxxxxxx";
 var db=SpreadsheetApp.openById(id);
 var table=db.setActiveSheet(db.getSheetByName("Data"));
 
 var result=[];
 for (var i=0; i<data.length; i++){
 var temp=data[i].match(/meta itemprop="(.*)" content="(.*)"/); 
 result[temp[1]]=temp[2];
 } 
 
 table.appendRow([result["quoteTime"], result["price"], result["priceChange"], result["priceChangePercent"]]);
 
}

จากนั้น ตั้ง Trigger ให้ทำงานวันละครั้ง เวลาตั้งแต่ 09:00-10:00 เป็นอันเรียบร้อย

ผลที่ได้

1. เปิด https://drive.google.com แล้ว Login
2. คลิกปุ่ม New > Google Sheets
3. ตั้งชื่อไฟล์: GASWS1
4. เมนู Tools > Script Editor
5. เลือก Blank Project
6. ตั้งชื่อโปรเจค : myproject1
7. เมนู File > New > Script File
8. ตั้งชื่อ: myscript1
9. แก้ไข myFunction() ตามนี้

   function myFunction() {
    Logger.log("Hello World");
   }
   

10. เมนู File > Save หรือ กดปุ่ม Ctrl+s
11. เมนู Run > myFunction
12. ดูผลได้ที่ เมนู View > Logs หรือ กดปุ่ม Ctrl+Enter
13. สร้าง function myForLoop ดังนี้
14. function myForLoop(){
for (var i=1; i<=10 ; i++) {
myFunction();
}
}
15. แล้ว Save ด้วย กดปุ่ม Ctrl+s
16. เมนู Run > myForLoop
17. ดูผลได้ที่ เมนู View > Logs หรือ กดปุ่ม Ctrl+Enter
18. เมนู File > New > Script File
19. ตั้งชื่อ myscript2
20. สร้าง function myDate ดังนี้
21. function myDate() {
Logger.log(new Date());
}
22. แล้ว Save ด้วย กดปุ่ม Ctrl+s
23. เมนู Resources > All yours triggers
24. คลิก No triggers set up. Click here to add one now.
25. ตั้งค่า
    Run = myDate
    Event = Time-driven
    แล้วเลือกเป็น Minutes timers และ Every minute
    จากนั้นคลิกปุ่ม Save
26. ดูผลได้ที่ เมนู View > Logs หรือ กดปุ่ม Ctrl+Enter

1. เปิด Google Sheets : GASWS1
2. สร้าง Sheet ใหม่ ชื่อ “Log”
3. เมนู Tools > Script Editor…
4. เมนู File > New > Script File
5. ตั้งชื่อ: myscript4
6. เขียนโค๊ดตามนี้

    function check_website(url) { 
    var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
    return response.getResponseCode();
   }
   
   function doLog(timestamp, responseCode, timeDiff) {
    
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Log")); 
    sheet.appendRow([timestamp, responseCode, timeDiff]);
   }
   
   function getTime() {
    var startTime = new Date() ;
    var responseCode=check_website("http://www.psu.ac.th");
    var endTime = new Date() ;
    var timeDiff = endTime-startTime; 
    doLog(Utilities.formatDate(new Date(), "GMT+7", "yyyyMMdd-HHmmss") , responseCode , timeDiff);
   }

7. เมนู Resources > All yours triggers
   คลิก No triggers set up. Click here to add one now.
   ตั้งค่า
   Run = getTime
   Event = Time-driven
   แล้วเลือกเป็น Minutes timers และ Every minute
   จากนั้นคลิกปุ่ม Save

1. เปิด Google Sheets : GASWS1
2. เมนู Tools > Script Editor…
3. เมนู File > New > Script File
4. ตั้งชื่อ: myscript3
5. สร้าง function insertData1() ตามนี้

   function insertData1() {
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Sheet1"));
    sheet.getRange(1, 1).setValue("Hello World");
    sheet.getRange("A2").setValue("สวัสดี");
    
    var v1=[ ["ชื่อ","นามสกุล","อายุ"] ];
    var v2=[
    ["สมชาย"]
    ,["Robert"]
    ,["39"]
    ];
    sheet.getRange("A3:C3").setValues(v1);
    sheet.getRange("A4:A6").setValues(v2);
   }

6. เมนู Run > insertData1
7. สร้าง function insertData2() ตามนี้

   function insertData2() {
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Sheet1")); 
    sheet.appendRow([new Date(),"something"]);
   }

8. เมนู File > Save หรือ กดปุ่ม Ctrl+s
9. เมนู Run > insertData2
10. เมนู Resources > All yours triggers
    คลิก No triggers set up. Click here to add one now.
    ตั้งค่า
    Run = insertData2
    Event = Time-driven
    แล้วเลือกเป็น Minutes timers และ Every minute
    จากนั้นคลิกปุ่ม Save
11. ไปที่ Google Sheets “GASWS1”
12. สร้าง Sheet ใหม่ ชื่อ “ReadData”
13. เมนู Tools > Script Editor…
14. สร้าง function readData1() ตามนี้

    function readData1(){
     var ss=SpreadsheetApp.getActiveSpreadsheet();
     var sheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("ReadData")); 
    
     var url="https://docs.google.com/spreadsheets/d/1oztRXjC2KJzrqC3LtiRnUPrEgohX4VTYHixylvtCdzY/edit?usp=sharing";
     var db=SpreadsheetApp.openByUrl(url);
     var table=db.setActiveSheet(db.getSheetByName("WGHCEPA"));
     var data=table.getRange("A1:J10").getValues();
     
     for (var i=0; i<data.length;i++){
     sheet.appendRow(data[i]);
     }
     
    }

15. เมนู File > Save หรือ กดปุ่ม Ctrl+s
16. เมนู Run > readData1
17. สร้าง function readData2() ตามนี้

    function readData2(){
     var ss=SpreadsheetApp.getActiveSpreadsheet();
     var sheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("readData")); 
    
     var id="1oztRXjC2KJzrqC3LtiRnUPrEgohX4VTYHixylvtCdzY";
     var db=SpreadsheetApp.openById(id);
     var table=db.setActiveSheet(db.getSheetByName("WGHCEPA"));
     var data=table.getDataRange().getValues();
     
     for (var i=0; i<data.length;i++){
     sheet.appendRow(data[i]);
     }
     
    }

18. เมนู File > Save หรือ กดปุ่ม Ctrl+s
19. เมนู Run > readData2
20. สร้าง function insertData3() ตามนี้

    function insertData3(){
     var url="https://docs.google.com/a/psu.ac.th/spreadsheets/d/1bQyyQrB3PPyawWVb3afMi0Kgz1KUibIfMblv351BtQI/edit?usp=sharing";
     var db=SpreadsheetApp.openByUrl(url);
     var table=db.setActiveSheet(db.getSheetByName("Sheet1"));
     // เปลี่ยนเป็น ชื่อ นามสกุล และคณะ/หน่วยงานของท่าน
     var mydata=["ชื่อ","นามสกุล","คณะ/หน่วยงาน"];
     
     table.appendRow(mydata);
    }

21. เมนู File > Save หรือ กดปุ่ม Ctrl+s
22. เมนู Run > insertData3