Google Spreadsheet 妙用 之 importxml (1)

久久沒更新，少玩了Minecraft後就忘了這BLOG。
今天決定寫一篇文來當作記錄，為此blog增加沒用處的存在感。
這次一樣使用Windows live writer來編寫，排版怪掉就請多多無視吧。m(_ _)m

Google Spreadsheet，也就是Google試算表。
普通用家大概會它當作Excel的線上代替品，但它的功能卻擁有超越了Excel本身能力。如它的問卷表格功能，線上同時編輯，甚至能使用script editor為你的試算表寫更強大的功能。

今天來談談的，是importxml的妙用。

importxml是一個formula function，能夠讓你匯入任何網頁的XML資料進試算表。
它還有一個importHTML版本，匯入HTML資料用。

 

實用例子:

• 你可以由股票網站直接匯入你喜歡的股票進試算表
• 你可以由PAD WIKIA匯入今天的活動進試算表
• 你還可以批量查TWITTER,GOOGLE,BING等資料,把其結果匯入試算表
• 批量查erep玩家是不是今天沒三擊(無誤)

 

今天剛好我想寫一個，能一口氣把整排的關鍵字放進GOOGLE搜尋，然後在試算表內列出各關鍵字的首個結果連接給我。這次就決定拿importxml作為開刀功能。

 

首先，就像料理一樣，準備材料:

• 一個Google試算表
• 一個GOOGLE搜尋用的連接
  • http://www.google.com/search?q=關鍵字
• 一個搜尋用的關鍵字格子編號 (例子:A2 )
• 一個GOOGLE結果頁的資料Xpath
• 或 直接用懶人法: http://www.xpathbuilder.com/

 

打開你的試算表，輸入一排關鍵字在A排。

然後在B2格作為結果開刀。

• 輸入以下句子
  • =importxml(搜尋用的連接, Xpath句)

這次例子:

• =importxml(http://www.google.com/search?q="&$A2&"&num=1&pws=0, "//h3[@class='r']/a/@href")
• 注意黃底的就是關鍵字所用的試算表格子編號(沒有它,你要查什麼啊!)

結果:

 

好多結果啊!?

冷靜，這是因為importxml是直接把所有查出來的結果都丟出來。

 

讓我們動刀修剪一下句子。
首先，把結果縮至一個。這次我們使用index功能即可。

• index(array結果,序位)
  • 我們只要第1個，所以序位填進1。

 

這次例子:

• =index(importxml("http://www.google.com/search?q="&$A2&"&num=1&pws=0", "//h3[@class='r']/a/@href"),1)

結果:

成功變成單一結果了，但句子還是很多不該混入的東西混進去。這時不妨拿出你喜歡修剪string用的工具（regex/google內建的TEXT功能等等），把它修正變為連接。

 

紅圈的正是該切掉的東西。前者為”/url?q=”，後者是”&sa=”與它後面的全部。
這就當作小型練習吧! 我就不附解答了。

提示: SUBSTITUDE, SPLIT

 

結果:

大成功!

 

 

我們直接把2排的方程式都拉下來給A3的關鍵字用。

完全大成功!

但我不知道為什麼艦ptt的第1個結果是這文章。

 

大概就這樣子，這樣就能做出批量式查字，並輸出結果首位連接到試算表。

若有大量關鍵字需要查，就能使用這方法了! (看著咪服的大量模組名字)

 

這方法換用importHTML，甚至能夠直接挖出HTML的部份，讓你能夠把結果網頁連帶各種複雜attribute之類都可以查得乾乾淨淨。

還可以配合GOOGLE問卷等功能，做到更強大功能搜尋。(給人填店名，可列出地點，評價，甚至這店相關最新的TWITTER/特價消息等等)

 

好功能，又免費，多多使用吧!