矩形に関数を割り当てる方法、別シートの操作などを理解する。
システム要件・完成目標とイメージ
- bloggerを更新したとき、ping送信を簡単に行えるようなツールを作成する
- ping送信するURLの個数に制限はしない
- データは別シートにまとめて、管理シートにボタンを設置する
- 更新がうまくいったかどうかエラーチェックをする
スプレッドシートの準備
スプレッドシート名は任意で、2つのシートを用意します。「manage」シート。この後ボタンを挿入します。レイアウトはお好きにどうぞ。 「data」シート。pingURLは可変にしますので、要素的には最後に置いた方がスマートです。
矩形(図形)に関数を割り当てる
まず矩形を追加します。メニューから「挿入」→「図形描画」を選択すると、「図形描画」ウインドウが起動します。 図形を描きます。好みで良いです。今回は角丸四角形で作りました。
適当に色も変え、ダブルクリックでテキストの追加もしました。
納得いく出来になったら「保存して終了」を押して終了します。 表示位置を整え、矩形右上の「・」が縦に3つ並んだボタンを押し、「スクリプトを割り当て」を選択します。 スクリプトファイルすらまだ追加していませんが、デフォルトで「myFunction」だったはずなので、とりあえず「myFunction」にしてみます。
※もし気に食わなければ、自分の好きな関数を作ったうえで、修正をかけてください。 これで、矩形をクリックすると動作する関数の入り口が準備できました。
スクリプトエディタを起動し、プロジェクト名を適宜入力しましょう。次からコードを書いていきます。
【GAS】GAS(Google App Script)の導入手順
Google App Script 導入手順
ソースコード
// ----------------------------------------------------------------------------- // ping送信テスト. // ----------------------------------------------------------------------------- var MngSheetName = "manage"; // 管理シート名. var DataSheetName = "data"; // データ一覧シート名. // manageシート. var MngSelectTitle = "C2"; // 更新するブログ名. var MngDebugLogX = 3; // 結果表示列. var MngDebugLogY = 11; // 〃 行. // dataシート. var DataUpdateX = 1; // 更新日記録列. var DataBlogTitleX = 2; // ブログタイトル列. var DataBlogURLX = 3; // ブログURL列. var DataBlogPingX = 4; // pingURL列. // ----------------------------------------------------------------------------- // myFunction // 外部から呼び出される関数. // ----------------------------------------------------------------------------- function myFunction() { var mngSheet = GetSheet( MngSheetName ); var dataSheet = GetSheet( DataSheetName ); var indexY = getIndexY( dataSheet, DataBlogTitleX, mngSheet.getRange( MngSelectTitle ).getValue() ); var logY = MngDebugLogY; var urls = []; var data = []; // ブログタイトルが見つからなかった. if ( indexY === -1 ) { mngSheet.getRange( MngDebugLogY, MngDebugLogX ).setValue( mngSheet.getRange( MngSelectTitle ).getValue() + "が見つかりませんでした" ); return; } // ping送信先を追加していく. var endX = dataSheet.getRange(indexY,dataSheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn(); for ( var i = DataBlogPingX; i <= endX; ++i ) { urls.push( dataSheet.getRange( indexY, i ).getValue() ); } // data.push('<?xml version="1.0"?>'); data.push('<methodCall>'); data.push('<methodName>weblogUpdates.ping</methodName>'); data.push('<params>'); data.push('<param><value>' + dataSheet.getRange(indexY,DataBlogTitleX).getValue() + '</value></param>'); data.push('<param><value>' + dataSheet.getRange(indexY,DataBlogURLX).getValue() + '</value></param>'); data.push('</params>'); data.push('</methodCall>'); // 送信内容. var options = { method: "POST", payload: data.join('\n'), muteHttpExceptions : true }; // 送信実行. var response = 0; for (var i=0; i < urls.length; i++) { response = UrlFetchApp.fetch( urls[i], options ); // responseが200番台なら成功. // 400番台ならクライアントエラー、500番台ならサーバエラー. mngSheet.getRange( logY, MngDebugLogX ).setValue( urls[i] ); ++logY; if ( (400 <= response.getResponseCode()) && (response.getResponseCode() < 600)) { // 失敗か?とりあえずlogだけ残す. mngSheet.getRange( logY, MngDebugLogX ).setValue( "エラー:" + response.getResponseCode() ); ++logY; } } // 更新日の記録. dataSheet.getRange( indexY, DataUpdateX ).setValue( Utilities.formatDate( new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm') ); // 送信内容の確認. for (var i = 0; i < data.length; i++) { mngSheet.getRange( (logY + i), MngDebugLogX ).setValue( data[i] ); } } // ----------------------------------------------------------------------------- // 現在のスプレッドシートの中の指定の名前のシートを取得. // ----------------------------------------------------------------------------- function GetSheet( sheetName ) { return SpreadsheetApp.getActive().getSheetByName( sheetName ); } // ----------------------------------------------------------------------------- // Y取得. // ----------------------------------------------------------------------------- function getIndexY( sheet, indexX, defineName ) { var listEnd = sheet.getRange(sheet.getMaxRows(), indexX).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); for ( var j = 2; j <= listEnd; ++j ) { if ( sheet.getRange( j, indexX ).getValue() === defineName ) { return j; } } // 見つからなかった. Logger.log( defineName + "が見つかりません" ); return -1; }
実行結果
「更新ブログ」の入力部分はデータの入力規則でリスト選択形式にしました。解説
35行目・89行目:
var mngSheet = GetSheet( MngSheetName ); var dataSheet = GetSheet( DataSheetName ); ~~~ function GetSheet( sheetName ) { return SpreadsheetApp.getActive().getSheetByName( sheetName ); }【GAS】実践・スクレイピングでは、シートIDからスプレッドシートを操作する方法を取りましたが、IDをいちいち取るのは面倒なので、GetSheet()で名前からシートを取得・操作します。
使い分けとしては、組み込まれたGASなら名前から。外部からスプレッドシートを指定する場合などはIDから。という感じでしょうか。
27行目・95行目:
var indexY = getIndexY( dataSheet, DataBlogTitleX, mngSheet.getRange( MngSelectTitle ).getValue() ); ~~~ function getIndexY( sheet, indexX, defineName ) { var listEnd = sheet.getRange(sheet.getMaxRows(), indexX).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); for ( var j = 2; j <= listEnd; ++j ) { if ( sheet.getRange( j, indexX ).getValue() === defineName ) { return j; } } // 見つからなかった. Logger.log( defineName + "が見つかりません" ); return -1; }getIndexY()でmanageシートで指定したブログ名から送信するデータの行番号を取得しています。
単なる総当たり処理です。
33行目:
if ( indexY === -1 ) { mngSheet.getRange( MngDebugLogY, MngDebugLogX ).setValue( mngSheet.getRange( MngSelectTitle ).getValue() + "が見つかりませんでした" ); return; }ブログ名の検索に失敗したらログ表示用に取ったエリアにその旨を入力し、処理を終了します。
39行目:
var endX = dataSheet.getRange(indexY,dataSheet.getMaxColumns()).getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn(); for ( var i = DataBlogPingX; i <= endX; ++i ) { urls.push( dataSheet.getRange( indexY, i ).getValue() ); }pingURLが入力されているセルがどこまであるかチェックして、urls[]に追加していきます。
45行目:
data.push('<?xml version="1.0"?>'); data.push('<methodCall>'); data.push('<methodName>weblogUpdates.ping</methodName>'); data.push('<params>'); data.push('<param><value>' + dataSheet.getRange(indexY,DataBlogTitleX).getValue() + '</value></param>'); data.push('<param><value>' + dataSheet.getRange(indexY,DataBlogURLX).getValue() + '</value></param>'); data.push('</params>'); data.push('</methodCall>'); // 送信内容. var options = { method: "POST", payload: data.join('\n'), muteHttpExceptions : true };この辺りは決まり事、という事で。詳しく知りたい方はこちら(ブログ更新ping(weblogUpdates.ping)のxmlrpcの仕様:infoScoop開発者ブログ)
62行目:
var response = 0; for (var i=0; i < urls.length; i++) { response = UrlFetchApp.fetch( urls[i], options ); // responseが200番台なら成功. // 400番台ならクライアントエラー、500番台ならサーバエラー. mngSheet.getRange( logY, MngDebugLogX ).setValue( urls[i] ); ++logY; if ( (400 <= response.getResponseCode()) && (response.getResponseCode() < 600)) { // 失敗か?とりあえずlogだけ残す. mngSheet.getRange( logY, MngDebugLogX ).setValue( "エラー:" + response.getResponseCode() ); ++logY; } }UrlFetchAppでPOSTします。
返ってくるgetResponseCode()で成功しているかどうかチェックしていますが、ちょっとザルすぎるかもしれません。
77行目:
dataSheet.getRange( indexY, DataUpdateX ).setValue( Utilities.formatDate( new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm') );日付の記録です。フォーマットを色々いじってみると面白いです。
80行目:
for (var i = 0; i < data.length; i++) { mngSheet.getRange( (logY + i), MngDebugLogX ).setValue( data[i] ); }dataの中身を念のため確認しておきます。