Google Apps Script

【営業効率化】複数の営業リストを一括管理する

複数シートに分かれている営業先別の連絡先を1シートにまとめ、一括で管理をします。
重複しているデータを簡単に確認することができ、データの整理をしやすくします。
また、Gmailの下書きメールを一括で作ることができます。

シート名の取得

出力シートにアクティブなシートの名前を取得し、全て表示します。

const sheet = SpreadsheetApp.getActiveSpreadsheet()　アクティブなシートを取得
const sheets = sheet.getSheets();　シート名を一括取得　　　　　
sheets[i].getName();　シート名を一つずつ取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const output_list = sheet.getSheetByName("全体抽出")
  const output_list_data = output_list.getDataRange().getValues()
  var sheets = sheet.getSheets();
  
　for (i=1;i<=sheets.length-2;i++) {
    var sheetName = sheets[i].getName(); //シート名の取得
    output_list_data[i+1][15] = sheetName //書き込み
  }

　output_list_data[1][15] = "全シート"
　output_list.getDataRange().setValues(output_list_data)

フォルダ内のファイル名を取得する

指定されたフォルダ内のファイル名を取得して、出力します。

指定フォルダーに下記のようにメールのテンプレートをテキスト形式で保存します。
下書きメール作成時にこのテキストの内容が読み込まれます。

 var target = DriveApp.getFolderById(フォルダID); 特定のフォルダ内のみ取得
 var files = target.getFiles();　フォルダ情報を一括取得
 var file = files.next();　　　　一括取得したフォルダ情報から一つずつファイルを取得
 var fileName = file.getName();　ファイル名を取得
  var id = "1TeIImrsarXW9sEzOqD6xgzuDYbpUbH5Ztest"; 
  var target = DriveApp.getFolderById(id);
  var files = target.getFiles();
  var file_count = 1

  while (files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();
    output_list_data[file_count][17] = fileName
    file_count = file_count+1
  }
output_list.getDataRange().setValues(output_list_data)

指定シートの情報取得

チェックボックスでシートを指定し、出力シートに表示されるようにします。
入力元の各シートは下記のフォーマットで用意します。

出力シートで対象シートにチェックを入れて実行をすると、一覧に表示されるようにします。

function input() {  
　const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const output_list = sheet.getSheetByName("全体抽出")//出力先
  const output_list_data = output_list.getDataRange().getValues()
  var lastline = 0  //最終行
  var sheetname = "" //シート名
  for(m=2;m<12;m++){
    if(output_list_data[m][14] ==true || output_list_data[1][14] ==true){
      sheetname = output_list_data[m][15]
      if(sheetname !=""){
        var input_list = sheet.getSheetByName(sheetname) //入力元
        var input_list_data = input_list.getDataRange().getValues()
        for (var i=1; i < input_list_data.length; i++) {
          for(var j=1;j<11;j++){
            var input_hai=[]
            input_hai[i]=input_list_data[i][j] 
            output_list_data[i+lastline][11] = sheetname//書き込み
            output_list_data[i+lastline][12] = input_list_data[i][0] 
            output_list_data[i+lastline][j] = input_hai[i] //書き込み

          }
        }
        lastline = lastline+i-1  //最終行
      }
    }

下書きメール作成

対象のメールアドレスとメールテンプレートのチェックボックスにチェックを入れ、Gmailの下書きメールを作成します。

function create_mail() {
  //シートから情報取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const send_list = sheet.getSheetByName("全体抽出")
  const send_list_data = send_list.getDataRange().getValues()

  //メールの題名等の設定
  const subject = 'メール管理ツールテスト'
  const options = { name: 'テスト' }
  var today = Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyy/MM/dd");
  var template_no = ""// メール種別

  var check_txt = 0
  for(m=1;m<27;m++){
    if(send_list_data[m][16] ==true){
      template_no = send_list_data[m][17]
      console.log(template_no)
      var send_body = text(template_no)//テキストから本文テプレートを取得
      check_text= check_txt++;
   }
}

テンプレートテキストの読み込み

チェックボックスで指定したテキストファイルを読み込みます。

//テキストファイルのメールテンプレート取り込み
function text (template_no) {
  var id = "1TeIImrsarXW9sEzOqD6xgzuDYbpUbH5Z"; //フォルダID
  var contents = DriveApp.getFolderById(id)  
  .getFilesByName(template_no)
  .next()
  .getBlob()
  .getDataAsString("UTF-8");  //.getDataAsString("sjis");
  return contents
}

指定シートへの更新処理

チェックを入れた行のみ、元のシートに上書きをします。

function update() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
  const output_list = sheet.getSheetByName("全体抽出")//入力元
  const output_list_data = output_list.getDataRange().getValues()

  //チェックされている行を更新
  for (var i = 1; i < output_list_data.length; i++) {
    const checkbox = output_list_data[i][0] //チェック
    if(checkbox == true){
      var no = output_list_data[i][12]

      checksheet = output_list_data[i][11]
      console.log(checksheet)
      const input_list = sheet.getSheetByName(checksheet) //出力先
      const input_list_data = input_list.getDataRange().getValues()

      for(var j=1;j<11;j++){
      input_list_data[no][j] = output_list_data[i][j]
      }
      input_list.getDataRange().setValues(input_list_data)
    }
  }
}

ポップアップの設定

指定したチェックボックスに2つ以上のチェックが入った場合、ポップアップで警告が出るように設定します。

  if(check_txt > 1){
      var ynAns = Browser.msgBox("メールテンプレートは一つにチェックしてください",Browser.Buttons.YES_NO);
      return
  }

ボタンの作成

[挿入]⇒[図形描写]で図形を作成後、図形の詳細で[スクリプトを割り当て]にファンクション名を指定すると、図形をスクリプト実行時にボタンのように利用できます。

重複データの色付け

スプレッドシートにはEXCELのように、条件書式設定に「重複データに色付けする」の設定項目がないため、「COUNTIF」関数を使って重複する値に色付けをします。

COUNTIF(範囲, 条件)	範囲内で条件に一致する要素の個数を返します。

B列の重複に色付けする場合
　⇒　カスタム書式=COUNTIF(B:B,B:B)>1

定列を右クリック⇒[条件付き書式]⇒[条件付き書式設定ルール]に下記のように記入します。

空白ではない場合の色付け

重複データの色付けと同様に、COUNTIF関数を使って空白ではない箇所に色付けされるように設定をします。

指定列を右クリック⇒ [条件付き書式]⇒ [条件付き書式設定ルール]に下記のように記入します。