GASでGoogleSheetsを制御する

これは何？

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

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

条件に合う場合のみ別カラムのセルに値を入れる処理

• シート名で処理を反映させるシートを指定する
• 2行目から最終行にかけて一気に行う
  • この例では1行目は項目名が入力されているものとしている
  • C列のセルに[Aさん、Bさん、Cさん、Dさん、Eさん]のいずれかの場合、E列にチェックを入れる
• 負荷軽減のためレコード数が1000以上だと待機時間を設ける

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スプレッドシート上の見え方

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

日付表示を変換する処理

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

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以上だと待機時間を設ける

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スプレッドシート上の見え方

→ 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以上だと待機時間を設ける

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）

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スプレッドシート上での反映

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

装飾を自動で反映させる処理

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

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);
}

最後に

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