Google Apps Script 見積書からデータを転記し、指定フォルダに請求書を作成（スプレッドシート）

はじめに

一応こちらの続きと言う感じで、やっていきます。
Google Apps Script 指定フォルダにコピー作成 + データを転記（スプレッドシート）

０から理解したいって方は、先に上記の記事を見てからこちらに来てもらえると、よりスムーズかと。。

今回は**本格的な見積書からデータを転記して、**請求書を作りたいと思う。

用意するもの

・請求書のテンプレのスプレッドシート
・見積書のスプレッドシート
・請求書を保存したいフォルダ（共有フォルダとか）

コピー作成された請求書のテンプレに、見積書から取得したデータを転記する。

そもそもスプレッドシートで見積書とか、どうやって作るねん？？って方はこちらを参考に。

大まかにやる事をまとめとく

・見積書のデータは２次元配列として、まとめて取得する。
→1行ずつ取得する方法もあるが、まとめて取得する方が私は好き。

・見積書内のtable（下記の画像を参考）のデータが、何行分あるかを知る。
→請求書のtable（下記の画像を参考）のデータが、何行になるのかを知るため。また、どこまでが見積書のデータかを知るため。

・最終金額の値があるセルの場所を知る。
→見積書のデータ取得のループでは取得しないため、今回は別で取る。

見積書を用意

こんな感じの見積書を使う。
見積書のデータはtableの中で、その上には相手方の名前（下の例では「株式会社　ここゲント」）

請求書のテンプレを用意

こんな感じの請求書のテンプレを使う。
これがコピーされて、そのコピー複製されたスプレッドシートに、見積書の中のtableのデータが転記される。
コピーされた請求書は、指定のフォルダの中に保存される。

メニューにボタンを追加

これは前回と全く同じ。
請求書を作成するプログラムを発動させるためのボタンを配置する。
ツール>スクリプトエディタでエディタを開く。

デフォルトで存在するmyFunction()は消しましょう。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom')
    .addItem('請求書を生成', 'menuItem1')
    .addToUi();
}

こんな感じでメニューにボタンを作れたかと。

見積書から請求書を作成

一気に全部載せます。

コード.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom')
    .addItem('請求書を生成', 'menuItem1')
    .addToUi();
}

// First itemをクリックした時の処理
function menuItem1() {
  var data = getData();
  setData(data);
}

function getData() {
  //現在のスプレットシートを取得する
  var sheet1 = SpreadsheetApp.getActiveSheet();
  
  //table内の最終行を取る
  var lastRow = getLastRow();
  //見積書のtableはB7から始まる
  var stopGetCount = lastRow + 6;
  Logger.log(stopGetCount);
  
  //名前を取る
  var name = sheet1.getRange(2, 2).getValue();
  //最終金額を取る
  var finalTotal = getFinalTotal();
  
  //tableの開始B7から、lastRow行 + 6列
   var values = sheet1.getRange(7, 2, lastRow, 6).getValues();
    
  return [values, name, finalTotal];
}

function setData(data) {
  //コピー先のスプレットシートのid
  var templateid = "****";
  var template = DriveApp.getFileById(templateid);
  
  //保存先フォルダオブジェクトの取得
  var destfolderid = "****"; //フォルダのid
  var destfolder = DriveApp.getFolderById(destfolderid);
  
  //table内の最終行を取る
  var lastRow = getLastRow();
  //請求書のtableはB17から始まる
  var stopSetCount = lastRow + 16;
   
   var fileName = '請求書(' + data[1] + ')';
   var sheet = template.makeCopy(fileName, destfolder);
  
   var copiedSheet = SpreadsheetApp.open(sheet);
   var sheets = copiedSheet.getSheets();
   var invoice = sheets[0];

   //データを転記
   //コピー先のシートの中のセルを指定して、コピー実行
  invoice.getRange('B8').setValue(data[1]); //name
  invoice.getRange('C12').setValue(data[2]); //finalTotal
  invoice.getRange('H26').setValue(data[2]); //finalTotal
  
  //dataの中のvaluesを取り出す
  var items = data[0];
   
  var j = 0;
  for(var i = 17; i <= stopSetCount; i++) {
    invoice.getRange(i, 2).setValue(items[j][3]); //description
    invoice.getRange(i, 5).setValue(items[j][1]); //qty
    invoice.getRange(i, 7).setValue(items[j][4]); //unitPrice
    invoice.getRange(i, 8).setValue(items[j][5]); //total 
    j++;
  }
}

//tableの最終行を取りたい
function getLastRow() {
 //現在のスプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  //現在のシートを取得
  var sheet = spreadsheet.getActiveSheet();
  
  //B列7行目からの最終行を下方向に検索
  var lastRow = sheet.getRange(7, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  //開始行は6固定。最終行の番号から開始行の番号を引くことで、table内の値が何行あるか判別
  var lastRowValue = lastRow - 6;
  return lastRowValue;
}

//最終金額を取りたい
function getFinalTotal() {
  //現在のスプレットシートを取得する
  var sheet1 = SpreadsheetApp.getActiveSheet();
  var textFinder = sheet1.createTextFinder('Total(Include GST)');
  var ranges = textFinder.findAll();
  //最終金額の左の文字、Total(Include GST)の位置を取る。Total(Include GST)の文字列は変更されない前提。
  var textPos = ranges[0].getA1Notation();
  
  var textPosCell = sheet1.getRange(textPos);
  //Total(Include GST)の右隣のセルの値を取る
  var finalTotal = textPosCell.offset(0, 1).getValue();
  return finalTotal;
}

軽く解説

見積書のデータを２次元配列として、まとめて取得する

「二次元配列」とはなんぞや？って人は、以下の記事を見て欲しい。画像付きでとてもわかりやすい。
GASでスプレッドシートを扱うときの二次元配列の「教え方」

//tableの開始B7から、lastRow行 + 6列
var values = sheet1.getRange(7, 2, lastRow, 6).getValues();

tableの開始B7（getRangeの7, 2）を起点とし、横に6列分（これは固定）、そして下に何行分か？を指定して、getValues()で取得している。getValueではなく、getValuesである。（複数形のsがあるかないか）

getLastRow()が返す値について

//B列7行目からの最終行を下方向に検索
  var lastRow = sheet.getRange(7, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  //開始行は6固定。最終行の番号から開始行の番号を引くことで、table内の値が何行あるか判別
  var lastRowValue = lastRow - 6;
  return lastRowValue;

B列7行目からの最終行を下方向に検索し、その結果がlastRowになる。
仮に4行分のデータがあった場合、lastRowの値は「10」となる！「４」では無い！

最初は私も盛大に勘違いしていたが、取得しているのは何行分あったかではなく、あくまで最終行の番号である。だから開始行の番号で引いてあげる必要がある。

こちらが参考になるかと。

setData(data)のループについて

//dataの中のvaluesを取り出す
  var items = data[0];

var j = 0;
  for(var i = 17; i <= stopSetCount; i++) {
    invoice.getRange(i, 2).setValue(items[j][3]); //description
    invoice.getRange(i, 5).setValue(items[j][1]); //qty
    invoice.getRange(i, 7).setValue(items[j][4]); //unitPrice
    invoice.getRange(i, 8).setValue(items[j][5]); //total 
    j++;
  }

初見では相当ややこしそうだが、、、
まず、data[0]を新たにitemsという変数に入れている。
なぜならdataにはvalues, name, finalTotalが入っており、その１つ目の要素（つまりdata[0]であるvalues）が、ループさせる必要のあるデータだからだ。

変数iが請求書の行番号。請求書のtableはB17から始まるので、17~ループが始まる。

変数jは配列itemsの何番目の値かを指定している。items[0]は、２次元配列で取得したvaluesの１つ目の配列の事。

items[1][3]だと、valuesの２つ目の配列（つまり見積書のtable2行目）の値で、その中かからさらに、４つ目の値である、「２つ目」が取れる。
配列は０からカウントするので、items[1][3]となる。

スプレッドシートのセル範囲の値を二次元配列として取得して取り扱う方法
こちらも参考になりました。

getFinalTotal()について

Google Apps Scriptでスプレッドシートの隣のセルを取得する
こちらが参考になりました。

作成した日付を記載する

Google Apps Script 現在の日付を取得（スプレッドシート）
これを参考に。

同じスプレッドシート内に作成するパターンについて

こちらを参考に
Google Apps Script 見積書からデータを転記し、同じスプレッドシート内に請求書を作成

最後に

やり方はたくさんあると思うので、その中のほんの１つと思って欲しい。。
最終金額の取得方法とか、結構無理やりだし。。

指定フォルダにコピーを作成する辺りの詳しい情報に関しては、前回の記事が参考になるかと。