Spreadsheet

【スプレッドシート】見積作成システムをつくってみた

 

Googleのスプレッドシートを利用した見積書を作成するシステムをつくってみました。

簡易的ではありますが、その分自由に設計できるので便利だと思います!

>人気記事!簡単な勤怠管理表の作り方はこちら

 

 
 

システムの概要

見積をテンプレートで登録しておく

作成した見積書に自動で番号を割り当てる

見積を添付したメールの下書きを作成する

 

注)テンプレートの登録はGoogle Workspace(旧G Suite)を利用している場合のみ使える機能です

Google Workspaceを利用していない場合は、都度コピーを作成で複製することになります。

 

用意したもの

最初にスプレッドシートを2種類を用意しました。

  1. 見積書のテンプレートとなるスプレッドシート
  2. 見積の番号を管理するスプレッドシート

 

見積書のテンプレートはこのような感じにしました。用途に応じて仕様を決めましょう。

見積書テンプレ

 

次に見積の番号を管理するシートになります。

大事なポイントは必ず0を入れておくことです!

このシステムではデータが入っている最終行から次の番号を計算していルので、スタートとなる0が必要になります。

番号管理シート

 

スクリプト①

まず初めに、今回実装する機能をクリックで実行できるようにスプレッドシートにメニューを追加します。

メニューとはスプレッドシートの左上に並んでいる「ファイル、編集、表示、挿入...」のことです。

function onOpen(){
    var ui = SpreadsheetApp.getUi();          
    var menu = ui.createMenu('追加機能'); //追加機能という名目のメニューを追加
    menu.addItem('見積番号を割り当て', 'getNumber'); //見積番号を割り当てる機能
    menu.addItem('メールの下書きを作成','createDraft') //見積書が添付されたメールの下書きを作成する機能
    menu.addToUi();                            
}

 

function onOpen()でファイルを開いた時に「追加機能」が表示されるようにしました。

追加機能

 

スクリプト②

続いて、見積番号を割り当てるするスクリプトになります。

見積書の「No.」のセルに見積番号が自動で入り、さらにファイル名も「見積番号_件名」になるようにしています。

見積番号を割り当てる
function getNumber(){
    var dataSS = SpreadsheetApp.openById('番号管理用スプレッドシートのID'); 
    var dataSheet = dataSS.getActiveSheet();
    var lastRow = dataSheet.getLastRow();
    var sheet = SpreadsheetApp.getActiveSheet(); //見積書のシート

    var No =  Number(dataSheet.getRange(lastRow,1).getValue());  //番号データの最終値を取得
    var newNo = No+1;

    sheet.getRange("G3").setValue(newNo); //セルG3に番号を入れる

    var name = sheet.getRange("A4").getValue(); //件名を取得
    var fileName = newNo+"_"+name; //ファイル名

    SpreadsheetApp.getActiveSpreadsheet().rename(fileName); //ファイル名を変更
    
    var Url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
    
    //番号データシートに記載する(番号、ファイル名、URL)    
    dataSheet.getRange(lastRow+1,1).setValue(newNo); 
    dataSheet.getRange(lastRow+1,2).setValue(fileName);
    dataSheet.getRange(lastRow+1,3).setValue(Url);
}

 

<スクリプト②の実行結果>

見積書には番号が入っており、さらにファイル名も「番号_件名」に変更されています。

実行結果1

 

番号管理用シートはこのようになります。

実行結果2

 

スクリプト③

最後は、PDF形式の見積書が添付されたメールの下書きを生成するスクリプトになります。

下書きを作成したら、管理用スプレッドシートに日付が入ります。

function createDraft() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sheetID = sheet.getSheetId();
  var key = ss.getId();
  var ui = SpreadsheetApp.getUi();
  var token = ScriptApp.getOAuthToken();
  
  var number = sheet.getRange("G3").getValue(); 
  var name = sheet.getRange("A4").getValue(); 
  var fileName = number+"_"+name

  var to = "" //宛先が毎回同じ場合は入力してもOK
  var subject = "件名"
  var body = "メール本文"
  
  var url = "https://docs.google.com/spreadsheets/d/" + key + "/export?gid=" + sheetID +
        "&format=pdf&portrait=true&size=A4&gridlines=false&fitw=true"
  var pdf = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' + token}}).getBlob().setName(fileName);
  
  GmailApp.createDraft(to,subject,body,{attachments:pdf});

  var dataSS = SpreadsheetApp.openById('番号管理用スプレッドシートのID'); 
  var dataSheet = dataSS.getActiveSheet();

  var formatDate = Utilities.formatDate(new Date(), "JST","MM/dd");
  var lastRow = dataSheet.getLastRow();
  dataSheet.getRange(lastRow,4).setValue(formatDate); //管理シートに日付を入れる
}

 

※下書きを作成せずにメールを自動で送りたい場合は、「GmailApp.createDraft」→「GmailApp.sendEmail」にします

 

<スクリプト③の実行結果>

メールの下書きにはPDFの見積書が添付されています。

実行結果3

 

番号管理用シートには日付が入りました。

実行結果4

 

見積書の作成システムの説明は以上です。

Google Apps Scriptとスプレッドシートを活用すれば業務の効率を大幅に改善できるので、これまでエクセルしか使ったことない人も触れてみると面白いのでは思います!

Google Apps Scriptを学べる本は少ないのが難点ですが、最近少しづつ出版されているのでチェックしてみて下さい。

>Google Apps Scriptの本について詳しくみる