【Google Spreadsheet】IMPORT系関数まとめ

Photo by Erol Ahmed on Unsplash

IMPORTRANGE

他のシートから、情報を丸ごと取得出来ます。僕は、IMPORTRANGEの事を、この関数を知らない人に教える時、「生きたコピペ」と伝えています。元シートが更新されると、この関数が挿入されたシートも更新されます。

途中に文字を入れると、エラーになってしまいます。

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1d2EERYK7FI6h2XY5_EKO16UPpSx56ODeje7x1qbIImI/edit#gid=0","ドラえもんの道具!A:C")

ドラえもんの道具

他のシートのA列とC列だけが欲しい。他は要らないみたいな場合は、QUERY関数と併用します。

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1d2EERYK7FI6h2XY5_EKO16UPpSx56ODeje7x1qbIImI/edit#gid=0","ドラえもんの道具!A:C"),"SELECT Col1,Col2")

IMPORTRANGEを間違って編集しないように、範囲の保護をGASで自動で行う方法を書いているので、そちらも合わせて、ご覧ください。結構、直接、編集したくなるシーンが多いんですよね......。

IMPORTHTML

=IMPORTHTML("https://support.google.com/a/table/7539891?hl=ja","table",1)

G Suite の今後のリリース - G Suite 管理者 ヘルプ

tableかlistを選択して.....とありますが、ほぼ、tableでしか使っていません。dlタグとかから抽出出来れば、使用頻度が上がりそう。それは、やり方が分かりません。

IMPORTXML

これは、使いこなせば、凄い武器になる関数の一つ。そして、今回、IMPORTシリーズの関数を書こうと思った、きっかけです。

リンクから、タイトルを調べる（これ、めっちゃ便利 ♪ ）

=IMPORTXML(B2,"/html/head/title")

転職先では、エゴサーチのような業務が増えそうなので、そうした時に使えそう.....。コンテンツの一部を抽出します。

Google Apps Scriptを併用して、リンクが挿入されたら、隣のセルに、IMPORTXML関数を埋め込むスクリプト

function setFormula() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet       = spreadsheet.getSheetByName('競合他社調査');
 const activeSheet = spreadsheet.getActiveSheet();

 //シート名の取得
 const targetSheetName = sheet.getSheetName();
 const activeSheetName = activeSheet.getSheetName();
　
 //シート名が違ったら、処理を終了
 if(targetSheetName !== activeSheetName){
   return
 }
 console.log('指定したシート：%s', targetSheetName);
 console.log('アクティブなシート：%s', activeSheetName);


 //アクティブセルを取得
 const activeCell   = sheet.getActiveCell();
 const activeDetail = {
   row:    activeCell.getRow(),
   column: activeCell.getColumn(),
   value:  activeCell.getValue(),
   range:  activeCell.getA1Notation()
 }
 console.log(activeDetail);
 
 //アクティブセルにhttpsが含まれていたら、隣の列のセルに、関数を埋め込む
 if(activeDetail.value.includes('https') === true){

   const targetColumn = activeDetail.column - 1;
   sheet.getRange(activeDetail.row, targetColumn).setFormula(`=IMPORTXML(${activeDetail.range},"html/head/title")`);

 }else{
   return
 }
}

Googleの検索の1番目の結果だけを取得するスクリプト

みん就（トヨタ自動車）の最新の情報のみを書き出す

=IMPORTXML("https://www.nikki.ne.jp/bbs/7203/?grad_yyyy=2022","//*[@id='bbsItem']/ul/li/div/div")

Yahoo!ファイナンスから、株価情報を抽出する。

=ImportXML(CONCATENATE("http://stocks.finance.yahoo.co.jp/stocks/detail/?code=",A2),"//td[@class='stoksPrice']")

Yahoo!ファイナンスから終値を抽出する。

=IMPORTXML(CONCATENATE("http://stocks.finance.yahoo.co.jp/stocks/detail/?code=",4397),"//*[@id='detail']/div[2]/div[1]/dl/dd")

外国株は、GOOGLRFINANCE関数があり、ダウでも個別株でも情報を簡単に取得する事が出来ます。

=GOOGLEFINANCE(B2)*107

B2のセルにあるティッカーコードの株価を取得し、107円くらいかな？今って。掛けると現在の株価が日本円で算出する事が出来ます。

IMPORTFEED

noteのブログのリンク、タイトル、内容（抜粋）をシートに書き出す

=IMPORTFEED("https://note.com/nepia_infinity/m/m191df585dd35/rss","items",FALSE,100)

余談、urlFetchAppを使って、HTMLタグの特定の要素を抽出してみましたが、望む結果にはなりませんでした。（←もうちょっと実践が必要なんだと思います。）

//みん就にログインする。
function loginBBS() {
 const LOGIN_URL = 'https://www.nikki.ne.jp/bbs/200605151351430142/?l-id=pc_company_tab_bbs';
 const USER_ID   = PropertiesService.getScriptProperties().getProperty('USER_ID');
 const PASSWORD  = PropertiesService.getScriptProperties().getProperty('PASSWORD');
 
 
 //console.log(`USER_ID: ${USER_ID} \nPASSWORD: ${PASSWORD}`);
 // HTTPリクエストのパラメータをobjectで設定
 // POSTで渡すフォームデータはpayloadで指定
 
 const options = {
   method : "post",
   followRedirects: false,
   contentType: "application/x-www-form-urlencoded",
   payload : {
     userId: USER_ID,
     password: PASSWORD
   }
 };
 
 //HTTP 200 OK はリクエストが成功した場合に返すレスポンスコード。
 const response = UrlFetchApp.fetch(LOGIN_URL, options);
 const htmlContents = response.getContentText('utf-8');
 
 console.log(response.getResponseCode());//200 success!!
 //console.log(htmlContents);
 
 //const targetElement = htmlContents.match(/<p>.*<\/p>/g); //NULL
 //const targetElement = htmlContents.match(/<ul class="bbs-list-style"><li class="item">.*<\/li><\/ul>/g); //NULL
 const targetElement = htmlContents.match(/<li class="item">.*<\/li>/g); //TRUE
 console.log(`targetElement ${targetElement}`);
 
 return
}//end