GAS初挑戦で自動化してみたら思った以上に簡単だったという話

Junko_itou

2022年1月16日 14:46

私はとっっても無駄なことをしております！！ｗ
というのも、いろんな家計簿アプリが無料で使えて便利なこのご時世に
スプレッドシートでコツコツ家計簿を記入しているのです。

今までは関数のみでなるべく自動化していたのですが、、
今年はGASを導入します\^^/

今までpythonでの自動化スクリプト作成の経験があり、
GoogleAPIを活用していました！
その場合のコードの内容としては・・こんな感じです。

1.スプレッドシートのIDからシートの全データを３次元配列で読み込む
2.必要な配列を取り出しループで編集
3.編集後の配列をシートに書き込む

ただ、せっかくなら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 にセット！

↑これの５に来る数字をリスト化して、変数 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 には０が代入されているらしい、しかも文字列で！！
ということがわかりました。

０どっからきた？？と調べたところ。
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で単純な繰り返し作業を自動化してみてください☆

この記事が気に入ったらサポートをしてみませんか？