GAS初挑戦で自動化してみたら思った以上に簡単だったという話
私はとっっても無駄なことをしております!!w
というのも、いろんな家計簿アプリが無料で使えて便利なこのご時世に
スプレッドシートでコツコツ家計簿を記入しているのです。
今までは関数のみでなるべく自動化していたのですが、、
今年はGASを導入します\^^/
今までpythonでの自動化スクリプト作成の経験があり、
GoogleAPIを活用していました!
その場合のコードの内容としては・・こんな感じです。
ただ、せっかくならGAS使ってみたいな〜という動機で
・python自動化経験あり
・JavaScript未経験
の私が挑戦してみました〜
まずは年始に必須作業!
シートの初期化を自動化です((o(´∀`)o))ワクワク
◆まずはマクロ記録から
手作業をそのままマクロに登録してくれる機能を活用して、
この作業をプログラム化するとどんなん?を見てみます。
ー私の環境
PC : Chromebook
OS : ChromeOS
ブラウザ : バージョン : 96.0.4664.111(Official Build) (64 ビット)
ー記録したい操作
セル「C6~06」をコピー
セル「C5~O6」にペースト
セル「C6~06」をクリア
ーレッツ!マクロに記録!!
まずはC5をクリックした状態でスタンバイ
スプレッドシート メニューバー > 拡張機能 > マクロ > マクロを記録 をポチッ
C6~O6 をセル指定してコピー
C5 を指定してペースト
C6~O6 を指定して削除
新しいマクロを記録〜の【保存】をポチッ
ーどれどれ・・どんなコードになったのかしら?
getRange ってどんなことできるんだろう??と、気にはなりつつ解読
var spreadsheet = SpreadsheetApp.getActive(); //spreadsheetの変数を定義
spreadsheet.getRange('C5').activate(); //カーソルは C5 にセット!
spreadsheet.getRange('C6:O6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
//getRange で指定した範囲をコピーしてアクティブセル(今はC5がアクティブ)に数値のみ貼り付け
spreadsheet.getRange('C6:O6').activate(); //アクティブセルを C5 から C6:O6 に変更
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
//アクティブセルの内容を削除
ということでしょう!!
スプレッドシートの状態を編集前に戻して
ースクリプト実行〜
ちゃんと記録できていて自動化できました\^^/
めっちゃ簡単!マクロ記録ばんざーい!
でもここからが本番!!
この作業×17の自動化するぞーー
◆複製して作業範囲を拡大
でもここからが本番!!
この作業×17の自動化するぞーー
とりあえず記録で作成されたコードをひたすらコピペ
「C5 → C7」 に変えて〜
「C6:O6 → C8:O8」 に変えて〜
というローラー作戦!!
コピペして〜変えて〜コピペして〜変えて〜・・・・・
めっちゃめんどくさい!!!
ーループ作成
なんだ!この、自動化しようとしているのに
アナログ感のある作業はっっ!!
そうか!!リストを作ってループで回せばいいのか〜ということで・・
最初にアクティブにする行をリストアップ
spreadsheet.getRange('C5').activate(); //カーソルは C5 にセット!
↑これの5に来る数字をリスト化して、変数 array に代入しておく
var array =[35,33,11,5,7,9,13,15,17,19,21,23,25,27,29,31,39];
arrayリストから数字をひとつずつ取り出して
for(var i in array){};
リストから取り出した数字を使ってセル指定できるように編集〜
最初の 'C5' は 'C'+i.toString(); になるはずこれを変数 pastecell に代入
var pastecell ='C'+i.toString();
次にのコピーするセル指定範囲を編集〜
最初に取り出すのは「35」で指定したいのは「C36:O36」なので・・・
array[i]+1を c に代入して
’C'+c.toString+':O'+c.toString を copycell に代入
var c = i+1 ;
var copycell = 'C'+c.toString()+':O'+c.toString();
この pastecell と copycell をセル指定するところに入れて・・・・・
ー失敗(T_T)
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
var array =[35,33,11,5,7,9,13,15,17,19,21,23,25,27,29,31,39];
for(var i in array){
var pastecell ='C'+i.toString();
var c = i+1 ;
var copycell = 'C'+c.toString()+':O'+c.toString();
spreadsheet.getRange(pastecell).activate();
spreadsheet.getRange(copycell).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange(copycell).activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};
};
いざ実行すると早速エラー(T_T)
14:02:39 エラー Exception: Range not found
え?なんで?と思い、
Logger.log(pastecell);
Logger.log(copycell);
のコードを追加。
途中の変数の動きをログに出力してみると
14:02:39 お知らせ 実行開始
14:02:40 情報 C0
14:02:40 情報 C01:O01
14:02:39 エラー Exception: Range not found
なるほど〜〜〜〜〜〜〜
'C'+i = C0 で i+1 が 01 ということは・・・
i には0が代入されているらしい、しかも文字列で!!
ということがわかりました。
0どっからきた??と調べたところ。
jsの場合ループはインデックスが代入されるらしい。
【pythonの場合】
for i in [5,6,7];
print(i)
こうすると 5 6 7 と出力されるんですが、
【jsの場合】
for i in [5,6,7];
Logger.log(i)
これだと 0 1 2 とインデックスが出力される仕様になっている
pythonでいうところの
for i in range(len([5,6,7]));
print(i)
と同じということになるようです。
仕様の違いからくる勘違いでした〜〜面白いですね!!
なので、リストのインデックス番目を参照に修正〜〜
var pastecell ='C'+i.toString();
var c = i+1 ;
この部分を・・・・・
var pastecell ='C'+array[i].toString();
var c = array[i]+1 ;
修正完了(`・ω・´)ゞ
ーレッツループ
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
var array =[35,33,11,5,7,9,13,15,17,19,21,23,25,27,29,31,39];
for(var i in array){
var pastecell ='C'+array[i].toString();
var c = array[i]+1 ;
var copycell = 'C'+c.toString()+':O'+c.toString();
spreadsheet.getRange(pastecell).activate();
spreadsheet.getRange(copycell).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange(copycell).activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};
わーーーーーーーい!!うまく動いたーーーー!感動です。
ぱぱっと!!1秒(たぶん)で終わりました。
思った通り動いてくれると感動ですねぇ〜。・゚・(ノ∀`)・゚・。
◆思ったより簡単!挑戦してみて〜
GAS、思ったより簡単でした!
なんと言っても「マクロに記録」の機能が便利ですっ!
これ、プログラミングの知識なくても組めそうです。
とりあえず自動化したい作業を小分けに「マクロに記録」してみて、
編集しながら適応範囲を広げていけば初心者でもできる!
あとは、ちょっとでも楽するために
for や While などの仕組みを知っておくと便利です。
検索してみてくださいね〜
◆気になるGAS
絶対参照、相対参照を変えることで何ができるんだろう〜?
GASでスクレイピングもできるらしいけど、どうやるんだろう〜?
メールの送信もできるっぽいけど、どうやるんだろう〜?
トリガーってあるし、定期実行も組めそう、どうやるんだろう〜?
自分の生活にどのように活用できるかいろいろ気になります(・∀・)ニヤニヤ
現状、我が家の家計簿はまだまだ手作業も多いので、
関数では難しい部分に今年は
GASでメスを入れていきたいと思います!(-ω☆)キラリ
ぜひ、みなさんもGASで単純な繰り返し作業を自動化してみてください☆
この記事が気に入ったらサポートをしてみませんか?