Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yakuse(けん)

Googleフォーム × スプレッドシート × Power Query で業務効率化

Last updated at Posted at 2025-10-04

〜エクセルへの手作業コピーをなくし、データ上限も回避する仕組み〜

1. はじめに

例えば、GoogleフォームやAppSheetで入力されたもの

  • 社内（店舗）からの報告（売上・在庫・クレーム内容）
  • 社外（お客様・取引先）の入力（問い合わせ・アンケート結果）

これらを毎日スプレッドシートからExcelにコピーする時
手作業でExcelにまとめるのは手間がかかり、放置したらすぐデータ量は上限に達してしまいます。

そこで本記事では、以下の仕組みで 完全自動化 を紹介します。

  • Googleフォームで入力
  • スプレッドシートに自動記録
  • Power QueryでExcel（ローカルPC）に自動連係

2. 課題とメリット

よくある課題 メリット
毎日スプレッドシートからExcelへ コピー＆ペーストが必要 Power Queryで自動更新、手動コピー不要
データが溜まると 動作が重くなり、不具合の原因となる Apps Scriptで古いデータを自動削除＋バックアップし、軽量＆長期運用が可能
担当者が不在だと 作業が止まってしまう トリガー機能で自動処理、人が不在でも継続運用
手作業ゆえに コピー漏れや範囲間違いなどのエラー 自動処理でヒューマンエラーを削減

3. 全体像

4. Googleフォームとスプレッドシートの連携

手順

  1. Googleフォームを作成
  2. 回答先を「スプレッドシート」に設定
  3. 入力されると、自動的にスプレッドシートに反映

5. Excel（Power Query）とスプレッドシート連携

手順

  1. スプレッドシートの共有設定を「リンクを知っている全員（閲覧者）」に変更
  2. 共有URLを export形式に変換
    （edit#gid=... → export?format=xlsx&id=...&gid=...）
  3. Excel → [データ] → [データの取得] → [その他のデータソース] → [Webから] を選択
  4. 変換後URLを入力して設定を完了する

※ 詳細な画面手順については、自分は以下のページを参考にしました：
GoogleスプレッドシートとPower Queryの連携手順

ポイント

  • スプレッドシート共有権限を「編集」にすると、シートの内容を変えられるので注意
  • 「参照」権限であっても、URLを知っている人は内容を見られるため、機密データや個人情報を含むデータには不向き

6. データ上限と対策

Googleフォームとスプレッドシートは、いずれも内部に回答データを保持しています。

  • Googleフォーム：1フォームにつき最大 約500万件の回答
  • Googleスプレッドシート：1シートにつき 1000万セルまで

このため、長期運用を続けると どちらも上限に到達 する可能性があります。
放置すると運用に支障が出るリスクがあるため、両方を対象としたメンテナンスが必要 です。


そこで次に、古いデータを削除しつつバックアップを残す仕組みを導入し、 安心して長期運用できる方法を記載します。

7. GoogleFormデータの自動削除

Google Apps Scriptを使えば、古いデータを自動削除できます。

基本手順

  1. Googleフォームを開き、メニューから [拡張機能] → [Apps Script] を選択
  2. 以下のスクリプトを貼り付けて保存
  3. トリガーで定期実行を設定する
    ※ 初めてGASを使う場合、承認ダイアログなどの設定が必要になります。
    　この記事では詳細な操作は割愛しているため、詳細は必要に応じて検索してください。

コード例：フォームの解答を削除

function resetFormResponses() {
  var form = FormApp.getActiveForm(); // バインドされているフォームを取得
  form.deleteAllResponses();          // すべての解答を削除
}

8. スプレッドシートのデータ削除

スプレッドシートもフォームと同様に、古いデータを削除する必要があります。
ただし、Google側でバックアップとして一定期間データを保持する仕組みも追加しておくと安心です。

基本手順

  1. スプレッドシートを開き、[拡張機能] → [Apps Script] を選択
  2. 以下のスクリプトを貼り付けて保存
  3. トリガーで定期実行を設定する

コード例（GAS）

  • 「フォームデータ」：前日以前の行を バックアップへコピーしてから削除
  • 「バックアップ」：8日前以前の行を削除（保持期間を過ぎた履歴を整理）
  • A列にタイムスタンプ（日時）が入っている想定、1行目はヘッダー
function archiveAndClean() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const src = ss.getSheetByName('フォームの回答 1');  // 本体
  if (!src) return;

  // バックアップシート（なければ作成）
  const backup = ss.getSheetByName('バックアップ') || ss.insertSheet('バックアップ');

  const lastRow = src.getLastRow();
  const lastCol = src.getLastColumn();
  if (lastRow < 2) return; // データなし（ヘッダーのみ）

  // ---- 日付しきい値（スクリプトのタイムゾーンで0:00）----
  const startOfToday = new Date();
  startOfToday.setHours(0, 0, 0, 0);

  const startOfYesterday = new Date(startOfToday);
  startOfYesterday.setDate(startOfYesterday.getDate() - 1);

  const startOf3DaysAgo = new Date(startOfToday);
  startOf3DaysAgo.setDate(startOf3DaysAgo.getDate() - 3);

  // ---- 全データ取得（2行目～）----
  const values = src.getRange(2, 1, lastRow - 1, lastCol).getValues(); // A列=タイムスタンプ(Date)
  const tsCol = values.map(r => [r[0]]); // タイムスタンプ列（昇順が前提）

  // ---- 1) 前日分を抽出してバックアップへ追記 ----
  const ydayRows = [];
  for (let i = 0; i < values.length; i++) {
    const ts = values[i][0];
    if (ts && ts >= startOfYesterday && ts < startOfToday) ydayRows.push(values[i]);
  }
  if (ydayRows.length) {
    // ヘッダーが無ければ本体のヘッダーをコピー
    if (backup.getLastRow() === 0) {
      const header = src.getRange(1, 1, 1, lastCol).getValues();
      backup.getRange(1, 1, 1, lastCol).setValues(header);
    }
    backup.getRange(backup.getLastRow() + 1, 1, ydayRows.length, lastCol).setValues(ydayRows);
  }

  // ---- 2) 本体：3日前の0:00より古い行を連続削除（1回だけ）----
  // lowerBound: 初めて threshold 以上となる位置(0-based, tsCol用)。空セルは「残す」扱い（= threshold 以上）にする。
  function lowerBoundDate(arr, threshold) {
    let lo = 0, hi = arr.length;
    while (lo < hi) {
      const mid = (lo + hi) >>> 1;
      const v = arr[mid][0];
      if (!v || v >= threshold) hi = mid; else lo = mid + 1;
    }
    return lo;
  }
  const cutIdx = lowerBoundDate(tsCol, startOf3DaysAgo);
  if (cutIdx > 0) {
    // 2行目から cutIdx 行分が「3日前より古い」ので一括削除
    src.deleteRows(2, cutIdx);
  }

  // ---- 3) バックアップ：8日前以前を連続削除（1回だけ）----
  const bLast = backup.getLastRow();
  if (bLast >= 2) {
    const bTs = backup.getRange(2, 1, bLast - 1, 1).getValues();
    const threshold8 = new Date();
    threshold8.setHours(0, 0, 0, 0);
    threshold8.setDate(threshold8.getDate() - 8);

    const cutIdxB = lowerBoundDate(bTs, threshold8);
    if (cutIdxB > 0) {
      backup.deleteRows(2, cutIdxB);
    }
  }
}

※削除処理が1行ずつで重かったため、一括処理に変更しました（2025年10月7日）

9. エクセルの自動実行

Excel ファイルは、マクロを使ってブックを開いた瞬間に自動更新させることが可能です。これにより、パワークエリのエクセルを開くだけで常にスプレッドシートの最新データを取得できます。

さらにタスクスケジューラを組み合わせれば、定期的に自動更新処理を走らせることも可能です。

エクセルマクロの設定

  1. パワークエリを設定したエクセルブックを「マクロ有効形式（.xlsm）」で保存する
  2. VBAエディタを開き、ThisWorkbook にコードを記載する
  3. タスクスケジューラで Excel ファイルを定期的に起動させる

VBAコード例

コード例

Private Sub Workbook_Open()
    ' ブックを開いた時に全クエリを更新
    Me.RefreshAll
    
    ' 非同期クエリがある場合は完了を待つ
    DoEvents
    Application.CalculateUntilAsyncQueriesDone
End Sub

※VBAの操作方法やタスクスケジューラの設定方法については、必要に応じて検索して確認してください。

10. 運用のポイント

  • 権限管理：フォームやスプレッドシートに適切な公開範囲を設定
  • バックアップ：データ連携エラー時のリカバリの為、要所でバックアップのしくみ
  • 定期チェック：スプレッドシートの容量や実行エラーの監視のしくみ（記事外）

11. まとめ

  • Googleフォーム → スプレッドシート → Power Query → Excel で自動連係
  • 手作業のコピー作業不要で、最新データをローカルPCで活用可能
  • Apps Scriptで上限回避とバックアップを両立
  • 店舗報告、顧客アンケート、日報など幅広く応用できる

🚀 これで「毎日コピー作業に追われる運用」から卒業できます！

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?