LoginSignup
70
82

More than 3 years have passed since last update.

@minamisatomi(minami satomi)inFignny Co.,Ltd.

総務がGASで請求書業務を半自動化してみた。

Last updated at Posted at 2021-03-22

◎登場人物紹介◎

総務
現在22歳。入社1年3ヶ月。フィグニー唯一の総務。
月末と月初は請求業務と給与計算に追われている。心配性な性格のため業務中は頭と胃が痛いことが多い。好きな食べ物は和菓子の練りきり。

柱
現在21歳。
運良くフィグニーに入社して早1年。
朝から晩までコードを書いている。今はインフラの沼に飲み込まれてる。好きな作業場所はソファ。


総務：請求書業務自動化したい！！！！！


さて... 第1回目の本日は、「総務がGASで請求書業務を半自動化してみた。」です！！

弊社は開発会社なのですが、バックオフィス業務はIT化が遅れている部分が多々あります。その最たるものが請求書業務です！
「SalesForce」「らくらく明細」をはじめとした便利なツールは有料で社長の決裁が下りなかったので（ケチ）、自分で作ることにしました。

総務一人では作れないのでサポートエンジニアがついて教えてくれます！
2年後にはチームの柱になる 郷将輝くん（以下「柱」という）です！
minami.png
総務：よろしくおねがいします！！
masaki.png
柱：よろしくお願いします。

1.目標

下記のように手動で行っていたことをGASを書いて自動化させていくのが目標です。

①案件リスト（スプレッドシート）のステータスが[作成待ち]になっている案件の数を確認
②案件の数だけ雛形請求書（スプレッドシート）を手動コピー
③案件ごとに案件リスト内の［請求日］［入金予定日］［顧客名］を請求書の該当する箇所にコピペ（それ以外は発注書等を見て入力するので今回は自動化しない）
④請求書ファイル名を手動で変更して請求書管理フォルダに移動
eb5677dc-4734-4a15-9577-747910a0e569.png
64d42f30-f6bd-4262-b66a-c34a84b82f70.png

2.事前準備

下記のように２つのテスト用テンプレートと１つのテスト用フォルダを作成しました。

・案件リスト（スプレッドシート）
・雛形請求書（スプレッドシート）
・完成したスプレッドシートが入るフォルダ


masaki.png
柱：事前準備は終わったので、GASのコードを書きましょう。
minami.png
総務：いよいよここから未知の領域ですね！！

3.手順

流石に総務は素人のためいきなり一人ではかけません。
そのため以下の手順で取り組んでいくことにしました。

①ProgateでJavaScriptを学ぶ。
②とりあえず調査。（検索力が試される。）
③柱に教えてもらう。

masaki.png
柱：それでは書いてみましょう。

4.GASでスクリプトを書く

①案件リスト（スプレッドシート）を開く
②上部メニュー「ツール＞スクリプト エディタ」を選択
③コードを入力

以下が今回書いたコードです。

// Spreadsheetが開かれた時に自動的に実行
function onOpen() {

// 現在開いている、スプレッドシートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// メニュー項目を定義
var entries = [
  {name : "請求書作成",functionName : "create"}];

// 「書類作成」という名前でメニューに追加
spreadsheet.addMenu("書類作成", entries);
}

function create(){

 // 現在開いている、スプレッドシートのシートを取得
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sh = spreadsheet.getActiveSheet();

 //ステータス列を取得
 var range_list = sh.getRange(2, 12, sh.getLastRow()-1).getValues();

 //2次元配列を1次元配列にする
 var editarray = Array.prototype.concat.apply([],range_list);

 //ステータス列のデータの値を取得
 for(var i = 0; i < editarray.length; i++){

  if(editarray[i] === "作成待ち"){

    //作成待ちの行を取得
    var cell = sh.getRange("L"+(i+2))
    var row = cell.getRow();

    //作成待ちの行の特定のセル（項目）の値を取得
    var rof = "A" + row + ":" + "F" + row;  
    var cell2 = sh.getRange(rof).getValues();

    //2次元配列を1次元配列にする
    var ss = cell2[0];

    // 請求書Noの表記を変更して取得
    var a = ss[0];
    invoice = Utilities.formatDate(a,"JST", "yyyyMMdd");
    var invoiceNo = invoice + (i+1)
    // 請求日の表記を変更して取得
    billingdate = Utilities.formatDate(a,"JST", "yyyy/MM/dd");
    // 入金期限の表記を変更して取得
    var b = ss[3];
    depositdate = Utilities.formatDate(b,"JST", "yyyy/MM/dd");
    // 会社名の値を取得
    var company = ss[5];

    //雛形の請求書（スプレッドシート）
    var templateFile = DriveApp.getFileById("xxxxxxxxxxxxx");
    // 完成したスプレッドシートが入るフォルダ
    var OutputFolder = DriveApp.getFolderById('xxxxxxxxxxxxx');
    // 出力ファイル名
    var CopiedFile = templateFile.makeCopy( "請求書_"+ invoice + "_"+ company +"_Fignny", OutputFolder );
    // コピーしたシートのID取得
    var CopiedFileId = CopiedFile.getId(); 
    //値を貼り付けする請求書
    var ss_copyTo = SpreadsheetApp.openById(CopiedFileId);

    //貼り付けするセル指定
    ss_copyTo.getRange("H10:Q10").setValue(invoiceNo);
    ss_copyTo.getRange("H11:Q11").setValue(billingdate);
    ss_copyTo.getRange("H12:Q12").setValue(depositdate);
    ss_copyTo.getRange("C6:O7").setValue(company);

    //[作成待ち]を[作成済み]に変更
    var completerow = "L" + (i+2)
    sh.getRange(completerow).setValue("作成済み");
}
}
}

今回は総務のチャレンジ企画なのでブロックごとに見ていきます。
ここから長くなりますがお付き合い下さい。（笑）

案件リスト（スプレッドシート）の上部に「書類作成」メニューを追加

minami.png
総務：一番最初に行うのはこれですね。調査してみます！！

柱：この処理はGAS側で書き方が定義されているものなので調べてみましょう。

総務：（　調査中・・・　）

総務：...うわああ！できた！！（すごい！感動！感激！）
　　　※素人なので一喜一憂します。

// Spreadsheetが開かれた時に自動的に実行
function onOpen() {

// 現在開いている、スプレッドシートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// どのようなメニュー項目の名前か、そのメニュー項目を押下した時どのような処理をするかを決る
var entries = [
  {name : "請求書作成",functionName : "create"}];

// 「書類作成」という名前でメニューに追加
spreadsheet.addMenu("書類作成", entries);
}

72de3622-083e-4d59-a4a6-6d065d8a9259.png
masaki.png
柱：[請求書作成]が押下されたときに行う処理をfunctionNameというのに指定しました。
　　今回はcreateとしましたが任意の名前でOKです。次はcreateという関数の処理を書きましょう。

総務：なるほど！！

柱：まずはやりたいことを頭の中で整理しましょう。

minami.png
総務：はい。案件リストのステータスが[作成待ち]の案件だけ、請求書を作成したいから・・・。742a850d-eca9-4952-9527-ea80b3d5e97b.png
総務: L列のステータスの値を取得したいですね。

ステータス列の値を取得

minami.png
総務：（　調査中・・・　）

総務：できました！！

 //ステータス列を取得
 var range = sh.getRange("L2:L9").getValues();
 console.log(range);

masaki.png
柱：L2:L9というのを固定してしまいますと、例えば次の月は案件が一個増えた場合L2:L10となってしまいますが、毎回変えるのですか？

総務：んー、確かに...。ちょっと調査してみます！！
minami.png
総務：記事にこんな内容が載ってました！

行番号は、いつも2行で一定というわけではなく可変です。
そこで、getLastRowメソッドを使って、シート上にデータのある最終行の行番号を取得するようにします。

柱：そうですね。これで書いてみましょう。

総務：できたかな？

 //ステータス列を取得
 var range = sh.getRange(2, 12, sh.getLastRow()).getValues();
  console.log(range);


ログ
[ [ '作成待ち' ],
  [ '作成待ち' ],
  [ '作成待ち' ],
  [ '作業中' ],
  [ '作成済み' ],
  [ '作業中' ],
  [ '作業中' ],
  [ '作業中' ],
  [ '' ] ]

minami.png
総務：おー！これが配列か！！...あれ？？何故か最後に空白があります。

柱：空白配列ですかね。getLastRowをログに出力してみましょう。

ログ　9

総務：９...？？
masaki.png
柱：先程参考にした記事を最後までよく見るとこのような記載があります。

今回の例では、データのある最終行数は3です。
ただ、見出し行を除きたいので、マイナス1をして、結果として2が行数の指定となるべき、ということです。

総務：ハッ！！なるほど！！案件リストにも見出しがある！

総務：できた！！

 //ステータス列を取得
 var range = sh.getRange(2, 12, sh.getLastRow()-1).getValues();
  console.log(range);

ログ
[ [ '作成待ち' ],
  [ '作成待ち' ],
  [ '作成待ち' ],
  [ '作業中' ],
  [ '作成済み' ],
  [ '作業中' ],
  [ '作業中' ],
  [ '作業中' ] ]

minami.png
総務：...箱の中に箱が複数ある状態だ。これが二次元配列か〜。

柱：二次元配列より一次元配列の方がシンプルに値を取り出せるから二次元配列を一次元配列にしてみましょう。

総務：（調査中・・・。）

総務：できました！！
※配列だとログで理解したので変数のrangeはrange_listに変更しました。

 //2次元配列を1次元配列にする
 var editarray = Array.prototype.concat.apply([],range_list);
  console.log(editarray);

ログ [ '作成待ち', '作成待ち', '作成待ち', '作業中', '作成済み', '作業中', '作業中', '作業中' ]

masaki.png
柱：OKですね。ここまでは列のデータを配列として取得しただけなので、ここから値を取得していきましょう。

柱：月ごとで案件数は違うので、最後の案件までの値を取得してそこまでfor文でループを回せばいいと思います。

柱：先程一次元配列にしたものを変数editarray(↓)にしましたね。それを使うんですよ！

ログ [ '作成待ち', '作成待ち', '作成待ち', '作業中', '作成済み', '作業中', '作業中', '作業中' ]

minami.png
総務：なるほど！配列を見るとわかりやすいな〜。配列ではインデックス番号は0からスタートだから.....

総務：苦戦したけど、できた！！全然理解できなかったfor文が実用できたー！（涙）

 //ステータス列のデータの値を取得
 for(var i = 0; i < editarray.length; i++){
  console.log(editarray[i]);

ログ
   作成待ち
   作成待ち
   作成待ち
   作業中
   作成済み
   作業中
   作業中
   作業中

minami.png
総務：次は請求書内にコピペする案件リスト内の特定の項目の値が欲しいなぁ。
あくまでも請求書を作成するのはステータスが[作成待ち]のものだけなので...（赤枠）
07079011-66af-430b-8f6a-106e5592f2d2.png
masaki.png
柱：if文でステータスが［作成待ち］の時の行を取得してから特定のセル（項目）の値を取得する処理をかけばできるのでは？

総務：（？？？）そういうことか....！理解するのに時間かかりました（笑）

ステータスが[作成待ち]の行を取得

masaki.png
柱：配列の中の値をひとつずつ見ていき、作成待ちのものを◯◯するという処理をかきたいのでfor文の中にif文をかきましょう。
minami.png
総務：なるほどこれが条件分岐ってやつですか（汗）

総務：配列editarrayではインデックス番号は0からスタート、スプレッドシートの行数に合わせるには変数 i に＋2（見出しがあるため）をすれば［作成待ち］のセルの範囲を取得することができるから、行数も取得できるのか。

総務：なんとかできました...。（※最初は＋2をせずに違う行数を取得していました...。）

 //ステータス列のデータの値を取得
 for(var i = 0; i < editarray.length; i++){
  console.log(editarray[i]);

  if(editarray[i] === "作成待ち"){

    //作成待ちの行を取得
    var cell = sh.getRange("L"+(i+2))
    var row = cell.getRow();
    console.log(row);

ログ
　作成待ち
  2
　作成待ち
　3
　作成待ち
　4
　作業中
　作成済み
　作業中
　作業中
　作業中

作成待ちの行の特定のセル（項目）の値を取得

minami.png
総務：欲しいのは、［請求日］［入金予定日］［顧客名］なのでA列からF列まで。
　　　 例えば、見出しを除いて一行目が［作成待ち］だったら、A2:F2ということになるのか...
でも行数は可変するので（二行目だったらA3:F3.....)どうすればいいんだろう。
masaki.png
柱：A列とF列という文字と、先程、作成待ちの行を取得した変数rowを連結させれば….

総務：そうか。それでできるのか。（Progateでやった記憶がある模様）

総務：すごい！取得できてる！！

    //作成待ちの行の特定のセル（項目）の値を取得
    var rof = "A" + row + ":" + "F" + row;  
    var cell2 = sh.getRange(rof).getValues();
    console.log(cell2);

ログ
作成待ち
 [ [ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    '未',
    '未',
    Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    'あいうえ開発',
    'あいうえ株式会社' ] ]
作成待ち
 [ [ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    '未',
    '未',
    Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    'かきこけ開発',
    '株式会社かきこけ' ] ]
作成待ち
 [ [ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    '未',
    '未',
    Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    'さしすせ開発',
    'さしすせ株式会社' ] ]
作業中
作成済み
作業中
作業中

masaki.png
柱：ここまできたら後は簡単ですね。時間の表記の変更の仕方は調べればすぐにでてくるのでやってみてください！
minami.png
総務：はい！まずは二次元配列を一次元配列にかえて〜、時間の表記を適切なものに変更したものを変数にいれていきます〜。

柱：あっそうだ。二次元配列を一次元配列にする方法は多数あるのですが違う方法を教えますね。

総務：はい！お願いします！
masaki.png
柱：この図を見て下さい。箱の中に複数箱が入っている状態ではなく、箱の中に一つの箱しか入っていない場合は、配列名[0]で一次元配列でだすことができます。
e0b801a4-e984-4dd9-a0e2-d85ef7b2d3e9.jpeg
総務：わかりやすいです！その方法もあるのか！

総務：お！できました！

柱：いいですね〜。


    //2次元配列を1次元配列にする
    var ss = cell2[0];
    console.log(ss);

ログ
作成待ち
 [ [ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    '未',
    '未',
    Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    'あいうえ開発',
    'あいうえ株式会社' ] ]
[ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
  '未',
  '未',
  Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
  'あいうえ開発',
  'あいうえ株式会社' ]
作成待ち
[ [ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    '未',
    '未',
    Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
    'かきこけ開発',
    '株式会社かきこけ' ] ]
[ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
  '未',
  '未',
  Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
  'かきこけ開発',
  '株式会社かきこけ' ]

※表記については、以下のようにする。
（請求書Noは請求日に案件リストの行数を付け加えたものとしている）
65e878fa-83fd-499a-8de0-9f7242b6cf35.png
minami.png
総務：ということで、表記変更と値の取得ができました！！

    // 請求書Noの表記を変更して取得
    var a = ss[0];
    invoice = Utilities.formatDate(a,"JST", "yyyyMMdd");
    var invoiceNo = invoice + (i+1)
    console.log(invoiceNo);
    // 請求日の表記を変更して取得
    billingdate = Utilities.formatDate(a,"JST", "yyyy/MM/dd");
    console.log(billingdate);
    // 入金期限の表記を変更して取得
    var b = ss[3];
    depositdate = Utilities.formatDate(b,"JST", "yyyy/MM/dd");
    console.log(depositdate);
    // 会社名の値を取得
    var company = ss[5];
    console.log(company);

ログ
[ Tue Mar 30 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
  '未',
  '未',
  Thu Apr 29 2021 11:00:00 GMT-0400 (アメリカ東部夏時間),
  'あいうえ開発',
  'あいうえ株式会社' ]
202103311
2021/03/31
2021/04/30
あいうえ株式会社

［作成待ち］の案件数の請求書のスプレッドシートを作る

minami.png
総務：請求書に貼り付けしたい値が取得できたので、今度は貼り付けするシートを作りたい！
masaki.png
柱：ここからの処理はGAS側で書き方が定義されているものなので調べればでてきますよ。
minami.png
総務：わかりました！

総務：（調査中・・・）

総務：まずは事前に準備した雛形請求書（スプレッドシート）と完成したスプレッドシートが入るフォルダのIDも取得しないと！ ほほー、xxxxx の部分にシートとフォルダのIDを入れれば取得できるのか〜！

 // 雛形請求書（スプレッドシート）
 var templateFile = DriveApp.getFileById("xxxxxxx");
 // 完成したスプレッドシートが入るフォルダ
 var OutputFolder = DriveApp.getFolderById('xxxxxxx');

総務：そしたらファイル名も決めておこう。 請求書のファイル名は[請求書xxxx年xx月xx日顧客会社名_自社名］にしたいのでうまく文字列と変数を連結させればできますね。

// 出力ファイル名
var CopiedFile = templateFile.makeCopy( "請求書_"+ invoice + "_"+ company +"_Fignny", OutputFolder );

総務：あれ？？雛形のスプレッドシートのIDしか取得してないな。これだと雛形に案件リストの値が貼り付けされてしまうのか。コピーしたシートのID取得する方法あるのかな〜。また調査だ。。。

総務：すぐでてきました（笑）これで案件数のスプレッドシートができるぞ〜！

// コピーしたシートのID取得
var CopiedFileId = CopiedFile.getId(); 
//値を貼り付けする請求書
var ss_copyTo = SpreadsheetApp.openById(CopiedFile);

masaki.png
柱：いいですね。調べ方も慣れてきたんですかね？（笑）

取得した値を貼り付けする

minami.png
総務：ここはもう簡単ですな！

//貼り付けするセル指定
ss_copyTo.getRange("H10:Q10").setValue(invoiceNo);
ss_copyTo.getRange("H11:Q11").setValue(billingdate);
ss_copyTo.getRange("H12:Q12").setValue(depositdate);
ss_copyTo.getRange("C6:O7").setValue(company);

柱：もう少しですね。

総務：やっとです（汗）

ステータスを［作成待ち］から［作成済み］に変更

minami.png
総務：よしゃ〜！！！できた〜！！！

var completerow = "L" + (i+2)
sh.getRange(completerow).setValue("作成済み");

masaki.png
柱：お疲れ様です！！
minami.png
総務：お疲れ様です！！ありがとうございます！！！

5.感想

今回は初めてプログラムを書いて動くものを作ってみました！

素人なので、最初は ”簡単そうだなぁ” と思っていましたが、ProgateでJavaScriptを少し学んだだけでは、なかなか難しいものでした。
経験がないため「これくらい簡単でしょ」というクライアント様が多いのも理解できたし、そういうときにエンジニアが必ず顔をしかめる理由もわかりました。（笑）
弊社の代表はエンジニア社長なので、両者の気持ちがわかった上で仕事を請けてくれるので良かったと思いました。

疑問や不明点はサポートエンジニアの柱に聞くとすぐに正解が分かるため、自分の意固地な性格もあり、”自力で調べて理解してやる。”と3-4時間調査してた時もありました。
その時に柱が「その気持ち分かります。悔しいけど、本当の仕事なら納期というものがあるから聞かないといけないんですよね。でも、悔しい気持ちを成長に変えれる。」と仰っていて、違う観点からもエンジニアという仕事はすごいと改めて思いました。

”請求書を自動作成するツールなんていくらでもあるじゃないか”と思う方もいるかもしれません。しかも、もっと便利なやつ。
ですが自分で作ってみることで、作業効率化は素晴らしいと感じ意欲的になった事や、総務として支えている専門職の方たちの凄さが実感できました！！

今度は何をしようか考えるのが楽しみです！

6.参考にさせていただいたサイト

【超初心者向け！】GASの二次元配列をやさしく図入り解説！ - Yuki's bnb blog

Google Apps Scriptでスプレッドシートの列データを配列として取得する方法

【GAS】for文をわかりやすく理解する方法【めがね式】 | 100メガ

二次元配列を一次元配列に変換する方法〜GoogleAppsScript〜 | GAS開発記録

Google Apps Script(GAS)の日付を文字列に変換する方法(formatDateメソッド) | AutoWorker〜Google Apps Script(GAS)とSikuliで始める業務改善入門

【Google Apps Script】移動・複製・リネーム・ID取得・読み書きする方法 [Spreadsheets] | CGメソッド

JavaScript | 配列の要素の値の取得と新しい値の代入

二次元配列とは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

for文とは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

JavaScriptの「Array.prototypeメソッド」の全30メソッドを解説【ES2016版】 | maesblog

70
82
5

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
70
82