超簡単に複数のスプレッドシートを一括修正する方法

とある日、仕事の依頼で複数のスプレッドシートを一括で修正してほしいと言われました。

同じ境遇の人は少ないかもしれませんが、GAS（Google Apps Script）というExcelでいう"マクロ"や"VBA"みたいなものを使うことで簡単に実装できましたので共有します。

やりたいこと

・Googleドライブのあるフォルダに格納されているスプレッドシートが対象（※私のときは100以上のスプレッドシートがあり、10個くらい手作業で修正した時点で「あ、これ終わらんやつだ」と悟りました）

・いずれも一つのテンプレートとなるスプレッドシートをコピーして複製されており、中身の細かい情報は異なるものの、雛形は同じ（※イメージですが、社員ごとにスプレッドシートを雛形からコピーして作成し、それぞれの社員が中身をいじっている感じです。）

・「A1セルのデータをB4セルにもコピーして表示してほしい」みたいなことを上司から言われまして「え、雛形変えたところで、みんなコピーして使ってるから1件1件直さないといけないじゃん（絶望）」となっていたところです。

解決策

・GASを使って一括修正プログラムを作成しました。

・修正用のスプレッドを開く

・対象となるフォルダ（Googleドライブ）のURLを入力

・対象とするスプレッドシートをファイル名でフィルタ

・コピー元の範囲、ペースト先の範囲を入力し、自動コピペ実行

・実行結果をスプレッドシートにリストとして表示

こんな感じで実装しました。

コード公開

以下のようなコードを作成しました。スクリプトエディタにコピペすればOKです。

function getFilelist() {
 //フォルダのURLを入力してもらいIDを指定して、SpreadSheet の　シート１に、ファイル名と、URLを取得して、SpreadSheet に書込み
 try {
   var folder_name = Browser.inputBox("Google DriveのフォルダURLを入れてください");
   var folder_id = folder_name.replace("https://drive.google.com/drive/u/0/folders/", "");
   folder_id = folder_name.replace("https://drive.google.com/drive/folders/", "");

   folder = DriveApp.getFolderById(folder_id);
   files = folder.getFiles();
   list = [];　//この変数のファイル名・URLが入っていきます
   rowIndex = 1; // The starting row of a range.
   colIndex = 1; // The starting row of a column.
   var ss;
   var sheet;
   var range;
   sheetName = "シート1"; //デフォルトのシート名がシート１です。

   list.push(["ファイル名", "URL", "処理結果"]);

   let matchText = Browser.inputBox("以下の文字列をファイル名に含む場合のみ修正します。\\n無条件に修正を行う場合は空欄のままでOKをクリック。");
   if(matchText == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let copyRange = Browser.inputBox("コピーするセル範囲を入力してください。\\n例：A1, A1:B3など。");
   if(copyRange == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let pasteRange = Browser.inputBox("ペーストするセル範囲を入力してください。\\n例：A1, A1:B3など。");
   if(pasteRange == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   while (files.hasNext()) {
     var buff = files.next();
     if (buff.getName().match(RegExp(matchText)) && buff.getUrl().match(/spreadsheets/)) {
       if(copyToCellData(buff.getUrl(),copyRange,pasteRange)){
         list.push([buff.getName(), buff.getUrl(), "対象"]);
       } else {
         return;
       }        
     } else {
       list.push([buff.getName(), buff.getUrl(), "対象外"]);
     }
   };

   ss = SpreadsheetApp.getActive();
   sheet = ss.getSheetByName(sheetName);
   range = sheet.getRange(rowIndex, colIndex, list.length, list[0].length);

   // 対象の範囲にまとめて書き出します
   range.setValues(list);

 } catch (e) {
   Browser.msgBox(e);
 }
}

function copyToCellData(ssUrl,copyRange,pasteRange){
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   mySheet.getRange(copyRange).copyTo(mySheet.getRange(pasteRange));
   return true;

 } catch (e) {
   Browser.msgBox(e+"\\n実行をキャンセルします。");
   return false;
 }
}

function clearSheet() {
 //シート１を全部クリア
 try {
   var ss;
   var sheet;
   var range;
   sheetName = "シート1"; //デフォルトのシート名がシート１です。

   ss = SpreadsheetApp.getActive();
   sheet = ss.getSheetByName(sheetName);

   //　全クリア
   range = sheet.clear();

 } catch (e) {
   Browser.msgBox(e);
 }
}

//スプレッドシートを開いた際に呼び出される関数で、メニューを追加
function onOpen() {
 SpreadsheetApp
   .getActiveSpreadsheet()
   .addMenu("一括修正", [
     { name: "Driveフォルダ読み込み", functionName: "getFilelist" },
     { name: "シート1クリア", functionName: "clearSheet" },
   ]);
}

実際に使ってみた

スプレッドシートを開くとメニューバーに「一括修正」が表示されます。（※環境によっては表示されるまでに10秒ほど時間がかかることもあります。もし表示されない場合はこちらも確認してみてください）

クリックすると「Driveフォルダ読み込み」というボタンがありますので、こちらを更にクリックしてください。

するとこんな感じのポップアップが表示されます。

ここに、対象となるGoogleドライブのフォルダURLを入れてください。（※https://drive.google.com/drive/folders/xxxxxとなっているURL）

次に対象とするファイル名に含まれる文字列を指定する画面が表示されます。

最後にコピーするセル範囲とペーストするセル範囲を指定すれば実行されます。

実行結果は以下のようにスプレッドに記載されます。

「処理結果」が「対象」となっているスプレッドシートについて、コピーが完了していると思いますので、確認してみてください。

番外編：削除するスクリプトも作ってみた

今回作ってみたのはコピー＆ペーストを一括処理するスクリプトですが、単純に指定された範囲を削除するだけのスクリプトも作ってみました。

function copyAllFile() {
 //フォルダのURLを入力してもらいIDを指定して、SpreadSheet の　シート１に、ファイル名と、URLを取得して、SpreadSheet に書込み
 try {
   var folder_name = Browser.inputBox("Google DriveのフォルダURLを入れてください");
   var folder_id = folder_name.replace("https://drive.google.com/drive/u/0/folders/", "");
   folder_id = folder_name.replace("https://drive.google.com/drive/folders/", "");

   folder = DriveApp.getFolderById(folder_id);
   files = folder.getFiles();
   list = [];　//この変数のファイル名・URLが入っていきます
   rowIndex = 1; // The starting row of a range.
   colIndex = 1; // The starting row of a column.
   var ss;
   var sheet;
   var range;
   sheetName = "シート1"; //デフォルトのシート名がシート１です。

   list.push(["ファイル名", "URL", "処理結果"]);

   let matchText = Browser.inputBox("以下の文字列をファイル名に含む場合のみ修正します。\\n無条件に修正を行う場合は空欄のままでOKをクリック。");
   if(matchText == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let copyRange = Browser.inputBox("コピーするセル範囲を入力してください。\\n例：A1, A1:B3など。");
   if(copyRange == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let pasteRange = Browser.inputBox("ペーストするセル範囲を入力してください。\\n例：A1, A1:B3など。");
   if(pasteRange == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   while (files.hasNext()) {
     var buff = files.next();
     if (buff.getName().match(RegExp(matchText)) && buff.getUrl().match(/spreadsheets/)) {
       if(copyToCellData(buff.getUrl(),copyRange,pasteRange)){
         list.push([buff.getName(), buff.getUrl(), "対象"]);
       } else {
         return;
       }        
     } else {
       list.push([buff.getName(), buff.getUrl(), "対象外"]);
     }
   };

   ss = SpreadsheetApp.getActive();
   sheet = ss.getSheetByName(sheetName);
   range = sheet.getRange(rowIndex, colIndex, list.length, list[0].length);

   // 対象の範囲にまとめて書き出します
   range.setValues(list);

 } catch (e) {
   Browser.msgBox(e);
 }
}

function eraseAllFile() {
 //フォルダのURLを入力してもらいIDを指定して、SpreadSheet の　シート１に、ファイル名と、URLを取得して、SpreadSheet に書込み
 try {
   var folder_name = Browser.inputBox("Google DriveのフォルダURLを入れてください");
   var folder_id = folder_name.replace("https://drive.google.com/drive/u/0/folders/", "");
   folder_id = folder_name.replace("https://drive.google.com/drive/folders/", "");

   folder = DriveApp.getFolderById(folder_id);
   files = folder.getFiles();
   list = [];　//この変数のファイル名・URLが入っていきます
   rowIndex = 1; // The starting row of a range.
   colIndex = 1; // The starting row of a column.
   var ss;
   var sheet;
   var range;
   sheetName = "シート1"; //デフォルトのシート名がシート１です。

   list.push(["ファイル名", "URL", "処理結果"]);

   let matchText = Browser.inputBox("以下の文字列をファイル名に含む場合のみ修正します。\\n無条件に修正を行う場合は空欄のままでOKをクリック。");
   if(matchText == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let eraseRange = Browser.inputBox("削除するセル範囲を入力してください。\\n例：A1, A1:B3など。");
   if(eraseRange == "cancel"){
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   while (files.hasNext()) {
     var buff = files.next();
     if (buff.getName().match(RegExp(matchText)) && buff.getUrl().match(/spreadsheets/)) {
       if(eraseToCellData(buff.getUrl(),eraseRange)){
         list.push([buff.getName(), buff.getUrl(), "対象"]);
       } else {
         return;
       }        
     } else {
       list.push([buff.getName(), buff.getUrl(), "対象外"]);
     }
   };

   ss = SpreadsheetApp.getActive();
   sheet = ss.getSheetByName(sheetName);
   range = sheet.getRange(rowIndex, colIndex, list.length, list[0].length);

   // 対象の範囲にまとめて書き出します
   range.setValues(list);

 } catch (e) {
   Browser.msgBox(e);
 }
}

function copyToCellData(ssUrl,copyRange,pasteRange){
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   mySheet.getRange(copyRange).copyTo(mySheet.getRange(pasteRange));
   return true;

 } catch (e) {
   Browser.msgBox(e+"\\n実行をキャンセルします。");
   return false;
 }
}

function eraseToCellData(ssUrl,eraseRange){
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   mySheet.getRange(eraseRange).clear();
   return true;

 } catch (e) {
   Browser.msgBox(e+"\\n実行をキャンセルします。");
   return false;
 }
}

function clearSheet() {
 //シート１を全部クリア
 try {
   var ss;
   var sheet;
   var range;
   sheetName = "シート1"; //デフォルトのシート名がシート１です。

   ss = SpreadsheetApp.getActive();
   sheet = ss.getSheetByName(sheetName);

   //　全クリア
   range = sheet.clear();

 } catch (e) {
   Browser.msgBox(e);
 }
}

//スプレッドシートを開いた際に呼び出される関数で、メニューを追加
function onOpen() {
 SpreadsheetApp
   .getActiveSpreadsheet()
   .addMenu("一括修正", [
     { name: "一括コピペ", functionName: "copyAllFile" },
     { name: "一括削除", functionName: "eraseAllFile" },
     { name: "シート1クリア", functionName: "clearSheet" },
   ]);
}

こちらもスクリプトエディタにコピペすればOKです。

スプレッドシートを更新すると、以下のように「一括コピペ」「一括削除」というメニューが表示されるようになります。

この「一括削除」を選択すれば、指定した範囲のセルデータをクリアすることができます。

コピペではなく、削除をしたいというケースではこちらがオススメです。（というより、一括コピペは当初作った機能なので、こちらを導入しておけばコピペも削除できるようになります）

スクリプトを追加してもメニューが表示されない場合

スクリプトをコピペしてもスプレッドにメニューが表示されない場合は、権限が付与されていない可能性があります。

その場合は試しにApps Scriptでコードを実行すると解決します。

Apps Scriptで「onOpen」を選択し（何でも大丈夫です）、「実行」か「デバッグ」をクリックします。（※画像赤枠）

すると、以下のような権限付与の確認画面が表示されます。（すでに権限が付与されている場合は表示されないです。その場合は別の原因があるかもしれません…。）

権限を付与したあとは、スプレッドシートのページを更新するようにしてください。これでメニューが無事に表示されると思います。

もしここで権限が承認できない場合は、以下のページも参考になるかと思います。

【GAS(GoogleActionScript)】「このアプリは確認されていません」このアプリは、Google による確認が済んでいません。と表示される場合の対処法 | EGUWEB.JP@オンライン【GAS(GoogleActionScript)】「このアプリは確認されていません」このアプリは、Google による確認が済んでいません。と表示される場合の対処法

まとめ

手作業だと日が暮れてしまう作業をなんとかGASで出来ないかと思い試行錯誤した甲斐がありました。

こうした作業をする方がどれくらいいるか分かりませんが、少しでもお役に立てれば幸いです。

他にも「コピペじゃなくて、特定の文字列をいれて修正したい」とか、「特定のセルに数式を入れたい」とか、修正の指示にも色々あると思いますので、需要があれば作ってみようと思います！

ぜひ、コメント等で教えていただけますと幸いです＾＾

参考にさせていただいた記事

【GAS】Google Driveのフォルダ内のファイル名、URLを取得するSpreadSheet　汎用版【コピペプログラミング】【GAS】Google Driveのフォルダ内のファイル名、URLを取得するSpreadSheet　汎用版【コピペプログラミング】