GASでGoogleSheetsを制御する

# これは何？
- Google App ScriptでGoogle Sheetsを制御するためのスクリプト例です
    - 経常業務に掛かる稼働の軽減を図ることを目的としています

※ Google Sheets（Googleスプレッドシート）はGoogle LLCの商標です

## 背景
Jira Software上でチケットを作成して動くことが多く、個人タスクに限らずチームの進行状況等をより可視化し易くするためにPluginを介して日次でGoogle Sheetsに出力しています。

シートに出力されたデータを集計などにそのまま使おうとするとエラーが出まくって大変なので、それらをいい感じに調整するといったシチュエーションで活躍するスクリプトを作ることにしましt

## 条件に合う場合のみ別カラムのセルに値を入れる処理
- ```シート名```で処理を反映させるシートを指定する
- 2行目から最終行にかけて一気に行う
    - この例では1行目は項目名が入力されているものとしている
    - C列のセルに[Aさん、Bさん、Cさん、Dさん、Eさん]のいずれかの場合、E列にチェックを入れる
- 負荷軽減のためレコード数が1000以上だと待機時間を設ける
```javascript
function myFunction() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  const lastRow = sheet.getLastRow();
  const BATCH_SIZE = 1000; // Number of rows processed at once
  
  for (let startRow = 2; startRow <= lastRow; startRow += BATCH_SIZE) {
    const currentBatchSize = Math.min(BATCH_SIZE, lastRow - startRow + 1);
    
    // Get a data for batch size
    const reporters = sheet.getRange(startRow, 4, currentBatchSize, 1).getValues();
    
    // Array of check mark decisions
    const checkmarks = reporters.map(([reporter]) => {
      return [reporter.match(/Aさん|Bさん|Cさん|Dさん|Eさん/) ? '' : '✅'];
    });
    
    // Set a batch results
    sheet.getRange(startRow, 5, currentBatchSize, 1).setValues(checkmarks);
    
    // Load balancing for large data processing
    if (currentBatchSize === BATCH_SIZE) {
      Utilities.sleep(500);
    }
  }
}

```

### Googleスプレッドシート上の見え方

![sample_sheet.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935367/b40c93c7-7648-85b3-08ed-7a254708b6ce.png)

→ E列で条件に合致するセルのみチェックを入れる

## 日付表示を変換する処理
- E列に日時が```yyyy/MM/dd HH:mm:ss```の形式で表示されている
- ```シート名```で処理を反映させるシートを指定する
- 2行目から最終行にかけて一気に処理する
    - E列の値を取得する
    - E列の値を```yyyy/mm/dd```に変換する
- 負荷軽減のためレコード数が1000以上だと待機時間を設ける

```javascript
function myFunction() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  const lastRow = sheet.getLastRow();
  const BATCH_SIZE = 1000; // Number of rows processed at once
  
  for (let startRow = 2; startRow <= lastRow; startRow += BATCH_SIZE) {
    const currentBatchSize = Math.min(BATCH_SIZE, lastRow - startRow + 1);
    const dateRange = sheet.getRange(startRow, 5, currentBatchSize, 1);
    const values = dateRange.getValues();
    
    const formattedDates = values.map(([date]) => 
      date ? [Utilities.formatDate(date, 'JST', 'yyyy/MM/dd')] : ['']
    );
    
    dateRange.setValues(formattedDates);
    
    // Load balancing for large data processing
    if (currentBatchSize === BATCH_SIZE) {
      Utilities.sleep(1000);
    }
  }
}

```

## 異なるカラム間の日付差分を表示する処理
- ```シート名```で処理を反映させるシートを指定する
- 2行目から最終行にかけて一気に処理する
    - B列とC列の値を取得する
    - B列とC列の差分を日付で換算してD列に格納する
        - 1日未満の場合は0日とする
- 負荷軽減のためレコード数が1000以上だと待機時間を設ける

```javascript
function calculateDays() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  const lastRow = sheet.getLastRow();
  const BATCH_SIZE = 1000; // Number of rows processed at once

  // Process the second to last line
  for (let startRow = 2; startRow <= lastRow; startRow += BATCH_SIZE) {
    const currentBatchSize = Math.min(BATCH_SIZE, lastRow - startRow + 1);
    
    // Get data from columns B and C together
    const dateRange = sheet.getRange(startRow, 2, currentBatchSize, 2);
    const dates = dateRange.getValues();
    
    // Calculate the difference
    const differences = dates.map(([createDate, firstResponseDate]) => {
      if (createDate && firstResponseDate) {
        return [Math.floor((firstResponseDate - createDate) / (1000 * 60 * 60 * 24))];
      }
      return [''];  // If either date is empty, set empty value
    });
    
    // Set calculation results in column D together
    const differenceRange = sheet.getRange(startRow, 4, currentBatchSize, 1);
    differenceRange.setValues(differences);
    
    // Load balancing for large data processing
    if (currentBatchSize === BATCH_SIZE) {
      Utilities.sleep(100);
    }
  }
}

```

### Googleスプレッドシート上の見え方
![differ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935367/5219c1a5-492d-c85f-3237-82680ad162b6.png)

→ D列に日数の差分が入る

## 日付表示の変更と装飾の反映をまとめて処理する
- E列に問い合わせ日が```yyyy/MM/dd HH:mm:ss```の形式で入っている
    - G列に回答日の値がdate形式で入っている
- ```シート名```で処理を反映させるシートを指定する
- 2行目から最終行にかけて一気に処理する
    - E列の値を取得する
    - E列の値を```yyyy/mm/dd```に変換する
- 1行目の背景色を灰色に設定
- フォントタイプを「M PLUS 1p」に設定
- 2行目以降の背景色を白に設定
- フォントの垂直方向の配置を中央「middle」に設定
- 全てのセルに枠線を引く
- 1行目上下の枠線のみ2pxに設定
- 負荷軽減のためレコード数が500以上だと待機時間を設ける

```javascript
function formatAndDecorateSheet() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  
  // Decoration of header row
  const headerRange = sheet.getRange(1, 1, 1, lastColumn);
  headerRange.setBackground('#b7b7b7')
             .setFontWeight('bold')
             .setFontSize(11)
             .setFontFamily('M PLUS 1p')
             .setVerticalAlignment('middle')
             .setBorder(true, true, true, true, true, true)
             .setBorder(null, null, true, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  // Processing of data rows
  const BATCH_SIZE = 500;
  
  for (let startRow = 2; startRow <= lastRow; startRow += BATCH_SIZE) {
    const currentBatchSize = Math.min(BATCH_SIZE, lastRow - startRow + 1);
    const batchRange = sheet.getRange(startRow, 1, currentBatchSize, lastColumn);
    
    // Change date format of column E
    const createDateRange = sheet.getRange(startRow, 5, currentBatchSize, 1);
    const createDateValues = createDateRange.getValues();
    const formattedCreateDates = createDateValues.map(([date]) => 
      date ? [Utilities.formatDate(date, 'JST', 'yyyy/MM/dd')] : ['']
    );
    createDateRange.setValues(formattedCreateDates);
    
    // Change date format of column G
    const responseDateRange = sheet.getRange(startRow, 7, currentBatchSize, 1);
    const responseDateValues = responseDateRange.getValues();
    const formattedResponseDates = responseDateValues.map(([date]) => 
      date ? [Utilities.formatDate(date, 'JST', 'yyyy/MM/dd')] : ['']
    );
    responseDateRange.setValues(formattedResponseDates);
    
    // Applying Decoration
    batchRange.setBackground('#ffffff')
              .setFontFamily('M PLUS 1p')
              .setVerticalAlignment('middle')
              .setBorder(true, true, true, true, true, true);
    
    // A short pause after heavy processing
    if (currentBatchSize === BATCH_SIZE) {
      Utilities.sleep(500);
    }
  }
}

```


## カウント関数を自動実行する処理
- C列に担当者のカラム
- E列は遭遇回数を入れる予定のカラム（null）

```javascript
function countTextOccurrencesPerRow() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名");

  var dataRange = sheet.getRange("C2:C" + sheet.getLastRow());
  var data = dataRange.getValues();
  var outputData = [];

  for (var i = 0; i < data.length; i++) {
    var textToCount = data[i][0];
    var count = 0;

    for (var j = 0; j < data.length; j++) {
      if (data[j][0] === textToCount) {
        count++;
      }
    }

    outputData.push([count]);
  }

  var outputRange = sheet.getRange(2, 5, outputData.length);
  outputRange.setValues(outputData);
}

```

### Googleスプレッドシート上での反映

![sample_count.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/935367/587f3881-7c2c-1c3b-85d4-d322c2a3ac1d.png)

→ C列の担当者を元に一致するレコード上のE列のセルに集計結果を埋める

## 装飾を自動で反映させる処理
- ```シート名```で処理を反映させるシートを指定する
- 1行目の背景色を灰色に設定
- フォントタイプを「M PLUS 1p」に設定
- 2行目以降の背景色を白に設定
- フォントの垂直方向の配置を中央「middle」に設定
- 全てのセルに枠線を引く
- 1行目上下の枠線のみ2pxに設定

```javascript
function decorateSpreadsheet() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  
  // 1行目の背景色を設定
  var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  firstRowRange.setBackground('#b7b7b7');
  firstRowRange.setFontWeight('bold');
  firstRowRange.setFontSize(11);
  
  // フォントタイプを設定
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setFontFamily('M PLUS 1p');

  // 2行目以降の背景色を設定
  var otherRowsRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  otherRowsRange.setBackground('#ffffff');
  
  // 垂直方向の配置を中央に設定
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setVerticalAlignment('middle');
  
  // 全てのセルに枠線を引く
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setBorder(true, true, true, true, true, true);

  // 1行目上下の枠線を2pxに設定
  sheet.getRange(1, 1, 1, sheet.getLastColumn()).setBorder(null, null, true, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}

```


# 最後に
経常業務を楽にし、本来掛けるべきことに労力を費やせるようになることを祈ります！