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

maedaさん

2021年5月16日 21:52

前回、複数のスプレッドシートを一括で扱うための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" },
   ]);
}