woshidan's blog

あいとゆうきとITと、とっておきの話。

Google App Scriptでセルを扱う

Google App Script

はてなが俺たちのはてなじゃない...。

諸々の集計業務を担当することになって、コピペを効率化しようと思ってGoogle App Scriptを調べたのでまとめます。

内容

特定のセルの値を取得
特定のセルに値を貼り付け
他のファイルのソースコードを参照するときの手順
確認用ダイアローグを出す
シートを開いたときに行う処理
参考

特定のセルの値を取得

// 単一の値を取得
SpreadsheetApp.getActive().getSheetByName("SHEET_NAME").getRange("A1").getValue();

// 現在アクティブなシートを取得する場合
SpreadsheetApp.getActiveSpreadsheet()).getRange("A1").getValue();

// 特定の範囲の中の値を取得する場合
// 2次元配列が帰ってきます
SpreadsheetApp.getActive().getSheetByName("SHEET_NAME").getRange("A1:B6").getValues();

特定のセルに値を貼り付け

// 単一のセルに貼り付けというのはなく、範囲から範囲への貼り付けとなる
var rangeToCopy = sheet.getRange('A:A');
var targetToCopy = sheet.getRange('B1'); // 貼り付け範囲か、一番左上のセルを指定するようにしておく
rangeToCopy.copyTo(targetToCopy);

// 形式のみ, 値のみのオプションについて
rangeToCopy.copyTo(targetToCopy, {formatOnly:true}); // 形式のみ
rangeToCopy.copyTo(targetToCopy, {contentsOnly:true}); // 値のみ

// 2次元配列を特定の範囲へ貼り付け
var copyValues = [[1, 2], [3, 4]];
SpreadsheetApp.getActive().getSheetByName("SHEET_NAME").getRange("A1:B2").setValues(copyValues);

他のファイルのソースコードを参照するときの手順

新しいプロジェクトを作成して分割したいソースコードを貼る
1のプロジェクトに対して、 ファイル > 版で管理 から、他のプロジェクトに公開するバージョンのコメントを入れて保存
2のプロジェクトのソースコードを参照したいプロジェクトを開いて、リソース > ライブラリ を選択
3で開いたメニューの下の方に、2のプロジェクトのURLの一部 https://script.google.com/macros/d/SomeLooooongRandomString/edit?ui...の SomeLooooongRandomStringの部分を入れる
入力した文字列が正しければ、2のプロジェクト名が3のメニューに現れるので、2のプロジェクトを利用する側で使うバージョンと、2のプロジェクトの関数などの前につく識別子を入力して、「保存」を押す
2のプロジェクトを利用する側で、5で決めた識別子を頭につけて2のプロジェクト内の関数を利用する

確認用ダイアローグを出す

// メッセージだけでる
Browser.msgBox("メッセージが出ます");

// OK/キャンセルのボタンがついたダイアローグが出る
Browser.msgBox("これからシートを編集しますか?", Browser.Buttons.OK_CANCEL);

// 選択されたボタンを取得
var selections = Browser.msgBox("これからシートを編集しますか?", 
                        Browser.Buttons.OK_CANCEL);
// キャンセルボタンをクリックした場合は"cancel"
Browser.msgBox(selections);

シートを開いたときに行う処理

// シートを開いたときにダイアローグを出す
function onOpen(){
  Browser.msgBox("これからシートを編集しますか?", Browser.Buttons.OK_CANCEL);
}

// シートを開いたときに上部にメニューを追加する
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {
      name : "シートの上部にに追加するメニュー内の項目の名前",
      functionName : "その項目を選択したとき起動する関数"
    }
  ];
  sheet.addMenu("シートの上部に追加するメニューの名前", entries);
}

参考

結局なのですが、普通にGoogle Spread Sheetの関数を使ってCSVインポートからのコピペ前の整形作業用の単純なシートを何枚か作ったらそれだけで大幅に時間が短縮されてしまい、Google App Scriptの動作が遅かったため、2日程度で0からほとんど動くところまで書いたのに導入までできなかったのが残念です...。

暇なときにもう少し手直ししてリベンジしたい...。