LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@MASHIRO-P(真白犬)

【GAS】不要なシートを一覧から選択して削除する

Last updated at Posted at 2024-02-04

作成の経緯

一つのスプレッドシートに毎日,毎週シートを追加し、
四半期や年度ごとに不要なシートを削除して、また追加していく。
そんな作業がありますよね。(...ない?)

スプレッドシート自体を新しく作り変えると、シートIDが変わってしまい、
ブックマークしていた人は都度ブックマークしなおす必要があります。
また、すべてシートを消したいわけではなく、直近1ヵ月分は残しておきたかったり、
特定の名前のシートは消したくなかったりもしたり...

要するに、選択したシートのみを一括で削除できる機能はないんかい！ということなのですが、どこにもなかったので作りました。

イメージ

image.png
image.png

仕様

・削除する前に、指定したドライブのフォルダ配下に、
　スプレッドシート自体のバックアップを作成するか選択できます。
　バックアップするフォルダの場所は、プログラムに直書きしてください。

・実行すると、2~3秒読み込み画面が入ります。
　その間に裏でシート名を取得し、新しい順に一覧化しています。
　プログラム内で指定したシート名は一覧から除外されます。

・シートごとにチェックボックスが作成されます。
　チェックしたシートが削除対象です。
　一番上の「すべて選択」ボタンを押すとすべてのチェックボックスがONになります。

・確認画面でOKを押すとシートが削除されます。

プログラムサンプル

コード.gs
/**
 * バックアップを取ってから、選択したシートを削除する
 */
 //本スプレッドシート
const SPREAD_SHEET = SpreadsheetApp.getActiveSpreadsheet();

//バックアップ配置先(フォルダーIDを指定してください)
const BACKUP_FOLDER = "XXXXXXXXXXXXXX";

function CopyAndDeleteSheetMain() {

  var selectBackUp = Browser.msgBox(
    "バックアップを作成します",
    "▼出力先のフォルダ\\n" +
    "https://drive.google.com/drive/folders/" + BACKUP_FOLDER + "\\n\\n" +
    "作成する場合は「OK」を押してください"
    , Browser.Buttons.OK_CANCEL);

  if (selectBackUp == "ok") {
    CopyToDriveMain();
    Browser.msgBox("過去案件ドライブにコピーしました。", Browser.Buttons.OK);
  } else {
    Browser.msgBox("バックアップは作成されません。\\nシート削除処理を行います。", Browser.Buttons.OK);
  }
  DeleteSheetMain();
}

/**
 * 本シートを指定のドライブにコピーする
 */
function CopyToDriveMain() {
  //テンプレートファイル(本シート)
  var activeSheetId = SPREAD_SHEET.getId();
  var templateFile = DriveApp.getFileById(activeSheetId);

  //バックアップを出力するフォルダ
  var outputFolder = DriveApp.getFolderById(BACKUP_FOLDER);

  //出力ファイル名
  var outputFileName = Utilities.formatDate(new Date(), "JST", "yyyy年MM月dd日_バックアップ");

  //指定したフォルダにコピーする
  templateFile.makeCopy(outputFileName, outputFolder);
}

/**
 * シートを削除するメインメソッド
 */
function DeleteSheetMain() {
  var allSheetList = getAllSheetList();
  var allSheetListLength = allSheetList.length;
  if (allSheetListLength == 0) {
    return Browser.msgBox("削除対象のシートがありません");
  }

  //HTMLモーダルの高さを決める
  var hight = 180 + allSheetListLength * 28

  //HTMLモーダルの幅を決める
  var width = 330;
  var sheetNameLengthList = [];
  for (sheetName of allSheetList) {
    var sheetNameLength = widthCal(sheetName);
    sheetNameLengthList.push(sheetNameLength);
  }
  var maxSheetNameLength = Math.max.apply(null, sheetNameLengthList);
  if (maxSheetNameLength > 13) {
    width = width + (maxSheetNameLength - 13) * 28
  }

  //createHtmlOutputFromFileではなく、createTemplateFromFileを使用すること
  var app = HtmlService.createTemplateFromFile("deleteSheet").evaluate()
    .setWidth(width)
    .setHeight(hight);;
  SpreadsheetApp.getUi().showModalDialog(app, 'シート削除');
}

/**
 * 現在開いているスプレッドシートからシート名を取得し、シート名の配列を作成する。
 * ただし、nonDispSheetListに指定したシート名(部分一致)は配列に追加しない。
 */
function getAllSheetList() {
  var sheets = SPREAD_SHEET.getSheets();
  var allSheetList = [];
  //削除対象から除外したいシートはここに指定してください
  //部分一致のため、以下の場合はテンプレート(AAA)とテンプレート(BBB)の両方が削除対象外となります
  var nonDispSheetList = ["テンプレート", "削除したくないシートB","削除したくないシートC"];

  for (sheet of sheets) {
    var nonDispFlg = false;
    var sheetName = sheet.getSheetName();
    for (nonDispSheet of nonDispSheetList) {
      if (sheetName.indexOf(nonDispSheet) == -1) {
        continue;
      }

      if (sheetName.indexOf(nonDispSheet) != -1) {
        nonDispFlg = true;
        break;
      }
    }
    if (!nonDispFlg) {
      allSheetList.push(sheetName);
    }
  }
  return allSheetList;
}

/**
 * チェックボックスで選択したシート一覧情報を受け取り、シートを削除する。
 */
function deleteSelectedSheets(sheetNames) {
  if (sheetNames.length == 0) {
    return Browser.msgBox("削除対象のシートが選択されていません");
  }
  var showSheetName = sheetNames.join("\\n");
  var deleteSheetConfirm = Browser.msgBox(
    "シート削除処理を実行します",
    "＜削除するシート＞\\n" + showSheetName + "\\n\\n" +
    "実行する場合は「OK」を押してください"
    , Browser.Buttons.OK_CANCEL);

  if (deleteSheetConfirm == "ok") {
    for (sheetName of sheetNames) {
      var sheet = SPREAD_SHEET.getSheetByName(sheetName);
      if (sheet) {
        SPREAD_SHEET.deleteSheet(sheet);
      }
    }
    SPREAD_SHEET.toast("シートの削除が完了しました。", "", 5);
  }
}

/**
 * 半角と全角文字数を計算する処理
 * 半角：0.5
 * 全角：1.0
 * とする
 */
function widthCal(string) {
  var x = string.replace(/[｡-ﾟ]/g, 'x');
  var hex = '';
  for (var i = 0; i < x.length; i++) {
    hex += x.charCodeAt(i).toString(16);
  }
  return hex.length / 4;
}
deleteSheet.html
<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <?!= HtmlService.createHtmlOutputFromFile('deleteSheetCSS').getContent(); ?>
</head>

<body>
  <div id="js-loader" class="loader">
    <h1>シート一覧<br>取得中...</h1>
  </div>
  <p class="attention">削除するシートを選択してください</p>

  <form name="form">
    <input type="checkbox" name="all" id="allCheckedBoxId" onClick="AllChecked();"><label for="allCheckedBoxId" class="allCheckedBoxLabelClass">すべて選択</label>
    <div id="checkboxes" class="OutSideCheckBoxClass"></div>
    <br>
    <button onclick="deleteSelectedSheets()" id="buttonId" class="buttonClass">確認する</button>
  </form>
</body>

<?!= HtmlService.createHtmlOutputFromFile('deleteSheetJs').getContent(); ?>

</html>
deleteSheetCSS.html
<style>
  .loader {
    align-items: center;
    background: #fff;
    bottom: 0;
    display: flex;
    justify-content: center;
    left: 0;
    position: fixed;
    right: 0;
    top: 0;
    z-index: 999;
  }

  @keyframes loader {
    0% {
      transform: rotate(0);
    }

    100% {
      transform: rotate(360deg);
    }
  }

  button[disabled] {
    pointer-events: none;
    background: #8e8e8e;
    border: solid 2px #8e8e8e;
  }

  .attention {
    font-size: 18px;
  }

  .allCheckedBoxLabelClass {
    font-size: 18px;
    font-weight: bold;
    padding-left: 5px;
    margin-bottom: 5px;
    -webkit-transition: all .3s;
    transition: all .3s;
  }

  .allCheckedBoxLabelClass:hover {
    background-color: #FFABCE;
  }

  .OutSideCheckBoxClass label {
    font-size: 18px;
    padding-left: 5px;
    margin-top: 5px;
    margin-bottom: 5px;
    -webkit-transition: all .3s;
    transition: all .3s;
  }

  .OutSideCheckBoxClass label:hover {
    background-color: #FFFF99;
  }

  .buttonClass {
    display: block;
    padding: 8px 20px;
    background: #d54100;
    border: solid 2px #d54100;
    border-radius: 10px;
    color: #ffffff;
    font-size: 130%;
    text-align: center;
    width: 100%;
  }

  .buttonClass:hover {
    background: #c33c00;
    border: solid 2px #c33c00;
  }

  label {
    display: inline-block;
    width: 85%;
  }
</style>
deleteSheetJs.html
<script>
  //ロードの表示/非表示
  const loader = document.getElementById('js-loader');
  window.addEventListener('load', () => {
  const ms = 400;
  loader.style.transition = 'opacity ' + ms + 'ms';
  
  const loaderOpacity = function(){
    loader.style.opacity = 0;
  }
  const loaderDisplay = function(){
    loader.style.display = "none";
  }
  setTimeout(loaderOpacity, 3000);
  setTimeout(loaderDisplay, 3000 + ms);
  });

  window.onload = function(){
    google.script.run.withSuccessHandler(function(sheetNames){
      var checkboxDiv = document.getElementById("checkboxes");
      for (var i = 0; i < sheetNames.length; i++) {
        var sheetName = sheetNames[i];
        var checkbox = document.createElement("input");
        var label = document.createElement("label");
        var br = document.createElement("br");
        var id = "checkBoxId" + i;
        checkbox.type = "checkbox";
        checkbox.name = "sheets";
        checkbox.value = sheetName;
        checkbox.setAttribute("class","checkboxClass");
        checkbox.setAttribute("id",id);
        checkbox.setAttribute("onClick","DisChecked()")
        label.innerHTML = sheetName;
        label.setAttribute("for",id);
        checkboxDiv.appendChild(checkbox);
        checkboxDiv.appendChild(label);
        checkboxDiv.appendChild(br);     
      }
    }).getAllSheetList();
  }
  
  // 「全て選択」チェックで全てにチェック付く
  function AllChecked(){
    var all = document.form.all.checked;
    for (var i=0; i<document.form.sheets.length; i++){
      document.form.sheets[i].checked = all;
    }
  }

  // 一つでもチェックを外すと「全て選択」のチェック外れる
  function DisChecked(){
    var checks = document.form.sheets;
    var checksCount = 0;
    for (var i=0; i<checks.length; i++){
      if(checks[i].checked == false){
        document.form.all.checked = false;
      }else{
        checksCount += 1;
        if(checksCount == checks.length){
          document.form.all.checked = true;
        }
      }
    }
  }

  function deleteSelectedSheets() {
    processing(false);
    var checkboxes = document.getElementsByName("sheets");
    var selectedSheets = [];

    for (var i = 0; i < checkboxes.length; i++) {
      if (checkboxes[i].checked) {
        selectedSheets.push(checkboxes[i].value);
      }
    }

    google.script.run.deleteSelectedSheets(selectedSheets);
    google.script.host.close();
  }

  // 処理中アイコン表示・非表示
  function processing(processing) {
    if (processing) {
        document.getElementById('buttonId').setAttribute("disabled", true);
    } else {
        document.getElementById('buttonId').removeAttribute("disabled");
    }
  }
</script>

あとがき

色や画面デザインは超シンプルです。
変更したい場合はHTMLとCSSを弄ってください。

そのほか、ドライブの階層構造が視覚的にわかるようなプログラムも作っています。
こちらもよければ見ていってください。

なお、本記事のコードをコピペしての使用する際は、自己責任でお願いします。
誤ってシートを削除してしまい戻せなくなった場合や、意図せぬ挙動となった場合も一切の責任は負いません。(Qiita全般に言えることではありますが、シート削除を行うプログラムなので念押しで書いています。)

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?