■ CSVデータ読み込み

CSVファイル選択からのデータ読み込み

CSVファイルからデータを取得し、整形する汎用的なコードです。まず、CSVファイルを固定で指定しています。指定したCSVをさらに汎用的にするために関数に渡しています。関数の処理は、まずファイルを毎月保存するためのディレクトリを作成しているので、かつ、そこにCSVをアップロードするので、このディレクトリのCSVを取得します。Google Driveに保存されたファイル、ディレクトリはそれぞれIDが振られます。そのIDによって対象のファイルを指定します。getFolderByName()、getFilesByName()などはそのIDによってファイルを取得する標準関数です。最後に文字コードをSJISで指定しています。こうして取得したCSVのテキストデータを戻り値で返しています。

function main() {
  var csv_name = '[csv name].csv';
  var csv = get_profit_csv(csv_name);
  var csvData = Utilities.parseCsv(csv);
}

function get_profit_csv(file_name) {
  var folder_name = '[folder name]';
  var folderID = DriveApp.getFoldersByName(folder_name).next().getId();
  var fileIT = DriveApp.getFolderById(folderID).getFilesByName(file_name).next();
  var textdata = fileIT.getBlob().getDataAsString('sjis');
  return textdata;
}

店舗毎のシートを作成し、商品コードと在庫数を出力する

  for(var shop in shops){
    var sheet = set_sheet(shopNames[shop]);
    set_header(sheet);
    var i = 2;
    for(var goods in ret[shop]) {
      sheet.getRange(i, 1).setValue(goods);
      set_goods_type(sheet, goods, i);
      cntArr = ret[shop][goods];
      let cnt = cntArr.reduce(function(sum, element){
        return sum + element;
      }, 0);
      sheet.getRange(i, 8).setValue(cnt);
      set_func_diff(sheet, i);
      i++;
    }
  }

各店舗で共通している商品を、集計するための商品リスト作成

  var goodsList = set_goods_list(ret);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[main sheet name]');
  sheet.clearContents();
  set_header(sheet);
  var j = 2;
  for (var id in goodsList) {
    set_goods_type(sheet, goodsList[id], j);
    set_func_summary(sheet, shopNames, j);
    set_func_diff(sheet, j);
    j++;
  }
  sheet.hideColumn(sheet.getRange("A1"));
  protection_sheet(sheet);

■ シートのフォーマット作成

シートの初期化

function set_sheet(shop_name) {
  // 同じ名前のシートがなければ作成
  var sheet = SpreadsheetApp.getActive().getSheetByName(shop_name)
  if (sheet != null) {
    // そのシートにある値のみ全てクリア
    sheet.clearContents();
    return sheet
  }

  sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  sheet.setName(shop_name);
  return sheet;
}

シートのヘッダーを作成

function set_header(sheet) {
    const header_str = ['goods code', 'goods name', 'category1', 'category2', 'category3', 'pre stocks', 'Replenishment', 'sales', 'event sales', 'loss', 'post stocks', 'Quantity difference', 'remarks'];
    for (var j in header_str) {
      j++;
      sheet.getRange(1, j).setValue(header_str[j]);
    }
}

商品コードをキーとして、紐づく商品属性等を表示する

function set_goods_type(sheet, goods_code, row) {
  var goodsTypes = goods_code.split('_');
  sheet.getRange(row, 1).setValue(goods_code);
  sheet.getRange(row, 2).setValue(goodsTypes[1]);
  sheet.getRange(row, 3).setValue(goodsTypes[2]);
  sheet.getRange(row, 4).setValue(goodsTypes[3]);
  sheet.getRange(row, 5).setValue(goodsTypes[4]);
}

各シートの当月の売上を集計する関数の設定

function set_func_summary(sheet, shopNames, row) {
    goods_code_column = 'A' + row;
    func = "=sum(";
    for (var i in shopNames) {
      func += "sumif('" + shopNames[i] + "'!A:H," + goods_code_column + ",'" + shopNames[i] + "'!H:H), ";
    }
    func = func.replace(/\,\ $/, ')');
    sheet.getRange(row, 6).setValue(func.replace(/\!H\:H/g, '!F:F'));
    sheet.getRange(row, 7).setValue(func.replace(/\!H\:H/g, '!G:G'));
    sheet.getRange(row, 8).setValue(func);
    sheet.getRange(row, 9).setValue(func.replace(/\!H\:H/g, '!I:I'));
    sheet.getRange(row, 10).setValue(func.replace(/\!H\:H/g, '!J:J'));
    sheet.getRange(row, 11).setValue(func.replace(/\!H\:H/g, '!K:K'));
}

各シートの数量差異を計算する関数の設定

function set_func_diff(sheet, row) {
  func2 = "=K" + row + "-(F" + row + "+G" + row + "-H" + row + "-I" + row + "-J" + row + ")";
  sheet.getRange(row, 12).setValue(func2);
}

各店舗で共通している商品を、集計するための商品リストを作成

function set_goods_list(ret) {
  var goodsList = [];
  for (var shop in ret) {
    for (var goods in ret[shop]) {
      if (goodsList.indexOf(goods) == -1) {
        goodsList.push(goods);
      }
    }
  }
  goodsList.sort();
  return goodsList;
}

■ シートの保護設定

シートの範囲保護

function protection_column(sheet) {
  // 保護初期化のためのクリア
  protection_remove(sheet);

  // 保護設定
  var protections = [
    sheet.getRange('!A:E').protect().setDescription('protection1'),
    sheet.getRange('!H:H').protect().setDescription('protection2'),
    sheet.getRange('!L:L').protect().setDescription('protection3'),
    sheet.getRange('!A1:M1').protect().setDescription('protection4')
  ];
  var me = Session.getEffectiveUser();
  for (var i in protections) {
    protections[i].addEditor(me);
  }
}

参考にしたサイト

• Google Apps Scriptを用いたスプレッドシート保護の設定を行う

※スプレッドシートを公開設定にすると、非ログイン状態で閲覧&編集ができる。

シートを保護して公開することで、不要な入力を除外することができ、アカウントを量産しなくても入力作業ができる。
非公開情報を含む場合、各店舗用のgoogleアカウントを取得してシートを共有する必要がある。

シート全体保護

function protection_sheet(sheet) {
  // 保護初期化のためのクリア
  protection_remove(sheet);
  // 保護設定
  var protection = sheet.protect().setDescription('protection');
}

保護設定のクリア

function protection_remove(sheet) {
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i in protections) {
    var protection = protections[i];
    if (protection.canEdit()) {
      protections[i].remove();
    }
  }
}

■ Google Driveへのバックアップ

月単位の履歴をドライブに保存する。その際、管理者以外編集できないように保護する。

function file_backup() {
  var copy_file_name  = "[backup file name]";
  var copy_dir = DriveApp.getFolderById("[folder id]");
  var moto_dat = DriveApp.getFileById("[file id]");
  var newfile = moto_dat.makeCopy(copy_file_name, copy_dir);
  var id_copy_file = newfile.getId();
}

■ GAS実行時のコンファーム

実行確認コンファーム

function check_alert(cmd) {
  // SpreadsheetApp.getUi().prompt('これでコンファーム表示できる。');

  var ui = SpreadsheetApp.getUi();
  var title = 'コマンド実行確認';
  var prompt = '【' + cmd + '】 を実行しますか？'
  if (ui.alert(title, prompt, ui.ButtonSet.YES_NO) == 'NO') { 
    throw new Error('処理を停止します。');
  } else {
    return true;
  }
}

■ 特殊処理

全シートへグレーアウトを反映する処理

各店舗シートにおいて、原本シートでグレーアウトした商品と同じ商品コードをグレーアウトする処理

function reflect_grayout() {
  check_alert('全シートへグレーアウトを反映'); // 実行確認アラート
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('原本');
  // 特定する色を取得する
  var targetColor = sheet.getRange(1, 1).getBackground();

  // 色一覧の「背景色の配列」と「内容の配列」を取得する
  var colorsListRange = sheet.getRange('!A:A');
  var colors = colorsListRange.getBackgrounds();
  var contents = colorsListRange.getValues();

  var prompt = colors[0][0];
  var gray_rows = [];
  for (var i in colors) {
    if (colors[i][0] !== '#ffffff') {
      gray_rows.push({"goods": contents[i][0],"bgcolor": colors[i][0]});
    }
  }

  // 全シートへの反映
  var shop_names = get_shop_names();
  for (var i in shop_names) {
    var shop_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shop_names[i]['name']);
    shop_sheet.getRange("!B:M").setBackground('#ffffff'); // 各店舗シートの背景を白で初期化
    var item_codes = shop_sheet.getRange('!A:A').getValues();
    for (var j in item_codes) {
      if (item_codes[j][0] == "") { continue; }
      for (var k in gray_rows) {
        Logger.log([item_codes[j][0], k, gray_rows[k]['goods'], gray_rows[k]['bgcolor'], gray_rows.length])
        if (item_codes[j][0] == gray_rows[k]['goods']) {
          var row = parseInt(j, 10) + 1;
          var bgcolor = gray_rows[k]['bgcolor'];
          shop_sheet.getRange("B" + row + ":M" + row).setBackground(bgcolor);
        }
      }
    }
  }

  var ui = SpreadsheetApp.getUi();
  var title = 'get colors'; 
}