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

はじめに

今回のは、こちらの仕様を少し変更したバージョンとなります。よって細かい情報は以下のリンクを見てください。
Google Apps Script 見積書からデータを転記し、指定フォルダに請求書を作成（スプレッドシート）

変更された点は**「請求書の保存先を、指定フォルダから同じスプレッドシート内にした」**という点だけです。

見積書を用意

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

請求書のテンプレを用意

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

注意点

tableの行数は同じに

見積書のデータが入っているtableの行数と、請求書のテンプレのtableの行数は、同じでないといけない。

今回の例では、見積書のtableの行数は最大１０行入る。なので、請求書のテンプレのtableの行数もは最大１０行としてある。

コピー元のシート名は変更しないで

今回の例では「請求書テンプレート」というシート名ですが、コピー元のシートを取得するときにこのシート名を使うので、変えないでね

見積書から請求書を作成

一気に全部載せます。

コード.gs

// Spreadsheetが開かれた時に自動的に実行されます.
function onOpen() {
  // 現在開いている、スプレッドシートを取得します.
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // メニュー項目を定義します.
  var entries = [
    {name : "請求書を生成"  , functionName : "menuItem1"},
  ];
  // 「Custom」という名前でメニューに追加します.
  spreadsheet.addMenu("Custom", entries);
}


// menuItem1をクリックした時の処理
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();
  Logger.log(finalTotal);
  
  //tableの開始B7から、lastRow行 + 6列
    var values = sheet1.getRange(7, 2, lastRow, 6).getValues();
    
  return [values, name, finalTotal];
}

function setData(data) {
  //現在のスプレッドシートを取得
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //同じスプレッドシート内にある「コピー元シート」をシート名で指定
  var source = ss.getSheetByName("請求書テンプレート");
  
  //  シートをコピー
  var newSheet = source.copyTo(ss);
  
  //今日の日付
  var today = new Date();
  var todayData = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy年 MM月 dd日');
  
  //見積書のtable内の最終行を取る
  var lastRow = getLastRow();
  //請求書のtableはB17から始まる
  var stopSetCount = lastRow + 16;
  Logger.log(stopSetCount);
   
  // 請求先の相手の名前を取得
   var fileName = '請求書(' + data[1] + ')';
  //　コピーしたシート名を変更
  newSheet.setName(fileName);
  
   //データを転記
   //コピー先のシートの中のセルを指定して、コピー実行
  newSheet.getRange('B8').setValue(data[1]); //name
  newSheet.getRange('C12').setValue(data[2]); //finalTotal
  newSheet.getRange('H28').setValue(data[2]); //finalTotal
  newSheet.getRange('H2').setValue(todayData); //日付
  
  //dataの中のvaluesを取り出す
  var items = data[0];
   
  var j = 0;
  for(var i = 17; i <= stopSetCount; i++) {
    newSheet.getRange(i, 2).setValue(items[j][3]); //description
    newSheet.getRange(i, 5).setValue(items[j][1]); //qty
    newSheet.getRange(i, 7).setValue(items[j][4]); //unitPrice
    newSheet.getRange(i, 8).setValue(items[j][5]); //total 
    j++;
  }
  
  //作成シートにカーソルをフォーカス
  ss.setActiveSheet(newSheet);
}

//見積書の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;

//もし１０行以上入ってたら警告を出す
  if (lastRowValue > 10) {
  alert();
    return;
  }
  return lastRowValue;
}

//最終金額を取りたい
function getFinalTotal() {
  //現在のスプレットシートを取得する
  var sheet1 = SpreadsheetApp.getActiveSheet();
  //「Total(Include GST)」の文字列を探す
  var textFinder = sheet1.createTextFinder('Total(Include GST)');
  //検索結果に該当するセルを抽出
  var ranges = textFinder.findAll();
  //最終金額の左の文字、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;
}

function alert() {
  var ui = SpreadsheetApp.getUi();
  var title = '見積書のデータが１０行以上です';
  var text = '１０行以上だと見た目が崩れるよ！'
  ui.alert(title, text, ui.ButtonSet.OK_CANCEL);
}

軽く解説

以下のリンクを見てください。ほとんど同じコードなので笑
Google Apps Script 見積書からデータを転記し、指定フォルダに請求書を作成（スプレッドシート）