Zenn
💭

【Google App Script】フォルダ内のファイルの全シート情報をスプレッドシートにコピーするスクリプト

2023/02/12に公開
GAS
Google Apps Script
tech

概要

下記のように、Googleドライブに複数ファイル、複数シートが保存されている状況を想定。その際、fフォルダ内に格納された全てのファイル、シート情報を別のスプレッドシートに書き込むスクリプトである。末尾にコード全体を記載しているので、お急ぎの方はそちらを使用してほしい。
folderToCopy/
　├ spreadsheet1 (sheet1/2/3)
　├ spreadsheet2 (sheet1/2/3)
　└ spreadsheet3 (sheet1/2/3)

フォルダを指定する

まずは、下記の通り「コピー内容を書き込むファイル」と「シート情報をコピーするファイルが格納されたフォルダ」を指定する。

// 書き込むファイルを指定
const writingSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
function getFiles(){

  // コピーするフォルダの指定
  const folderId= 'xxxxx';
  const folder = DriveApp.getFolderById(folderId);

ファイル情報を取得する

次にフォルダ内のファイル情報を取得する。

  • フォルダ内のファイル情報をfolder.getFiles()で取得する。
  • 取得したファイル情報について、fileListおよびfileNameList配列に格納する。
  • 格納する情報は、それぞれID情報・ファイル名である。
  • 情報を格納するため、それぞれ一番最後に.push(fileId) or (fileName)で格納している。
  //フォルダ内のすべてのファイルを取得
  const files = folder.getFiles();
  const fileList = [];
  const fileNameList = [];
  
    //各ファイルごとに出力
  while(files.hasNext()){
    let file = files.next();
    let fileName = file.getName(); // ファイル名
    let fileId = file.getId(); // ファイルID
    let fileURL = file.getUrl(); // ファイルURL
    fileList.push(fileId)
    fileNameList.push(fileName)
  }

各ファイルのシート情報を取得しコピーする。

改善の余地多々ありだが、コードの流れを説明する。

  • 先ほど作成したfileListにして、loopを回して各ファイル情報を取得する。
  • 取得したファイル情報にて、シート情報を取得しそれに対してloopを回すという流れである。
  • シートの全情報が取得できたら、それに対して取得するファイル情報を決めている。下記コードでは、let data = sheet1.getDataRange().getValues();　としてシート内の全情報を取得している。
  • 次に、取得したシート情報を書き込む。書き込む先の位置については、次の内容で指定している。writingSheet.getRange(writingSheet.getDataRange().getValues().length+1, 1, data.length, data[0].length).setValues(data);
  • getValues / setValues / getValue / setValueについては、下記文献に詳しく書いてある。もしくは、chatGPTに聞くのが手っ取り早いかも。 
　// 上記で保存したfileListという配列に対して、loopを回して各ファイル情報を出力する。
  for(let n = 0; n < fileList.length; n++){
  　// 各ファイル情報に対して、その全シート情報を取得する
    const sheets = SpreadsheetApp.openById(fileList[n]).getSheets();
    // 取得した全シート情報に対して、loopを回してシート情報を主とkするう。
    for(let i = 0; i < sheets.length; i++) {
    // 各ファイルのIDを指定して、そのシートIDを指定している。その上で、シートの全情報を取得する。
      let sheet1 = SpreadsheetApp.openById(fileList[n]).getSheetByName(sheets[i].getName());
      let data = sheet1.getDataRange().getValues();
    // コピーした情報を書き込む先のファイル情報を取得している。取得した情報を書き込む先のセル情報について、getRangeで指定している。
      writingSheet.getRange(writingSheet.getDataRange().getValues().length+1, 1, data.length, data[0].length).setValues(data);
    // K列に対して、ファイル名を書き込んでいる。
      writingSheet.getRange(writingSheet.getDataRange().getValues().length-data.length+2, 11, data.length-1, 1).setValue(fileNameList[n]);
    // L列に対して、シート名を書き込んでいる。
      writingSheet.getRange(writingSheet.getDataRange().getValues().length-data.length+2, 12, data.length-1, 1).setValue(sheets[i].getName());
    }
  }
}

コード全体

// 書き込むファイルを指定
const writingSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
function getFiles(){

  // コピーするフォルダの指定
  const folderId= 'xxxxx';
  const folder = DriveApp.getFolderById(folderId);
  //フォルダ内のすべてのファイルを取得
  const files = folder.getFiles();
  const fileList = [];
  const fileNameList = [];
  
  //各ファイルごとに出力
  while(files.hasNext()){
    
    let file = files.next();
    let fileName = file.getName(); // ファイル名
    let fileId = file.getId(); // ファイルID
    let fileURL = file.getUrl(); // ファイルURL
    fileList.push(fileId)
    fileNameList.push(fileName)
  }
  
  for(let n = 0; n < fileList.length; n++){
    const sheets = SpreadsheetApp.openById(fileList[n]).getSheets();
    for(let i = 0; i < sheets.length; i++) {
      let sheet1 = SpreadsheetApp.openById(fileList[n]).getSheetByName(sheets[i].getName());
      let data = sheet1.getDataRange().getValues();
      writingSheet.getRange(writingSheet.getDataRange().getValues().length+1, 1, data.length, data[0].length).setValues(data);
      writingSheet.getRange(writingSheet.getDataRange().getValues().length-data.length+2, 11, data.length-1, 1).setValue(fileNameList[n]);
      writingSheet.getRange(writingSheet.getDataRange().getValues().length-data.length+2, 12, data.length-1, 1).setValue(sheets[i].getName());
    }
  }
}

Discussion