こんにちは！
DIVstudio　RYO-Uです

以前から「なんとかならないかな～？」って思っていた
複数あるスプレッドシートを決まったフォーマットに
自動で変換するシステムをＧＡＳで構築してみました

行いたい業務の概要は

スプレッドシートB
スプレッドシートC　から必要な情報を抜き出し
↓
スプレッドシートAに決めれられたフォーマットで書き出し

今回はGAS（Google Apps Script）を使用して行いました
日常的にGASは使用していないので
ChatGPTにかなり助けてもらいました・・・ｗ

まずは
スプレッドシートに空シート「Ａ」「Ｂ」「Ｃ」「Ｄ」を作成
※なんで「Ｄ」も作成するのかはこの後説明・・

「拡張機能」→　Google Apps Script起動

シート「B」「C」に抜き出したい各CSVデータをインポート

・まずはシートBのデータをシートAに書き出し

function removeApostrophe() {　// シートBのデータをシートAに書き出す
  var sheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートA");
  var sheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートB");
  var lastRowB = sheetB.getLastRow();
  var rangeB = sheetB.getRange(2, 2, lastRowB-1, 1); // B2から最終行までの範囲を取得
  var dataB = rangeB.getValues();
  
  // A列にデータを書き込む
  for (var i = 0; i < dataB.length; i++) {
    sheetA.getRange(i+1, 1).setValue(dataB[i][0].replace(/'$/, ""));
  }
}

・次にシートCのデータをシートDに書き出し
※シートCのデータは日付と時間が別々に記載してあるデータで
希望は日付と時間を一緒にしたいのでA＋BでシートDに書き出し

function replaceSlash2() {　// シートCの日付データをA＋Bで取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートC");
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
  const newData = data.map(row => {
    const date = new Date(row[0]);
    const formattedDate = Utilities.formatDate(date, 'JST', 'yyyy-MM-dd');
    const time = row[1] ? row[1].toString().replace(/:/g, '') : "";
    const formattedTime = time.slice(0, 2) + ":" + time.slice(2, 4);
    return [formattedDate + " " + formattedTime];
  });
  const destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートD");
  destSheet.getRange(1, 1, newData.length, 1).setValues(newData);
}

別のコードで何度もトライしましたがシートCにある「日付」と「時間」は
文字列？が違うためなのか「年/月/日 時:分:秒」としたいのにできなかった・・・
なので「年/月/日 時:分:秒」を書き出すため一旦シートDに書き出すことにしました。

・次にシートDに書き出した「年/月/日 時:分:秒」は必要ではない情報もまとめて書き出しいるため検索用に最初と同じ「シートCのデータをシートDに書き出し」を実行

function writeColumnC() {　// シートDにシートCのC列データを書き出し
  const srcSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートC");
  const data = srcSheet.getRange(2, 3, srcSheet.getLastRow() - 1, 1).getValues();
  const destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートD");
  destSheet.getRange(1, 3, data.length, 1).setValues(data);
}

・そして先程の検索用情報をシート「A」と「D」で比べて同データの行の「年/月/日 時:分:秒」を抜き出します。

function writeDataToSheetA() {　// シートAにシートDのA列のデータを書き出し
  const sheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シートA');
  const sheetD = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シートD');
  
  const dataA = sheetA.getRange(1, 1, sheetA.getLastRow(), 1).getValues();
  const dataD = sheetD.getRange(1, 3, sheetD.getLastRow(), 1).getValues();
  
  const outputData = [];
  for (let i = 0; i < dataA.length; i++) {
    for (let j = 0; j < dataD.length; j++) {
      if (dataA[i][0] == dataD[j][0]) {
        outputData.push([sheetD.getRange(j + 1, 1).getValue()]);
        break;
      }
    }
  }
  
  sheetA.getRange(1, 12, outputData.length, 1).setValues(outputData);
}

・あとは上と同じ手順でシートAにシートCの情報を書き出し

function copyNameData() {　// シートAにシートCの名前を書き出し
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = ss.getSheetByName('シートA');
  var sheetC = ss.getSheetByName('シートC');
  var dataA = sheetA.getDataRange().getValues();
  var lastRowA = sheetA.getLastRow();
  var lastColA = sheetA.getLastColumn();
  var dataC = sheetC.getRange(1, 3, sheetC.getLastRow(), 1).getValues();
  var lastRowC = sheetC.getLastRow();
  var isFound = false;

  for (var row = 0; row < lastRowA; row++) {
    isFound = false;
    for (var col = 0; col < lastColA; col++) {
      var cellA = sheetA.getRange(row+1, col+1);
      var valueA = cellA.getValue();
      
      for (var i = 0; i < lastRowC; i++) {
        var valueC = dataC[i][0];
          
        if (valueA === valueC) {
          sheetA.getRange(row+1, 3).setValue(sheetC.getRange(i+1, 6).getValue());
          isFound = true;
          break;
        }
      }
      if (isFound) {
        break;
      }
    }
  }
}

・シートAにシートCの別の情報を書き出し

function copytotalamountData() {　// シートAにシートCの合計金額を書き出し
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = ss.getSheetByName('シートA');
  var sheetC = ss.getSheetByName('シートC');
  var dataA = sheetA.getDataRange().getValues();
  var lastRowA = sheetA.getLastRow();
  var lastColA = sheetA.getLastColumn();
  var dataC = sheetC.getRange(1, 3, sheetC.getLastRow(), 1).getValues();
  var dataAJ = sheetC.getRange(1, 36, sheetC.getLastRow(), 1).getValues(); // AJ列のデータを取得
  var lastRowC = sheetC.getLastRow();
  var isFound = false;

  for (var row = 0; row < lastRowA; row++) {
    isFound = false;
    for (var col = 0; col < lastColA; col++) {
      var cellA = sheetA.getRange(row+1, col+1);
      var valueA = cellA.getValue();
      
      for (var i = 0; i < lastRowC; i++) {
        var valueC = dataC[i][0];
          
        if (valueA === valueC) {
          sheetA.getRange(row+1, 7).setValue(dataAJ[i][0]); // AJ列のデータをG列に書き込む
          isFound = true;
          break;
        }
      }
      if (isFound) {
        break;
      }
    }
  }
}

・最後に他の必要な列に数値を挿入

function insertValues() {　// シートAのHIJKNQRMに数値入力
  // データが入っているシートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シートA");

  // データが入っている行数を取得
  var lastRow = sheet.getLastRow();

  // H列、I列、J列、K列、N列、Q列、R列、の各列に値を挿入
  sheet.getRange("H1:H" + lastRow).setValue(1);
  sheet.getRange("I1:I" + lastRow).setValue(1);
  sheet.getRange("J1:J" + lastRow).setValue(2);
  sheet.getRange("K1:K" + lastRow).setValue(1);
  sheet.getRange("N1:N" + lastRow).setValue(1);
  sheet.getRange("Q1:Q" + lastRow).setValue(1);
  sheet.getRange("R1:R" + lastRow).setValue(1);
  sheet.getRange("M1:M" + lastRow).setValue(0);
}

希望のスプレッドシートのフォーマットに自動で行うことができました

もっと簡単にするコードはあると思いますが
個人的には満足ですｗ

これで毎月の業務がかなり短縮されました！

これからも不定期で業務効率化のシステムを組んでみたいと思います。