【GAS】Google Apps Script 活用事例　各シート内容の説明を残したまま、スプレッドシート内の目次を作成するスクリプト

E列のシート内容のみ、チームの各々のメンバーに手入力で更新してもらい、最新のシート名と並び順が、目次の並び順と常に一致するスクリプトを書きました。

ロジック

1.　全てのシート名、URLを取得して貼り付ける
2.　更新日時でsortする
3.　重複削除前の情報を一旦取得する
4.　重複削除前の2次元配列からE列が空白以外の行を取得する
5.　URLが一致したら、シート内容を転記する

スクリプト全文はこんな感じ

/**
* スプレッドシート内に非表示のシートを含め、目次を作成する
* 
*/

function generateSheetIndex() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const baseUrl     = spreadsheet.getUrl();

 //シートをアクティブにして、左から1番目に移動する
 const indexSheet = spreadsheet.getSheetByName('目次');
 spreadsheet.setActiveSheet(indexSheet, true);
 spreadsheet.moveActiveSheet(1);

 //全てのシートを取得
 const sheets = spreadsheet.getSheets();
 const values = [['No.', 'シート名', 'URL', '更新日時', 'シート内容']];


 // 目次シートを除くため i は 1 から開始
 for(let i = 1; i < sheets.length; i++) {

   /*必要に応じて、ここから後述のスクリプトと入れ替え*/

   const number    = i;
   const sheet     = sheets[i];
   const sheetName = sheet.getName();
   const d         = new Date();
   const date      = Utilities.formatDate(d, 'JST', 'yyyy/MM/dd HH:mm');
   const sheetUrl  = baseUrl + '#gid=' + sheet.getSheetId();
   values.push([number, sheetName, sheetUrl, date]);

   /*ここまで*/

 }//for

 const lastRow = indexSheet.getLastRow() + 1;
 console.log(`最終行：　${lastRow}`);

 //初回　目次シートが白紙の場合
 if(lastRow === 1){
   indexSheet.getRange(1, 1, values.length, values[0].length).setValues(values);

 }else if(1 < lastRow){
 
   //2回目以降の実行　新しく追加されたシートだけが最終行に追加される
   //見出し行を削除後、シート名、URLなどを貼り付け
   const headerRow = values.shift(); 
   indexSheet.getRange(lastRow, 1, values.length, values[0].length).setValues(values);


   //主にfor文内で使用
   const index = {
     number:    headerRow.indexOf('No.'),
     sheetName: headerRow.indexOf('シート名'),
     url:       headerRow.indexOf('URL'),
     date:      headerRow.indexOf('更新日時'),
     contents:  headerRow.indexOf('シート内容')
   };
   console.log(index);


   //getRangeの指定で使用
   const column = {
     date:     index.date + 1,
     contents: index.contents + 1,
     url:      index.url + 1
   };

   console.log(column);


   //シート内容の記載がある行だけを取得
   const range    = indexSheet.getRange(2, 1, indexSheet.getLastRow() -1, indexSheet.getLastColumn());
   const original = range.getDisplayValues();

   //シート内容についての説明が入力されている箇所を取得
   const filtered = original.filter(row => row[index.contents]);
   console.log(`index.contents ${index.contents}`);
   console.log(filtered);

   //2行目以降を、更新日時でsortする
   range.sort([
     {column: column.date, ascending: false}
   ]);

   //URLが一致する行を重複とみなし削除する。
   range.removeDuplicates([column.url]);

   //重複削除後のシートの値を取得する
   const cleanedValues = indexSheet.getDataRange().getDisplayValues();

   console.log(`シートの枚数：　${sheets.length}`);

   for(let i = 0; i < filtered.length; i++){
     for(let j = 0; j < cleanedValues.length; j++){
     //jがシートの枚数以上になったら、処理を中断する
     //目次シートの分マイナスする
       if(sheets.length < j)break;

       //urlが一致したら、setValuesで貼り付け
       if(filtered[i][index.url] === cleanedValues[j][index.url]){
         const row = j + 1;
         const targetRange = indexSheet.getRange(row, column.contents);

         console.log(`${filtered[i][index.sheetName]} === ${cleanedValues[j][index.sheetName]}`);
         console.log(`${targetRange.getA1Notation()}`);
         targetRange.setValue(filtered[i][index.contents]);

       }//if
     }//for_j
   }//for_i
 }//else
}//end

​

非表示を含む全てのシートに連番を振るスクリプト

   //シート名に連番をつける
   const number = i;
   const originalSheetName = sheet.getName().replace(/.*】/, '');
   sheet.setName(`【 ${number} 】${originalSheetName}`);


   //リネーム後のシート名を取得
   const newSheetName      = sheet.getName();
   const d                 = new Date();
   const date              = Utilities.formatDate(d, 'JST', 'yyyy/MM/dd HH:mm');
   const sheetUrl          = baseUrl + '#gid=' + sheet.getSheetId();
   values.push([number, newSheetName, sheetUrl, date]);
   

入れ替え場所の目安については、下記文言をCtrl + Fで探してみてください。
/*必要に応じて、ここから後述のスクリプトと入れ替え*/　

今現在、表示中のシートにのみ連番を振るスクリプト

function setSheetNewName(){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheets      = spreadsheet.getSheets();
 let number        = 0;
 let string        = '';

 for(const sheet of sheets){
   if(sheet.isSheetHidden() === true){

     console.log(`${sheet.getName()}　は非表示です。`);
     string += `${sheet.getName()}　は非表示のため、スキップしました。\\n`;

     continue;

   }else if(sheet.getName() !== '目次'){
     number += 1;
     const originalSheetName = sheet.getName().replace(/.*】/, '');
     const newName           = `【 ${number} 】${originalSheetName}`;
     sheet.setName(newName);
     
     console.log(`${originalSheetName} → ${sheet.getName()}　にリネーム完了\\n`);
     string += `${originalSheetName} → ${sheet.getName()}　にリネーム完了\\n`;
   }
 }
 Browser.msgBox(string);
}

全てのシート名から連番を一旦消す

function setSheetNewName2(){
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheets      = spreadsheet.getSheets();
 for(const sheet of sheets){
   const original = sheet.getName().replace(/.*】/, '');
   sheet.setName(original);
 }
}

実務で使うことないと思っていたけど、スクリプトがどんなケースでも上手くいくかシートの並び順を順不同にしたいというニーズが生じました。