2014年1月26日 星期日

在Blogger顯示Google試算表的QUERY查詢結果

本文是Google Visualization API的運用,可以在部落格顯示Google試算表的QUERY查詢結果,點選欄位還有排序效果,程式碼是參考Google Code Playground 範例程式修改。

值得一提的是,所要連接的Google試算表如果是用新版製成的,QUERY的查詢指令可能會失效,我在陰錯陽差的狀況下將連接KEY換到舊表格一切就正常了。(PS:舊版的電子表格網址是如同「https://docs.google.com/spreadsheet/ccc?key=」這樣的),如果是新版試算表在試算表右下方會有如下的標誌




這個方法無法套用在Google的協作平台,因為該網頁空間好像不允許引用外部Script(都是自家的API了,怎會有這種限制)

如果想學習套用,請將程式碼以HTML編輯模式貼入文章內文中,並修改程式碼內文有標註//*修改地方(有2處)
 
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    
    <title>
      Google Visualization API Sample
    </title>
    <script src="//www.google.com/jsapi" type="text/javascript"></script>
    <script type="text/javascript">
      google.load('visualization', '1', {packages: ['table']});
    </script>
    <script type="text/javascript">
    function drawVisualization() {
     //var query = new google.visualization.Query( 
     //     'https://docs.google.com/spreadsheets/d/1SXPknVhdGumk-DLLWCijfMV8m11lWI0Fo-BbjF9WCHA/edit?usp=sharing');  
     //連接的spreadsheet(電子表格)如果是新版的,經測試Query的任何查詢指令似乎都會失效
     var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheet/ccc?key=0AkeHSKjTEDu_dDhqOXBXSDFuQm0xR284dkwwalZqWnc&usp=sharing');
          //*修改地方1:new google.visualization.Query(Google試算表參照網址)要改成自己的網址 
    
      // Apply query language.
      //相關QUERY語法請參照https://google-developers.appspot.com/chart/interactive/docs/querylanguage
      //query.setQuery('SELECT A,B where D>1');//OK
      //query.setQuery('SELECT * where upper(B) contains upper("ja")');//OK
      query.setQuery('SELECT * where upper(B) like upper("%jaC%")');//OK
      //*修改地方2:query.setQuery(查詢語法)

      // Send the query with a callback function.
      query.send(handleQueryResponse);
    }
    
    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }
    
      // Create and draw the visualization.
      var data = response.getDataTable();
      visualization = new google.visualization.Table(document.getElementById('table'));

      <!-- visualization.draw(data, null); -->
      visualization.draw(data, {showRowNumber: true});
    }
    
    google.setOnLoadCallback(drawVisualization);
    </script>
  </head>
  <body style="border: 0 none; font-family: Arial;">
    <div id="table">
</div>
</body>
</html>

套用後的效果如下(請在各標題欄位點一下會有排序效果,如果滑鼠移動到某一列顏色也會有變化) Google Visualization API Sample

2014年1月24日 星期五

google試算表跨檔匯入資料

試過以內建的函數來寫,但因為都還在研發未開放(XD),目前似乎無解,所以嘗試用script寫了跨檔匯入的功能 範例檔請自行下載,共有三個檔 跨工作表查詢-匯入2個外部檔(含script)、北部銷貨、中部銷貨,請用副本模式下載至自己的雲端硬碟
執行範例檔之前,並至 跨工作表查詢-匯入2個外部檔 / 工作簿KEY工作表 修正成你的副本Key(副本的KEY會跟作者的KEY不一樣)






script內容如下:
//宣告全域變數,讓其他自建函數也能取用該值
var keys
var thisbookkey
var values

function onOpen() {
  //目前新版的試算表不提供於表格內圖案指定指令碼,所以用了選單功能
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "各地區彙總",
    functionName : "getOtherWorkbook"
  }];
  sheet.addMenu("銷售報表", entries);
};

function getOtherWorkbook(){
  getKey();//取得自建函數getKey()的 keys值
  // keys值要運用在迴圈,取得每個工作簿的內容
  var targetlastrow=1
  for (var i=0;i<keys.length;i++){
    
      var ss =SpreadsheetApp.openById(keys[i]);//指定工作簿
      SpreadsheetApp.setActiveSpreadsheet(ss);//指定工作表
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作表1");
      var lastrow=sheet.getLastRow();//用getLastColumn()會出錯,無所謂,因為一般資料庫表單欄位不會讓查詢使用者動
      if(i==0){
        values= sheet.getSheetValues(1, 1, lastrow, 3);//假設各地區銷售工作表的欄位數是固定的,這裡的範例是3欄
      }else{
        values= sheet.getSheetValues(2, 1, lastrow, 3);//第一個工作簿以後,不需要再取出欄位名稱
      }
    //Logger.log(values.length);
      copy2TargetWorkbook();
  }
}
 
function copy2TargetWorkbook(){
  //COPY至目的工作簿表格
  var ss = SpreadsheetApp.openById("1PSCFJ-XohhHnV2uiunYjljeFMgSSRPlHH5qHXuzDWr4");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("銷售紀錄彙總表");
  var lastrow=sheet.getLastRow();
  lastrow++;//
  //Logger.log(values);
  
  var cell=sheet.getRange(lastrow, 1, values.length, values[0].length);//指定目的儲存格範圍 
  cell.setValues(values);//要讓 資料自動展開,不可用cell.setValue(values),不然不會分解值,會全部放在第一個儲存格 
  //Logger.log(sheet.getLastRow());//由 檢視 / 記錄  查看執行的結果
  
}

function getKey(){
  thisbookkey=SpreadsheetApp.getActiveSpreadsheet().getId();//取得主工作簿的key
  var ss = SpreadsheetApp.openById(thisbookkey);
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("銷售紀錄彙總表");
  //先清空 銷售紀錄彙總表 的資料內容
  sheet.clearContents();
  //工作簿KEY工作表內有放各工作簿的Key值,取出各工作簿的KEY值
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作簿KEY");
  var lastrow=sheet.getLastRow();
  keys =sheet.getSheetValues(2, 2, lastrow-1, 1);
  //Logger.log(thisbookkey);
}

2014年1月22日 星期三

Class Sheet getSheetValues方法

(google apps script)
取出儲存格範圍內的值
語法:

getSheetValues(startRow, startColumn, numRows, numColumns)
getSheetValues(起始列號,起始行號,列展延量,行展延量)

準備工作:
請在A2:H8內輸入任意內容,接下來再進入 工具 / 指令碼編輯器內將以下的代碼COPY貼上
//**** 取出儲存格範圍內的值
//getSheetValues(startRow, startColumn, numRows, numColumns)
//適用於取出有範圍的儲存格
//這個方法取出的值Values,可以先下用setValue 方法(記住沒有s喔)看一下格式全貌 或 用 Logger.log(values);來檢視
//樣貌如後 [14-01-23 00:02:52:601 HKT] [[*車種*, *車次*, *經由*, *發車站->終點站*, *臺北開車時間*, *花蓮到達時間*, *行駛時間*, *備註*],
//[自強, 202.0, -, 樹林->花蓮, -693958.7430555555, -693958.65625, 02小時05分, 逢週一、五至日行駛。新自強號(普悠瑪)列車,不發售無座票。],
function testrun(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();//指定工作簿
  var sheet = ss.getSheets()[1];//指定工作表
  //var cell=sheet.getRange("A21:H28");//這個表示法與 var cell=sheet.getRange(21, 1, 7, 8);相同
  var  values= sheet.getSheetValues(2, 1, 7, 8);//取得來源儲存格範圍內的值
  var cell=sheet.getRange(21, 1, 7, 8);//指定目地儲存格範圍
  cell.setValues(values);//要讓資料自動展開,不可用cell.setValue(values),不然不會分解值,會全部放在第一個儲存格
  //Logger.log(values);
}
這段代碼同時演示了[取值]與[賦值]的方法,
var cell=sheet.getRange(21, 1, 7, 8);這行代碼中的7、8因為必須與 values二維陣列變數大小一致,所以可以改寫成
var cell=sheet.getRange(21, 1, values.length, values[0].length);


其餘的在代碼內已有說明語法請自行演練,以下是我跑出來的結果

2014年1月21日 星期二

在Blogger中插入簡單好讀的程式碼片段(二)

第一篇的方法顯示起來有點不夠穩重大方,所以上網找了幾篇文章綜整了一下,也不引用舊版的方法(原先在CSS檔的CODE圖示要在自己的網頁空間設置網路路徑,現在已經升級進化無需設置了,網頁開啟速度似乎也比較快)  



☞第一次使用前置作業 (以後無需重複這些步驟)
1.到 範本 / 修改HTML,</head>之前加上下面這段代碼
<script src="//google-code-prettify.googlecode.com/svn/loader/run_prettify.js"></script>
這樣以後在Blogger HTML編輯模式就不用一直加這段代碼(你也可以選擇每次POST文章時,在文章編輯視窗加上這段代碼,這樣就不會因為換了其他範本,代碼也跟著消失)

 2.在版面配置 / 範本設計工具進階 / 新增CSS 增加下列代碼

.post .code { 
  display: block; /* fixes a strange ie margin bug */
  font-family: Courier New; 
  font-size: 10pt; 
  overflow:auto; 
  background: #f0f0f0 url(data:image/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAsAAASwCAYAAAAt7rCDAAAABHNCSVQICAgIfAhkiAAAAQJJREFUeJzt0kEKhDAMBdA4zFmbM+W0upqFOhXrDILwsimFR5pfMrXW5jhZr7PwRlxVX8//jNHrGhExjXzdu9c5IiIz+7iqVmB7Hwp4OMa2nhhwN/PRGEMBh3Zjt6KfpzPztxW9MSAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzAMwzB8HS+J9kUTvzEDMwAAAABJRU5ErkJggg==) left top repeat-y;
  border: 1px solid #ccc;
  padding: 10px 10px 10px 21px;
  max-height:200px;
  line-height: 1.2em;
}

放上你想顯示的程式碼片段(以後只要執行此步驟) 
在文章HTML編輯模式加上這段代碼
<pre class="code prettyprint">
貼上想要顯示程式代碼
</pre>
底下是範例結果

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "選單項內容",
    functionName : "呼叫函數"
  }];
  sheet.addMenu("My選單", entries);
};

2014年1月20日 星期一

正則表達式取代字元,擷取想要的內容

網頁中有些標籤內容看似很複雜又很有規律,如何快速擷取?

(以下敘述均針對Google Spreadsheet環境下操作)
底下這些內容是撈取自網頁 <script> 標籤的內容,想要快速的分解出  臺北地區、桃園地區..等字串,就需要借助正則表達式來作取代分解的動作,下面的說明會運用到的函數有REGEXREPLACESPLIT、TRANSPOSE,請將下面的內容copy到儲存格A1

"TRCity.push('0');TRCity.push('臺北地區');TRCity.push('Taipei');TRCity.push('1');TRCity.push('桃園地區');TRCity.push('Taoyuan');TRCity.push('2');TRCity.push('新竹地區');TRCity.push('Hsinchu 
');TRCity.push('3');TRCity.push('苗栗地區');TRCity.push('Miaoli 
');TRCity.push('4');TRCity.push('臺中地區');TRCity.push('Taichung 
');TRCity.push('5');TRCity.push('彰化地區');TRCity.push('Changhua 
');TRCity.push('6');TRCity.push('南投地區');TRCity.push('Nantou');TRCity.push('7');TRCity.push('雲林地區');TRCity.push('Yunlin');TRCity.push('8');TRCity.push('嘉義地區');TRCity.push('Chiayi 
');TRCity.push('9');TRCity.push('臺南地區');TRCity.push('Tainan');TRCity.push('10');TRCity.push('高雄地區');TRCity.push('Kaohsiung');TRCity.push('11');TRCity.push('屏東地區');TRCity.push('Pingtung 
');TRCity.push('12');TRCity.push('臺東地區');TRCity.push('Taitung');TRCity.push('13');TRCity.push('花蓮地區');TRCity.push('Hualien 
');TRCity.push('14');TRCity.push('宜蘭地區');TRCity.push('Yilan 
');TRCity.push('15');TRCity.push('平溪線');TRCity.push('Pinghsi 
Line');TRCity.push('16');TRCity.push('內灣/六家線');TRCity.push('Neiwan / Liujia 
Line');TRCity.push('17');TRCity.push('集集線');TRCity.push('Jiji 
Line');TRCity.push('18');TRCity.push('沙崙線');TRCity.push('Shalun 
Line');TRCity.push('19');TRCity.push('深澳線');TRCity.push('ShenAo Line');"

請在A2儲存格輸入  =REGEXREPLACE($A$1,"[A-Za-z.()'\s地區]","")
結果如下

0;臺北;;1;桃園;;2;新竹;;3;苗栗;;4;臺中;;5;彰化;;6;南投;;7;雲林;;8;嘉義;;9;臺南;;10;高雄;;11;屏東;;12;臺東;;13;花蓮;;14;宜蘭;;15;平溪線;;16;內灣/六家線;/;17;集集線;;18;沙崙線;;19;深澳線;;

說明:
一、
REGEXREPLACE(被取代的字串,正則表達式,要變成的字串)
這是用正則表達式挑選出來的字元集合來取代字串的函數

簡單說明一下正則表達式 "[A-Za-z.()'\s地區]" 的功用
正則表達式需要用 [  ] 包起來,
字串中只要 有英文字母A-Z(不管大小寫)、. 號、' 號、()號、任何型式的空白字元(\s)、地區 字眼,都要挑出來,並且用第三個參數 "" 空白來取代

更深入的正則表達式說明請善用網路搜尋學習

PS:在這個例子中沒有取代掉阿拉伯數字0-9,是因為這是地區代碼,所以保留了起來

二、
仔細觀察一下上面輸出的結果,"/"這個符號其實是沒作用的,我們的重點是地區代號(阿拉伯數字),所以在 A2的儲存格 公式就替換為 
=REGEXREPLACE($A$1,"[A-Za-z.()'/\s地區]","")

結果如下

0;臺北;;1;桃園;;2;新竹;;3;苗栗;;4;臺中;;5;彰化;;6;南投;;7;雲林;;8;嘉義;;9;臺南;;10;高雄;;11;屏東;;12;臺東;;13;花蓮;;14;宜蘭;;15;平溪線;;16;內灣六家線;;17;集集線;;18;沙崙線;;19;深澳線;;

三、使用SPLIT(字串,分割字串,[TRUE])
由資料內容來看,0;臺北;;是一組DATA,1;桃園;;又是一組DATA,表示我們如果要分離字串,他的分割字串就是 ;;
所以,請在 A3儲存格輸入公式 =SPLIT($A$2,";;")
然而當我們輸入這樣的公式時,會大失失望,因為工作表這個公式的第三個參數默認值是TRUE,會把所有帶有 ; (不管是幾個連續的 ; 號)都視為分割字元,所以要修正成

=SPLIT($A$2,";;",FALSE)
表示我們要分割的字串組是要 確實的二個 ; 號

分離的結果是橫向排列

一般運用上,我們不希望橫向放分離過後的字串陣列(系統可運用的Column數比ROW數少,放資料筆數容易受到限制),所以運用TRANSPOSE(字串陣列)來轉置(橫轉直,直轉橫)改變方向

所以在 A5 儲存格 我們做個對照組,輸入公式 = TRANSPOSE(SPLIT($A$2,";;",FALSE))
可以看到不一樣的顯示結果
 

<本章節結束>

在Blogger中插入簡單好讀的程式碼片段(一)

操作實例:

在自己的Blogger HTML編輯版面  適當位置插入下面程式片段即可
<script src="//google-code-prettify.googlecode.com/svn/loader/run_prettify.js"></script>
<pre class="prettyprint">
 Copy你的程式碼片段
</pre>


例如
<script src="//google-code-prettify.googlecode.com/svn/loader/run_prettify.js"></script> 
<pre class="prettyprint">
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "選單項內容",
    functionName : "呼叫函數"
  }];
  sheet.addMenu("My選單", entries);
};

function 呼叫函數() {
Browser.msgBox('hello world');
};


</pre>

就會呈現如下的效果了


function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "選單項內容",
    functionName : "呼叫函數"
  }];
  sheet.addMenu("My選單", entries);
};

function 呼叫函數() {
  Browser.msgBox('hello world'); 
};

2014年1月19日 星期日

Google spreadsheet(二)

2. QUERY
從網頁導入的資料可能筆數很多,這時如果會使用QUERY來查詢,可以快速擷取你要的資訊,也比較不傷眼睛(XD大誤

語法:QUERY(資料, 查詢, 標題)

資料,指的是儲存格範圍
查詢,姑且稱做為 類SQL語言,不過因為都在 spreadsheet 內操作,所以無須寫 FROM TableName(相關的SQL語法請參照相關資料)
在spreadsheet中,標題是用不上的,都改用欄位名(Column)來查詢--見下面範例

篩選資料,原則上是可以用篩選功能,只不過是我大喜歡這個功能,因為篩選後,列的資料會隱藏,導致右邊的空白欄位無法利用(會隱藏不見)

援用上例,做個實例操作,在K2欄位輸入
=QUERY(A2:H,"select * where H like '%太魯閣%'")

這代表要找到 DATA(A2:H), H欄位中 包含有 太魯閣 字串的記錄
其中 %為萬用字元,為一個以上的字串,也就是  太魯閣前後都可以有字串

這樣就可以找到當日太魯閣號的自強號班次,你也可以改成 普悠瑪 試試...






 

Google spreadsheet(一)

用慣MS的EXCEL,原本懶得換別的工具
沒想到Google spreadsheet意外的好用,只是得先習慣它的操作方式


這幾天試著用內建的函數寫了<台鐵自強號列車查詢>,很意外的竟無須寫Script

預防文章的說明敘述不夠詳細,所以附上檔案供大家參考

因為權限開的是檢視模式,所以其他使用者無權操作所有功能(下拉式選單、展開隱藏列等等)

如果你有興趣並且擁有Google雲端硬碟帳號打開檔案後  檔案 / 建立副本(等同於你將檔案下載至你的Google雲端系統),之後就可以自行查看並操作所有功能

檔案連結:
查詢表-台鐵自強號


以下就用這個實例來逐一講解相關的公式使用(IMPORTHTML、QUERY、IMPORTXML、SUBSTITUTE、REGEXREPLACE、SPLIT、TRANSPOSE)

在這之前,如果要從網頁撈到資料,工具一定不能少,在此推薦FIRFOX的抓包插件 HttpFox不然現在的網頁都有使用FORM、FRAM,可能難以找到正確網址,相關教學請參考
EXCEHOME網頁採集
或自行上網搜尋

應用這個軟體,當我們填好資料送出時,抓包數據就替我們抓到正確的連結網址了

 現在我們可以利用這個網址進行 IMPORTHTML 的操作


1. IMPORTHTML



這個函數相當於EXCEL的 資料/從WEB
取得資料的功能(WEBQUERY),只不過
MS沒有提供函數給使用者操作


使用方法:
IMPORTHTML(網址, 查詢, 索引)


查詢
根據包含所需資料的結構類型,可以設定成「清單」或「表格」。

索引
從「1」開始的索引,指明應該傳回 HTML 原始碼中界定的哪個表格或清單,在網頁的原始碼內應該可以看到<table>......</table>的標籤,記得順便數一下我們要的資料在第幾組,以本例而言是第2組,所以  索引=2  。

假設你從台鐵火車時課查詢選擇客製化查詢=>台北-花蓮,自強號
網址長得應該是下面這樣
http://twtraffic.tra.gov.tw/twrail/CustomSearchResult.aspx?todate=2014/01/19&backdate=&fromstation=1008&tostation=1715&trainclass=%271100%27,%271101%27,%271102%27,%271107%27&roadtype=3&handicapped=undefined&dining=&addtrain=undefined&totime=0000&backtime=0000
把它貼到 A1 儲存格 


在 B2 儲存格內輸入如下公式,就會出現台鐵網頁的查詢結果了,對於每次搭固定點的人而言,可以寫一次一勞永逸,之後就不用上網填選資料(很煩),當然要把網頁的日期等寫成變數,以後只要改日期就好

=IMPORTHTML(A1, "table", 2)