source code บน google sheet
ทำการเก็บข้อมูล หลังตลาดปิด ผมใช้มาตั้งแต่ปี 2013
เวลาไปเที่ยวไหนก็ไม่กังวลครับ ไป ห้าวัน เจ็ดวัน ข้อมูลก็ทำการบันทึก
ให้ครับ
******************************************
function s01_rw()
{
if (Utilities.formatDate(new Date(), "GMT+7:00", "E" ).charAt(0) == "S" ) return
///// read /////////////
var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var s1 = ss1.setActiveSheet(ss1.getSheets()[0]);
//// Force change value for importhtml
SpreadsheetApp.getActiveSheet().getRange("v12:v15").clear();
SpreadsheetApp.flush();
//////// write /////////////////////////////////
var s0 = ss1.getSheetByName("control");
var dataRange_s0 = s0.getDataRange();
var check_on_time = dataRange_s0.offset(9,7,1,1).getValues();
s0.getRange(13,8,1,1).setValue(check_on_time);
if ( check_on_time == "Active" )
{
var o1 = ss1.getSheetByName("Output1");
var dataRange_o1 = o1.getDataRange();
//**** for Output1 ***
var i = 1;
var value1 = dataRange_o1.offset(i, 0).getValue();
if ( value1 == '#VALUE!' )
{
var delay02 = dataRange_s0.offset(20,7,1,1).getValue();
var delay01 = dataRange_s0.offset(7,7,1,1).getValues();
s0.getRange(14,8,1,1).setValues(delay01);
if ( delay02 < 12000 )
{ delay02 = delay02 + 3000 ;}
Utilities.sleep(delay02);
s0.getRange(21,8).setValue(delay02);
var dataRange_o1 = o1.getDataRange();
o1.getRange(1,2).setValue(delay02);
var value1 = dataRange_o1.offset(i, 0).getValue();
}
if ( value1 > 0 )
{ var value2 = dataRange_o1.offset(i, 1).getValue();
var value3 = dataRange_o1.offset(i, 2).getValue();
var value4 = dataRange_o1.offset(i, 3).getValue();
var value5 = dataRange_o1.offset(i, 4).getValue();
var value6 = dataRange_o1.offset(i, 5).getValue();
var value7 = dataRange_o1.offset(i, 6).getValue();
var value8 = dataRange_o1.offset(i, 7).getValue();
var value9 = dataRange_o1.offset(i, 8).getValue();
var value10 = dataRange_o1.offset(i, 9).getValue();
var value11 = dataRange_o1.offset(i,10).getValue();
var value12 = Utilities.formatDate(new Date(),"GMT+7:00", "yyyy-MM-dd' 'HH:mm:ss");
o1.appendRow([value1,value2,value3,value4,value5,value6,value7,value8,value9,value10,value11,value12]);
}
}
var last_time_write = Utilities.formatDate(new Date(),"GMT+7:00", "yyyy-MM-dd' 'HH:mm:ss");
s0.getRange(7,5,1,1).setValue(last_time_write);
};
/////////////////////////////////////////////////////////////
***************************************************
เมื่อทำงานให้ mail บอกเราด้วย
function s01_start()
{
if (Utilities.formatDate(new Date(), "GMT+7:00", "E" ).charAt(0) == "S" ) return
// ***** Interval 10 min. *****
ScriptApp.newTrigger("s01_rw").timeBased().everyMinutes(10).create();
Utilities.sleep(25000);
var ss1 = SpreadsheetApp.getActiveSpreadsheet();
ss1.setActiveSheet(ss1.getSheets()[0]);
var s0 = ss1.getSheetByName("control");
var dataRange_s0 = s0.getDataRange();
var check_on_time = "Active";
s0.getRange(13,8,1,1).setValue(check_on_time);
var message = "<HTML><BODY>"
+ "<br /><br />"
+ "<P> s01-Trigger-Start"
+ "<P> "
+ "<P> "
+ "<P> Best Regards, "
+ "<P> Cloud Computer "
+ "</HTML></BODY>";
MailApp.sendEmail("
[email protected]", "s01-Trigger-Start" , "" ,{htmlBody: message});
};
**************************************************
เมื่อหยุดทำงานให้ mail บอก
function s01_stop()
{
var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var s0 = ss1.getSheetByName("Control");
var dataRange_s0 = s0.getDataRange();
// Remark Locate a trigger by unique ID
var allTriggers = ScriptApp.getScriptTriggers();
// Remark Loop over all triggers
for(var i=0; i < allTriggers.length; i++)
{
var Trigger_name = allTriggers
.getHandlerFunction();
s0.getRange(3+i,17,1,1).setValue(Trigger_name);
s0.getRange(3+i,18,1,1).setValue(allTriggers.getUniqueId());
if( Trigger_name == 's01_rw' )
{
s0.getRange(3+i,19,1,1).setValue("Delete");
ScriptApp.deleteTrigger(allTriggers);
var message = "<HTML><BODY>"
+ "<br /><br />"
+ "<P> s01-Trigger-Stop"
+ "<P> "
+ "<P> "
+ "<P> Best Regards, "
+ "<P> Cloud Computer "
+ "</HTML></BODY>";
MailApp.sendEmail("[email protected]", "s01-Trigger-Stop" , "" ,{htmlBody: message});
}
else {s0.getRange(3+i,19,1,1).setValue("Keep"); }
}
};
**********************************************************
