Google Apps Scriptで日付入力フォームを作成する

# 作成にいたる経緯
Google スプレッドシートで日付を1ヶ月ごとに連続入力したい場合、フィルハンドルをクリックしてドラッグするか、```EDATE```関数や```DATE```関数を使用すると実現できます。

例）フィルハンドルをクリックしてドラッグ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/31485951-4588-51c0-eca9-b6bc2d5900cf.png)

ただし、以下のような点で上手く入力できないケースがあります。

* フィルハンドルをクリックしてドラッグ
例えば2行ごとに1ヶ月ずつ加算したいケースでは、連続入力できません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/f2a76ba7-e0eb-16a2-37eb-780c049bf338.png)

* ```EDATE```関数や```DATE```関数
フィルターなどで行が非表示になっている場合、参照しているセルがずれるため、連続入力できません。
例）こんなデータがあるとして
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/8b747d4a-c5d8-7608-3d19-3ddc5f34cfbe.png)
"a"でフィルターして、1つ目に"2023/1/31"を入力、2つ目に```EDATE```関数を入力
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/7df6350a-652d-ab58-f1dc-7221ecb0d2d8.png)
3つ目に```EDATE```関数をコピペすると・・・
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/478f51ab-a462-9fd7-2b71-5236f0f4146e.png)
思い通りになりません(--#)

# メニューの作成
ということで、まずはフォームを表示するためのメニューを作成します。
スプレッドシートの「拡張機能」→「Apps Script」でエディタを表示して、以下のコードを入力します。
```javascript
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('カスタムメニュー')
    .addItem('日付入力', 'showDateAutofillForm')
    .addToUi();
}
```
これで、スプレッドシートにメニューが追加されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/5c65e819-2926-6bdf-d112-8cfa161fecc9.png)

# フォームの作成
次に、フォームを作成します。
エディタ上でHTMLファイルを追加し、以下のコードを入力します。
なお、ファイル名は「DateAutofillForm」としています。
```html
<!DOCTYPE html>
<html>
  <head>
    <script>
      // あとで作成する
    </script>
  </head>
  <body>
    <form id="myForm">
      <table>
        <tr>
          <td>最初の日付(例:2023/05/31)</td>
          <td><input type="text" id="firstDate"></td>
        </tr>
        <tr>
          <td><label>何ヶ月ずつ増やす？</label></td>
          <td><input type="text" id="incrementMonth"></td>
        </tr>
        <tr>
          <td><label>何行ずつ増やす？</label></td>
          <td><input type="text" id="incrementCycle"></td>
        </tr>
      </table>
      <input id="btn" type="button" value="選択範囲に入力" onclick="handleButtonClick()" />
    </form>
  </body>
</html>
```

# フォームの表示
作成したフォームを、メニューの「日付入力」をクリックした際に表示するようにします。
```javascript
function showDateAutofillForm() {
  var htmlOutput = HtmlService.createHtmlOutputFromFile("DateAutofillForm");
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, '日付入力');
}
```
これで、以下のようなフォームが表示できるようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/c08d4537-e271-b4d3-d6c1-1ae7a09dcd5f.png)

# ボタンイベントの作成
フォーム上のボタンをクリックした際に、Google Apps Script側の関数を呼ぶようにします。
まずは、gsファイルの方に以下のコードを追加します。
```javascript
function buttonClick(firstDate, incrementMonth, incrementCycle) {
  // あとで作成する
}
```
続いて、htmlファイルのscriptタグ内に以下のコードを追加します。
```javascript
function handleButtonClick() {
　　　　// 処理が終了するまではボタンを押下不可に
  document.getElementById("btn").disabled = true;
  google.script.run
    .withSuccessHandler(function() {
    　　　　// 処理が終了したのでボタンを押下可に
      document.getElementById("btn").disabled = false;
    })
    .buttonClick(
      document.getElementById("firstDate").value,
      document.getElementById("incrementMonth").value,
      document.getElementById("incrementCycle").value
    );
}
```

ポイントとして、
このような形式でgs側の関数を呼ぶことができます。
```javascript
google.script.run.buttonClick()
```
また、処理成功時にコールバック関数を指定することもできます。
```javascript
google.script.run.withSuccessHandler(コールバック関数).buttonClick()
```

# 連続入力の処理作成
最後に、連続入力の処理を作成します。
gs側のbuttonClick関数を以下のように変更します。
```java
function buttonClick(firstDate, incrementMonth, incrementCycle) {

  incrementMonth = Number(convertToHalfWidth(incrementMonth));
  incrementCycle = Number(convertToHalfWidth(incrementCycle));

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var count = sheet.getSelection().getActiveRange().getNumRows();
  var currentDate = new Date(firstDate);
  var values = [];
  var inputtedCount = 0;
  var selectionTopRow = sheet.getSelection().getActiveRange().getRow();

  for (var i = 0; i < count; i++) {
    
    if (sheet.isRowHiddenByFilter(selectionTopRow + i)) {
      values.push([sheet.getSelection().getActiveRange().getCell(i + 1, 1).getValue()]);
      continue;
    }

    values.push([formatDate(currentDate, "yyyy/MM/dd")]);
    inputtedCount++;
    if (inputtedCount % incrementCycle === 0) {
      currentDate = isLastDate(currentDate)
          ? new Date(currentDate.getFullYear(), currentDate.getMonth() + 1 + incrementMonth, 0)
          : new Date(currentDate.getFullYear(), currentDate.getMonth() + incrementMonth, currentDate.getDate());
    }
  }

  sheet.getSelection().getActiveRange().setValues(values);
}
```
また、以下の関数も追加します。
```javascript
function formatDate(date, format) {
  format = format.replace(/yyyy/g, date.getFullYear());
  format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));
  format = format.replace(/dd/g, ('0' + date.getDate()).slice(-2));
  format = format.replace(/HH/g, ('0' + date.getHours()).slice(-2));
  format = format.replace(/mm/g, ('0' + date.getMinutes()).slice(-2));
  format = format.replace(/ss/g, ('0' + date.getSeconds()).slice(-2));
  format = format.replace(/SSS/g, ('00' + date.getMilliseconds()).slice(-3));
  return format;
};

function isLastDate(date) {
  return date.getDate() === new Date(date.getFullYear(), date.getMonth() + 1, 0).getDate();
}

function convertToHalfWidth(text) {
  var halfWidthText = text.replace(/[Ａ-Ｚａ-ｚ０-９]/g, function(match) {
    return String.fromCharCode(match.charCodeAt(0) - 0xFEE0);
  });
  return halfWidthText;
}
```
ポイントとして、
このようにしてフィルターで行が非表示になっているか判定することができます。
```javascript
sheet.isRowHiddenByFilter(selectionTopRow + i)
```
# 動作確認
連続入力したい行を事前に選択しておき、フォーム上で日付などを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/a19f83f0-92a2-8d80-89e1-541d7d76bb43.png)
その後、「選択範囲に入力」をクリックすると・・・
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2627583/0fec5294-e5d7-f3a4-c425-adc1481b9fce.png)
良い感じに連続入力できました！！