【5分でできる】GASでスプレッドシートのデータを自動転記する方法

なおき | AI×時短術

2024年5月1日 10:05

「このスプレッドシートのデータ、毎回コピペすんのめんどくさいな」
「データの転記って自動でできないの？」
「複数のデータを統合して分析したいけど自動化したい！！」

本記事ではこのような方を対象に、GAS(Google App Script)を使ってスプレッドシートのデータを自動で転記する方法をご紹介します。

■本記事で分かること
GASを使ってスプレッドシートのデータを自動で転記する方法

■本記事の信頼性
私は、本業でChatGPTなどのAIツールを活用して効率化を実現し、ほぼ毎日定時で帰宅。家族との充実した時間を確保している「なおき」といいます。

AIと全く無縁の体育系で文系出身（数学Ⅱで終わりました）の私でもできる時短術をわかりやすく説明できるよう、Xなどを活用して情報発信しています。

また、note開始３か月で総閲覧回数は約5000回、約5ケタ/月に至る記事の購入をいただいております！（何も広告とかうってないですが、案外売れて驚いております(;'∀'))

これからも皆様のお役に立てる記事を配信していくのでよろしくお願いします！

では、いつものようにこの記事を読んでくださった方々が１分でも早く帰れることを願い、今回はGASを使ってスプレッドシートのデータを自動で転記する方法をご紹介します！

1.Googleスプレッドシートの準備

まずは、Googleスプレッドシートで転記したいデータを作成します。

今回は、名前、年齢、アドレス、データ記入日を並べてみました。

また、データに空白行があった場合にはエラーを返せるように、3行目を空白にしてみます。

次に、転記先のスプレッドシートを準備します。

とりあえず今回は何も入っていないスプレッドシートを準備して、次に進んでください。

2.GAS(Google App Script）の設定と実行

それでは次にGASの設定に入っていきます。

ステップ１：Apps Scriptを開く

転記用のスプレッドシートデータのメニューバーから
「拡張機能」→「Apps Script」を選択します。

ステップ２：コードの入力

Apps Scriptが開いたら、デフォルトで入力されている部分のコードを削除してください。

同じところに以下のコードをコピペします。

function transferData() {
  try {
    // ソーススプレッドシートのIDとシート名
    var sourceSpreadsheetId = 'ここにソーススプレッドシートのIDを入れる';
    var sourceSheetName = 'シート1';

    // ターゲットスプレッドシートのIDとシート名
    var targetSpreadsheetId = 'ここにターゲットスプレッドシートのIDを入れる';
    var targetSheetName = 'シート1';

    // スプレッドシートのオブジェクトを取得
    var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
    var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
    var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
    var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);

    // ソースシートからデータの取得
    var data = sourceSheet.getDataRange().getValues();

    // データ検証：空の行が含まれていないかチェック
    if (data.some(row => row.every(cell => cell === ""))) {
      throw new Error("データに空の行が含まれています。");
    }

    // 日付データをローカルタイムゾーンで処理
    var formattedData = data.map(function(row) {
      return row.map(function(cell) {
        if (cell instanceof Date) {
          return Utilities.formatDate(cell, Session.getScriptTimeZone(), 'yyyy/MM/dd');
        }
        return cell;
      });
    });

    // バッチサイズでデータを転記
    var batchSize = 100; // 一度に転記する行数
    for (var i = 0; i < formattedData.length; i += batchSize) {
      var batchData = formattedData.slice(i, i + batchSize);
      targetSheet.getRange(i + 1, 1, batchData.length, batchData[0].length).setValues(batchData);
    }

  } catch (e) {
    // エラー処理：エラーが発生した場合の処理
    Logger.log('エラーが発生しました: ' + e.toString());
    SpreadsheetApp.getUi().alert('エラーが発生しました: ' + e.toString());
  }
}

このコードは、１つのGoogleスプレッドシート（ソース）から別のスプレッドシート（ターゲット）へデータを自動的に転記するためのコードです。

ちなみに、以下の機能を追加してます。

①データ検証
取得したデータに空の行がないかどうかをチェックします。空の行がある場合はエラーを発生させ、処理を中断します。

②日付データのフォーマット調整
転記した際に起こりうるタイムゾーンによる日付のずれを防ぎます。

③バッチ処理によるデータ転記
データを100行ごとの小分け（バッチ）にして、ターゲットシートに書き込みます。これにより、大量のデータを扱う際のタイムアウトエラーを回避できます。

④エラーハンドリング:
スクリプトの実行中にエラーが発生した場合、その内容をログに記録し、ユーザーにアラートを表示します。

次に、実行する前に以下の「IDを入れる」部分をご自身のデータと同じになるように書き換えて下さい👇

    // ソーススプレッドシートのIDとシート名
    var sourceSpreadsheetId = 'ここにソーススプレッドシートのIDを入れる';
    var sourceSheetName = 'シート1';

    // ターゲットスプレッドシートのIDとシート名
    var targetSpreadsheetId = 'ここにターゲットスプレッドシートのIDを入れる';
    var targetSheetName = 'シート1';

スプレッドシートのIDは以下のように各URLに含まれています。

https://docs.google.com/spreadsheets/d/この部分/edit#gid=0

実際にコピペしたコードは以下のようになります。
修正後は必ず保存マーク💾を押してください。

ステップ３：実行及び権限の承認

保存出来たら、「▷実行」の部分をクリックして関数transferDataを実行します。

実行すると権限の承認を求められるので、以下の画像の赤枠部分をクリックしていってください。

すると、Apps Scriptの実行ログが出てきます👇

データに空白の行が含まれているため、エラーが返ってきました。

では、転記用のスプレッドシートから空白の行を削除しましょう。

もう一度、Apps Scriptの画面で実行をクリックすると、今度は成功するはずです。

転記先のスプレッドシートを見てみると…

無事、データが転記されていますね！

でも、これだけだと毎回実行ボタンを押さないといけないのでめんどくさいですよね。

このため、データが編集される度に自動で転記されるようにトリガーを設定してみましょう！

まず、Apps Scriptの左側のメニューから⏱マークを選択して、トリガー画面に移動します。

次に、画面右下の「トリガーを追加」をクリックしてください。

出てきた画面で以下のように設定して保存します。

イベントソース：スプレッドシートから
イベントの種類を選択：編集時

初回は権限の承認が求められるので、以下の手順で承認してください。

承認されると、トリガー画面が以下のようになります。

以上でトリガー設定完了です！

では、実際に転記したいデータを編集してみましょう！
転記用のスプレッドシートデータに「Daveさん」を追加してみると…

転記先のスプレッドシートが自動で更新されました👇👇

以上で作業は終了です！
お疲れ様でした！

4.まとめ

今回は、「GASを使ってスプレッドシートのデータを自動で転記する方法」をテーマに紹介しましたが、いかがだったでしょうか？

Googleスプレッドシートで転記用のシートを準備
GAS(Google App Script）コードとトリガーの設定
GASの実行

上記の内容が実行できれば、スプレッドシートのデータを自動で転記できるようになります！

多くのスプレッドシートを１つにまとめて管理したい時、１つ１つコピペするのはかなり手間ですが、この方法をマスターすれば誰かが編集したファイル内容も自動で反映することができます！

チームで仕事をする際にかなり役に立つのではないでしょうか？

今回紹介した内容で皆さんの業務が少しでも効率化できれば、スキ、SNSでの紹介をしていただけると大変励みになりますので、何卒よろしくお願いします！

このほか、AI×時短術の知識をX（旧Twitter）で紹介していますので、是非フォローしてください！

Tweets by Naoki_GPT

この記事が気に入ったらサポートをしてみませんか？