超簡単に複数のシートの行/列を増やしたり、削除したり、一括でコピペする方法

前回、複数のスプレッドシートを一括で扱うためのGASのコードを公開して、ありがたいことにコメントを頂くことができました。

活用してくださった皆様、本当にありがとうございます🤗

「一通りググってみたけど、うまいやり方が出てこないなぁ」

「しかたない、、もしかしたら車輪の再発明（言いたいだけ）みたいなことになるかもしれないが作ってみるか…」

「せっかく作ったし、知識を独り占めしてても仕方がない。記事にでもしてみるか。」

そんな経緯で作成したものですが、Googleという大海原か、あるいはnoteの中でどうにかして見つけてくださったのか、思いの外、記事のビュー数が付いていることに驚いております（笑）

ありがたいことにコメントの中で、「"複数のスプレッド"を一括で操作するのではなく、"一つのスプレッドに含まれる複数のシート"を一括で操作できないか」という新たなミッションを頂きましたので、早速作ってみました。

この記事を読むと解決できること

・一つのスプレッドシートの中に複数のシート（ワークシート、シートタブ、などとも呼ぶようです。スプレッドシートのシートってすごい分かりづらい🤨）があり、同じようなフォーマットで管理している。（例えば、各店舗の売上台帳や、各商品の在庫管理、みたいなもの）

・各シートの同じ場所に新たな行や列を追加したい（例えば、行ごとに毎月売上を記入している場合や、列ごとに商品の在庫を管理している場合など）

・あるいは、各シートの行や列をまるっとコピーして、新たな行と列を挿入してペーストしたい

解決策

今回も前回と同様にGAS（Google Apps Script）というExcelでいう"マクロ"や"VBA"みたいなものを使うことで実装することができました。

実行の流れとしては、

・修正したいスプレッドシートのURLを入力

・スプレッドシートの全シートをすべて取得

・各シートごとに行や列の挿入、削除、コピペなどを行う

といったシンプルなものです。

（シンプルな割にコードが長ったらしくて読みづらいと思いますが、ご勘弁ください…。むしろ、もっと良いコードがあったら教えて下さい🙇‍♂️🙇‍♂️）

コード公開

以下のようなコードを作成しました。新しくスプレッドシートを作成してスクリプトエディタを開き、そこにコピペすればOKです。

修正したいスプレッドシートに都度コピペするのは面倒だと思ったので、都度修正したいスプレッドシートのURLの入力を受け付けるようにしています。

function insertRowsAllSheets() {
 try {
   var fileUrl = Browser.inputBox("スプレッドシートのURLを入力してください。");

   let insertPosition = Browser.inputBox("挿入する行番号を入力してください。");
   if (insertPosition == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let numOfRows = Browser.inputBox("挿入する行数を入力してください。\\n");
   if (numOfRows == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   if (insertRows(fileUrl, insertPosition, numOfRows)) {
     Browser.msgBox("処理が正常に完了しました。\\n");
   } else {
     return;
   }

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

function insertRows(ssUrl, insertPosition, numOfRows) {
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   var sheets = mySheet.getSheets();
   for (i = 0; i < mySheet.getSheets().length; i++) {
     sheets[i].insertRows(insertPosition, numOfRows);
   }
   return true;

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

function insertColumnsAllSheets() {
 try {
   var fileUrl = Browser.inputBox("スプレッドシートのURLを入力してください。");

   let insertPosition = Browser.inputBox("挿入する列番号(数字のみ)を入力してください。\\n例：A列→1、C列→3");
   if (insertPosition == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let numOfColumns = Browser.inputBox("挿入する列数を入力してください。\\n");
   if (numOfColumns == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   if (insertColumns(fileUrl, insertPosition, numOfColumns)) {
     Browser.msgBox("処理が正常に完了しました。\\n");
   } else {
     return;
   }

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

function insertColumns(ssUrl, insertPosition, numOfColumns) {
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   var sheets = mySheet.getSheets();
   for (i = 0; i < mySheet.getSheets().length; i++) {
     sheets[i].insertColumns(insertPosition, numOfColumns);
   }
   return true;

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

function eraseRowsAllSheets() {
 try {
   var fileUrl = Browser.inputBox("スプレッドシートのURLを入力してください。");

   let erasePosition = Browser.inputBox("削除を開始する行番号を入力してください。");
   if (erasePosition == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let numOfRows = Browser.inputBox("削除する行数を入力してください。\\n");
   if (numOfRows == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   if (eraseRows(fileUrl, erasePosition, numOfRows)) {
     Browser.msgBox("処理が正常に完了しました。\\n");
   } else {
     return;
   }

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

function eraseRows(ssUrl, erasePosition, numOfRows) {
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   var sheets = mySheet.getSheets();
   for (i = 0; i < mySheet.getSheets().length; i++) {
     sheets[i].deleteRows(erasePosition, numOfRows);
   }
   return true;

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

function eraseColumnsAllSheets() {
 try {
   var fileUrl = Browser.inputBox("スプレッドシートのURLを入力してください。");

   let erasePosition = Browser.inputBox("削除を開始する列番号を入力してください。");
   if (erasePosition == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   let numOfColumns = Browser.inputBox("削除する列数を入力してください。\\n");
   if (numOfColumns == "cancel") {
     Browser.msgBox("実行をキャンセルします。");
     return;
   }

   if (eraseColumns(fileUrl, erasePosition, numOfColumns)) {
     Browser.msgBox("処理が正常に完了しました。\\n");
   } else {
     return;
   }

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

function eraseColumns(ssUrl, erasePosition, numOfColumns) {
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   var sheets = mySheet.getSheets();
   for (i = 0; i < mySheet.getSheets().length; i++) {
     sheets[i].deleteColumns(erasePosition, numOfColumns);
   }
   return true;

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

function copyAndPasteAllSheets() {
 try {
   var fileUrl = Browser.inputBox("スプレッドシートのURLを入力してください。");

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

   if (copyAndPaste(fileUrl, copyRange, pasteRange)) {
     Browser.msgBox("処理が正常に完了しました。\\n");
   } else {
     return;
   }

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

function copyAndPaste(ssUrl, copyRange, pasteRange) {
 try {
   let mySheet = SpreadsheetApp.openByUrl(ssUrl);
   var sheets = mySheet.getSheets();
   for (i = 0; i < mySheet.getSheets().length; i++) {
     sheets[i].getRange(copyRange).copyTo(sheets[i].getRange(pasteRange));
   }
   return true;

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

//スプレッドシートを開いた際に呼び出される関数で、メニューを追加
function onOpen() {
 SpreadsheetApp
   .getActiveSpreadsheet()
   .addMenu("一括修正", [
     { name: "全シート行挿入", functionName: "insertRowsAllSheets" },
     { name: "全シート列挿入", functionName: "insertColumnsAllSheets" },
     { name: "全シート行削除", functionName: "eraseRowsAllSheets" },
     { name: "全シート列削除", functionName: "eraseColumnsAllSheets" },
     { name: "全シート一括コピペ", functionName: "copyAndPasteAllSheets" },
   ]);
}

実際に使ってみた

スプレッドシートを開くとメニューバーに「一括修正」という項目が自動で追加されます。（若干ラグがあるので表示までに10秒程度かかるかもしれませんが、それでも表示されない場合はこちらを確認してみてください。）

「一括修正」をクリックすると、更に項目が下に表示されます。

・全シート行/列挿入…すべてのシートの同じ場所に行・列を挿入します。

・全シート行/列削除…すべてのシートの同じ場所の行・列を削除します。

・全シート一括コピペ…すべてのシートの同じ場所をコピーし、別で指定する同じ場所にペーストします。

こんな感じの機能になっています。試しに「全シート行挿入」をクリックし、行の挿入をしてみます。

こんなポップアップが表示されますので、対象となるスプレッドシートのURLを入力して、「OK」をクリックしてください。

（https://docs.google.com/spreadsheets/d/XXX/editみたいなURLです。/editのあとに#~~~みたいな表記が続くこともありますが、わざわざ削除せずそのまま貼り付けても正常に動作します。）

次に挿入する行番号を数字で入力してください。例えば、1行目と2行目の間に1行新たに追加したい場合、「1」と入力すればOKです。※何行追加するかは次のポップアップで入力します。

（ちなみに列の場合も数字で入力をお願いします。例えば以下のようにA列の場合は「1」、B列の場合は「2」になります。）

次に挿入する行数を数字で入力してください。

すると、以下のように表示されて、処理が完了します。

指定したスプレッドシートを確認していただければ、各シートに行が追加されているのが分かると思います。

行・列の削除についても同じ流れで行うことができます。

コピペの処理の場合は、まず以下のようにコピー元の範囲を尋ねられます。

範囲の指定は1つのセルでも、範囲でも問題ないです。（ただし貼り付け先の範囲と合っていないとエラーになるので気をつけてください。）

ペーストする範囲も同じように入力すると各シートでコピペの処理が行われます。

例えば「A:A」をコピーして、「B:B」に貼り付けする、といった指定をすれば列ごとコピペすることも可能です。

まとめ

前回のコードをほぼ流用しましたので、あまり目新しさはないかもしれませんが、作っていて「こっちも割と需要ありそうだな」と思いました（笑）

なんとなく誰かが作ったスプレッドシートがいつしか業務上で必要不可欠なものになり、あるいはその作業が複数同じように行われるようになって、どんどんスプレッドが肥大化していって修正するときに絶望するパターンは私も経験済みです。

他にもみなさんが絶望したエピソードがあればぜひコメントください。解決できそうなものであれば、頑張ってトライしてみます！（実現の保証は出来ませんが…😅）

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

Google Apps Scriptでスプレッドシートにデータ行を挿入する２つの方法