WEB

GASで別スプレッドシートの値のある全範囲をコピー&ペーストする方法

投稿日:2018年4月19日 更新日:

Google Apps Script(GAS)で「AのGoogleスプレッドシートから、Bのスプレッドシートに、シートを開いたタイミングで内容を全部コピーして上書きしたい」と思いました。

しかし、ドンピシャのコードが検索してもみつかりませんでした。

よこちゃん
ということで、見つからなかったので、他サイトを参考にしながら書いてみました。

職業プログラマーではないので、適当なコードですがとりあえず動いているので、参考までに公開してみたいと思います。

GASの別のスプレッドシートコピーでやりたかったこと

スプレッドシートA(自分のシート)を開いたタイミングで、スプレッドシートBから内容をコピーして、スプレッドシートAに全内容を上書き。

AとBのスプレッドシートを、全く同じ状態にする。

参考にしたサイト

参考[GAS][スプレッドシート]別のスプレッドシートにデータをコピーするには: 逆引きGoogle Apps Script
上記サイトのコードをベースにして書きました。

GASで、スプレッドシート起動時に別スプレッドシートの値のある全範囲をコピー&ペーストするコード

コードを下記に置いておきます。

function onOpen() {
  var ss_copyFrom = SpreadsheetApp.openById('ここにスプレッドシートキーを入力'); //コピー元のマスターデータのあるスプレッドシート
  var ss_copyTo = SpreadsheetApp.getActiveSpreadsheet();

  var sheet_copyFrom = ss_copyFrom.getSheetByName('元データのシート名'); //コピー元のスプレッドシートの値を抜き出したいシート名
  var sheet_copyTo = ss_copyTo.getSheetByName('自分のシート名'); //ペーストする自分のスプレッドシートのシート名

  sheet_copyTo.clear();// コピー元の行や列が減っていることを考慮して、一旦コピペ先のシートをクリア

  var lastRow = ss_copyFrom.getLastRow(); //最終行を取得
  var lastColumn = ss_copyFrom.getLastColumn(); //最終列を取得

  var copyValue = sheet_copyFrom.getRange(1,1,lastRow,lastColumn).getValues(); //コピー元のシートから値の入っている全範囲をコピー
  sheet_copyTo.getRange(1,1,lastRow,lastColumn).setValues(copyValue); //自分のシートにコピーした値を全範囲をペースト
}

上記コードは、自分のスプレッドシート(ペースト先のスプレッドシート)の方に書きました。

基本は、コメントアウトしてある説明のとおりです。

  1. 2行目の「ここにスプレッドシートキーを入力」
  2. 5行目の「元データのシート名」
  3. 6行目の「自分のシート名」

あとは、上記3点を各自の仕様に合わせて書き直せばOKかと思います。

あと、自分はスプレッドシート開いたタイミングで実行されて欲しかったので、function onOpen()を使いました。

GASで、シート上のボタンクリック時に別スプレッドシートの値のある全範囲をコピー&ペーストするコード

onOpen()がうまく動いていない感じがありました。

そこで、スプレッドシート上に配置したボタンを押したらコードが起動してコピペされるように書き換えました。

function getList() {
  var ss_copyFrom = SpreadsheetApp.openById('ここにスプレッドシートキーを入力'); //コピー元のマスターデータのあるスプレッドシート
  var ss_copyTo = SpreadsheetApp.getActiveSpreadsheet();

  var sheet_copyFrom = ss_copyFrom.getSheetByName('元データのシート名'); //コピー元のスプレッドシートの値を抜き出したいシート名
  var sheet_copyTo = ss_copyTo.getSheetByName('自分のシート名'); //ペーストする自分のスプレッドシートのシート名

  sheet_copyTo.clear();// コピー元の行や列が減っていることを考慮して、一旦コピペ先のシートをクリア

  var lastRow = ss_copyFrom.getLastRow(); //最終行を取得
  var lastColumn = ss_copyFrom.getLastColumn(); //最終列を取得

  var copyValue = sheet_copyFrom.getRange(1,1,lastRow,lastColumn).getValues(); //コピー元のシートから値の入っている全範囲をコピー
  sheet_copyTo.getRange(1,1,lastRow,lastColumn).setValues(copyValue); //自分のシートにコピーした値を全範囲をペースト
}

とはいっても、元々のGASのコードはほぼ同じ。

関数名をonOpen()からgetList()に変更しただけです。

あとは、このコードを起動するボタンをスプレッドシート上に作ります。


挿入>図形描写を選択。


なんでもよいですが、適当に四角でも書きます。


このようなボタンが出来上がります。右クリックしてから、右上部分をクリックします。


スクリプト割り当てができますので、こちらをクリック。


先ほど書いた関数であるgetListを入力します。

これで完成です。

あとは、スプレッドシート上でボタンを左クリックすると、getListの関数が起動し、コピペが実行されます。

最後に

スプレッドシートのimportrange関数では、元データの編集権限がないと実行できませんが、こちらの方法ならコピペなので実行できます。

起動時、毎回「sheet_copyTo.clear();」で全消しリフレッシュしてからコピペされるので、マスターデータの更新情報がその都度入ります。

つまりこれにより、AとBのスプレッドーシートの内容が全く同じになります。
(上書きだけだと、コピペ元のシートの内容が"少なくなっている"ような場合、バグる)

ひたすら転記する作業に疲れた人とかも、使えるかもしれません。


-WEB

Copyright© ヨコログ , 2019 All Rights Reserved.