やったこと
GASを使って各サーバーからjsonデータを取得してスプレッドシートの数値と画像を更新する処理を作成してみました。
学んだことを記載します。お役に立てればと思います。
GASマニュアルの見方
リファレンスURL
-
https://developers.google.com/apps-script/reference/calendar/
-
主にGmailやCalenderなど大見出し機能が初めに表示されます。
-
例えばスプレッドシートのセルを操作する場合なら、SpreadSheet > Rangeを表示すれば利用できる機能とサンプルが閲覧できます。
プログラムの作りかた自己流
- 共通する処理はライブラリ化して利用しています。以下はJson処理となります。クラスもどきみたいな感じで、グローバル変数利用しているのがなんか微妙ですがこれでいいかなとw
- ライブラリについてはこのあと説明
json.gs
/**
* グローバル変数
* @const {string} URL
* @const {string} AUTH
*/
var URL;
var AUTH;
/**
* アクセス情報をセット
* @param {string} url Json取得用のアクセス情報
* @const {string} URL
*/
function setUrl(url)
{
this.URL = url;
}
/**
* 認証情報をセット
* @param {number} auth 1:Basic認証
* @const {string} AUTH
* Throws new Error 認証情報が不正な場合に発生
*/
function setAuth(auth)
{
auth = parseInt(auth);
if (auth == 1)
{
this.AUTH = auth;
}
else
{
throw new Error("認証情報を指定してください。");
}
}
/**
* jsonデータ取得
* @return {Array<string>} data jsonデータを返す
*/
function getJson()
{
var options;
switch (AUTH)
{
case 1:
// Basic認証
options = {"headers" : {"Authorization" : " Basic " + Utilities.base64Encode(URL.id + ":" + URL.pw)}};
break;
}
try
{
var response = UrlFetchApp.fetch(URL.url, options);
var data = JSON.parse(response.getContentText());
}
catch(e)
{
// レスポンス200以外の場合
var error = e;
throw new Error("Jsonデータ取得に失敗しました。ログを確認してください。");
Logger.log("message:" + error.message + "\nfileName:" + error.fileName + "\nlineNumber:" + error.lineNumber + "\nstack:" + error.stack);
}
return data;
}
- Jsonを呼び出す側
hoge.gs
/**
* mJsonという名前でJsonライブラリ登録
* @const {string} JSON
*/
mJson.setUrl(URL);
mJson.setAuth(1);
var JSON = mJson.getJson();
function updateHoge1()
{
// Jsonデータ利用
Logger.log(JSON['test'].hoge);
}
funciton updateHoge2()
{
// Jsonデータ利用
Logger.log(JSON['test'].hoge);
}
スプレッドシートを利用する
- openByIdはスプレッドシートの以下の部分に記載されています。
- URLの"~/d/*****/edit"のアスタリスクの部分がスプレッドシートのID
- シート名でも指定できますが、名前変えられる場合を考慮しました。
- getSheets()[0]この0は最初のシート番号となり0から始まります。その後1づつ加算すれば別のシート指定も可能です。
// 対象のスプレッドシートを指定
var hoge = SpreadsheetApp.openById('xxxxxxxxxx');
// 利用するシートをアクティブ
var sheet = hoge.getSheets()[0];
# セルに値をセット
// セルO23に'テスト'をセット
sheet.getRange('O23').setValue('テスト');
版の管理からライブラリを作成する
-
版の管理から、変更内容を任意で入力します。私の場合はそのプログラム単位名みたいな感じで登録しています。
-
プロジェクトのプロパティで表示されている、プロジェクトキーをコピーしておきます。
-
ライブラリ画面で、ライブラリ検索にコピーしたプロジェクトキーを指定し、選択を押します。
-
先ほど版の管理で登録した名前が表示されます。登録したバージョンとGASで利用するライブラリと識別子を指定して保存で完了です。
-
他のスプレッドシートのGASから登録したライブラリを利用する場合は、ライブラリ画面で先ほどのプロジェクトキーを指定すれば利用できます。
HipChatに送信
- GasからHipChatへ通知したいと思ったので、API2を利用してライブラリを作りました。
- パラメータを渡せば利用できます。よかったら利用してください。
hipchat.gs
/**
* グローバル変数
* @const {string} ROOM_ID
* @const {string} AUTH_TOKEN
* @const {Array<string>} PARAM
*/
var ROOM_ID;
var AUTH_TOKEN;
var PARAM;
/**
* HipChatのRoomIDをセット
* @param {string} room_id HipChatのRoom通知用ID
* @const {string} ROOM_ID
*/
function setRoomeId(room_id)
{
this.ROOM_ID = room_id;
}
/**
* HipChatのRoomアクセストークンをセット
* @param {string} auth_token
* @const {string} AUTH_TOKEN
*/
function setAuthToken(auth_token)
{
this.AUTH_TOKEN = auth_token;
}
/**
* HipChatへの各パラメータをセット
* @param {Array<string>} param
* @const {Array<string>} PARAM
var sample = {
'color' : 'green',
'message' : 'test',
'message_format' : 'text',
'notify': false
};
*/
function setParam(param)
{
this.PARAM = param;
}
/**
* HipChatへ通知（API Version2）
*/
function send()
{
var url = 'https://api.hipchat.com/v2/room/' + ROOM_ID + '/notification';
var options = {
'contentType' : 'application/json',
'headers' : {
'Authorization' : 'Bearer ' + AUTH_TOKEN
},
"method": "post",
"payload" : JSON.stringify(PARAM)
};
try
{
UrlFetchApp.fetch(url, options).getContentText();
}
catch(e)
{
var error = e;
throw new Error("HipChat通知に失敗しました。ログを確認してください。");
Logger.log("message:" + error.message + "\nfileName:" + error.fileName + "\nlineNumber:" + error.lineNumber + "\nstack:" + error.stack);
}
}
連想配列でのfor文
// 動物達
var animal = {
'cat' : 1,
'dog' : 2
};
// 場所など
var place = {
'cat' : {
house : 'ベランダ',
food : '猫まっしぐら猫缶'
},
'dog' : {
house : '犬小屋',
food : 'ドッグフード特大サイズ'
}
};
for (var category in animal)
{
Logger.log(place[category].house);
Logger.log(place[category].food);
}
外部ライブラリを利用する
- googleが公開している便利なライブラリもあります。
- 今回利用したmoment.jsを例にしています。主に日付や時刻操作系のライブラリが詰まっています。
- ライブラリから以下のプロジェクトキーを指定すれば利用可能になります。
moment.js
Project Key : MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48
画像を取得する
var url = 'https://www.google.co.jp/images/srpr/logo11w.png';
// セルA1に画像を貼り付ける
sheet.insertImage(url, 1, 1);
セルの削除
- セルの削除は行と列単位でできるのですが、範囲指定した場合は切り取りで行うようです。
// A2からB5まで切り取り、A1に貼り付け。A1からA5までを削除した感じとなります。
sheet.getRange('D23:O37').moveTo(sheet.getRange('C23'));
セルの書式コピーして貼り付け
// M23からM37までの書式をコピーして、列15の23行から37行に貼り付け
var pv_range = sheet.getRange('M23:M37');
pv_range.copyFormatToRange(sheet, 15, 15, 23, 37);
セルのボーダセット
// セルO99からO113の範囲で右側ボーダセット
range = sheet.getRange('O99:O113');
range.setBorder(null, null, null, true, null, null);
他にもいろいろできるので、マニュアルなどを参考にしてみてくださいー