スプレッドシートとクラウドデータベースを統合する方法

スプレッドシートは、顧客データの整理と管理に有用です。複雑でない小規模なデータセットを扱うビジネスに適しています。

しかし複雑なデータに多くのユーザーがアクセスするという状況では、スプレッドシートでのデータ管理には限界があります。また、スプレッドシートでは加えられた変更を監視・追跡することも難しく、複数のバージョンが存在してしまうなんてこともしばしば。

クラウドデータベースはデータの取得、管理、整理を行うための環境で、より優れたデータ管理を実現できます。

今回は、一般的な表計算ツールであるMicrosoft ExcelとGoogle SheetsをクラウドデータベースのMariaDB、MySQL、PostgreSQLに統合し、データ管理プロセスを改善する方法をご紹介します。

前提条件

これからご紹介する手順には、以下の環境が必要になります。

• Google Sheets（サンプルデータはこちら）
• CoefficientがインストールされたCoefficient
• DevartがインストールされたMicrosoft ExcelのWorkbook
• pgAdmin4とMySQL Workbench（データベースと対話するためのグラフィカルユーザーインターフェース）

スプレッドシートとデータベース統合の要点

クラウドデータベースは、DBaaS（Database as a Service）として、ハードウェアの購入、設定、保守管理に必要な時間とリソースを削減しながら、データベースのホスティング、デプロイ、管理を実現します。

主要なデータベースには以下のようなものがあります。

• PostgreSQL：信頼性、拡張可能な機能、高いパフォーマンスで知られる堅牢なオープンソースのリレーショナルデータベース。数多くのツールや技術との統合をサポートしており、スケーラブルなアプリケーションの構築を支援。
• MySQL：一般的なオープンソースのリレーショナルデータベースで、SQLおよびNoSQLアプリケーションの構築にスケーラビリティ、柔軟性、信頼性に優れた選択肢。手頃な価格でビジネスクリティカルなアプリケーションを支える高性能で可用性の高いデータベースシステム。
• MariaDB：オープンソースのリレーショナルデータベースで、大小のデータを扱うことができ、信頼性が高くあらゆる企業に適している。MySQLの特徴に加えて（MariaDBはMySQLのフォーク）、よりスケーラブルでクエリが高速であることから、パフォーマンスを重視する企業に理想的。

クラウドデータベースは、自動バックアップ、バージョン管理、ディザスタリカバリなど、数々の機能により、ビジネスをスムーズに運用することができます。他にも以下のような利点が挙げられます。

• スケーラビリティ
• 柔軟性
• ビジネスの俊敏性
• セキュリティ
• 費用の削減

Kinstaのデータベースホスティングを利用すると、PostgreSQL、MySQL、MariaDBのインスタンスを数分でセットアップ可能です。Kinstaでは、クラウド上のデータベースシステムへのアクセスをサポートしています。

スプレッドシートデータの整理

スプレッドシートのデータには、エラーの原因となる重複値や異常値、不要な情報などが含まれている可能性があります。ひいては、データの品質やデータベースとの接続に影響を与えます。

1. データの準備

データベースとの統合を行う際には、まずスプレッドシートデータの整理を行うことが大切です。

• テンプレートを使用する─Google SheetsとExcelには、データの書式設定と整理を迅速に行うためのテンプレートが多数用意されています。用途に応じたテンプレートを探すのには手間がかかりますが、長期的なデータ管理を考慮すれば、時間を割く価値があります。
• データをフォーマットする─1つの複雑なシートを複数のシートに分割したり、可読性を考慮して列をアルファベット順または数値順で並べ替えたり、重要度別に色付けを行ったりすることで、データをよりわかりやすく視覚化することができます。
• データクリーニング─外れ値、重複値、特殊文字を削除したり、1つの列を複数の列に分割して統合時の解析エラーを回避したり、条件付き書式を使用して誤ったデータを特定したりすることも大切です。
• 不要なデータを非表示にする─ExcelやGoogle Sheetsでは、現時点では必要なくても後で役に立つ可能性のあるデータがある場合、非表示にすることができます。

2. データの構造化

また、以下のベストプラクティスも参考にしてみてください。

• メタデータを記録する─メタデータは、現在のデータ構造とその出所に関する重要な情報を提供します。メタデータを記録することで、すべてのデータポイントを正確にマッピングすることができます。
• nullと0を表現する─ゼロとnullは異なる数値です。データベースが0をnullとして解釈すると、制約エラーを引き起こす可能性があるため、データベースとの統合用データシートでは、ゼロ値を正確に記録してください。
• フィールド名に特殊文字を使用しない─列名に数字、特殊文字、その他のUnicode文字を使用すると、スプレッドシートからデータをインポートする際に解析エラーが生じる可能性も。フィールド名を付ける際は、キャメルケース（studentNameのように各構成語の先頭を大文字にする）やアンダースコア（_）を使用して、わかりやすく整えることが重要です。

データを適切に構造化したら、クラウドデータベースと連携していきます。

MariaDBとスプレッドシートを統合する方法

まずは、KinstaでMariaDBデータベースを追加します。今回のスプレッドシートデータのインポートには、ノーコードの自動同期ツール「Coefficient」を使用して、データベースインスタンスをGoogle Sheetsに統合します。以下の手順に移る前に、Coefficientをインストールしてください。

MySQL WorkbenchをMariaDBに連携する

まずは、MariaDBデータベースインスタンスに外部接続の情報を追加します。

1. MyKinstaの「外部接続」セクション（「データベース」＞（データベース名）＞「概要」）の「ホスト名」「ユーザー名」「パスワード」「データベース名」をコピーする。

   

   ここでMariaDBインスタンスと対話するため、グラフィカルユーザーインタフェースを搭載したMySQL Workbenchを起動します。新規接続を追加して、データベースインスタンスをMySQL Workbenchに接続します。

2. 「Welcome to MySQL Workbench」ページで左下の「MySQL Connection」をクリック。
3. 「Setup New Connection（新規接続をセットアップ）」ページでMariaDBデータベースインスタンスが提供する外部接続の情報を入力する。

   

4. ページ下部の「Test Connection」をクリックする。互換性がないまたは非標準のサーバーバージョンに関する接続の警告が表示されますが、無視して問題ありません。これでデータベースインスタンスがMySQL Workbenchに接続されます。
5. 以下のSQL文を使用してテーブル名を「diabetes_table」とし、列を持つテーブルを作成する。

   CREATE TABLE `diabetes_table` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `Pregnancies` varchar(45) NOT NULL,
     `Glucose` int(11) NOT NULL,
     `BloodPressure` int(11) NOT NULL,
     `BMI` decimal(3,1) NOT NULL,
     `DiabetesPedigreeFunction` decimal(4,3) NOT NULL,
     `Age` int(11) NOT NULL,
     `Outcome` tinyint(4) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `id_UNIQUE` (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3

Google SheetsをMariaDBに連携する

1. Google Sheetsを開く。 サンプルのスプレッドシートには、7つの列を持つカンマ区切り値（CSV）ファイル（diabetes.csv）が含まれています。

   

2. 「拡張機能」をクリックする。

   

3. 「Coefficient Salesforce, Hubspot Data Connector」＞「Launch」を開く。

   

   これにより、シート右側にCoefficientが表示され、スプレッドシートとMariaDBデータベース間でデータのインポートおよびエクスポートができるようになります。

4. 「Coefficient」の「Export to」をクリックして、「MySQL」を選択（MariaDBがMySQLのフォークであるため、MySQLを選択）。
5. MariaDBインスタンスの接続情報を入力して、「Connect」をクリック。

   

6. 「Source Data」パネルで、「Tab」から「diabetes」（サンプルのスプレッドシート）を選択し、「Header row」から「Row 1」を選択。

   

7. 「Destination」パネルで「Table」から「Sheets-db diabetes_table」を選択。
8. 「Action」から「Insert」を選択して、スプレッドシートのデータを挿入する。

   

   これで「Schemas」パネルに、スプレッドシートの列が表示されます。

   

9. スプレッドシートの列をMariaDBテーブルの見出しにマップして、「Save」をクリック。

   

10. 「Specific rows on sheet（シート上の特定の行）」を選択して「Next」をクリック。
11. 12行目を選択してマッピングをテストし、「Done selecting rows（行の選択を完了）」をクリックする。

    

12. スプレッドシートに「Record ID」「Result」（OKを示す）および「Timestamp」（エクスポートの日時を示す）の列ができる。

    

13. 「Done」をクリックする。
14. 次にエクスポートする行を選択。「Insert X rows in MySQL（MySQLにX行を挿入）」を選択して「Done」をクリック。
15. 以下のクエリを使用して、インポートしたデータをMariaDBテーブルに表示する。

    SELECT * FROM <your_db_name>.diabetes_table;

    

ExcelのシートをMariaDBに連携する

Excelを使用する場合は、Devartをインストールします。このプラグインを使用すると、ExcelシートをMariaDBに連携し、Excel上でデータをインポートおよび編集して、変更をデータベースに反映することができます。また、インストール手順を示すドキュメントも付属しているため安心です。

1. 空のExcelシートを開く。
2. 上部のナビゲーションバーで「Devart」をクリック（Devartをインストールしていると表示される）。

   

3. 「Get Data」をクリックし、「Import Data Wizard（データのインポートウィザード）」を開く。

   

4. 「MySQL database as the Data Source（データソースとしてのMySQLデータベース）」を選択し、MariaDBデータベースの情報を入力して接続する。
5. 「Test Connection」をクリックすると、接続に成功したことを伝えるメッセージが表示される。
6. 「OK」＞「Next」をクリック。
7. Visual Query Builderまたは独自のSQLクエリを使用して、「diabetes」テーブルからExcelシートにすべてのデータをインポートする。

   

8. 「Finish」をクリックする。これで、クラウドデータベースのデータがExcelシートに取り込まれました。

   

9. このシートとデータベースを編集して更新するには、「Edit Mode」を選択。

   

   接続の設定でパスワードを保存しないことを選択した場合、データベースのパスワードの入力を促すプロンプトが表示されます。

10. 接続を再テストし、パスワード入力後も接続されていることを確認。
11. データベースに追加する新しいレコードを2つ選択する。

    

12. 「Commit」を選択し「OK」をクリックして変更を適用し、MariaDBデータベースに変更を反映する。
13. クエリを実行して、更新されたデータベースを確認する。これで、新たに2つのレコードが表示されます。

    

PostgreSQLとスプレッドシートを統合する方法

Google SheetsとPostgreSQLデータベースを統合してデータをインポートするには、データのインポートをシームレスに行うため、信頼できる接続を確立する必要があります。

KinstaにPostgreSQLデータベースを追加し、接続情報を使ってグラフィカルユーザーインターフェイス（GUI）であるpgAdmin4に接続します。

前のセクション同様、Coefficientを使用して、データベースインスタンスをGoogle Sheetsに連携します。

Google SheetsとExcelのデータをPostgreSQLに連携してインポートする

1. 「Register – Server」ダイアログでPostgreSQL接続のための以下の情報を入力。
   • Host name/address
   • Port
   • Maintenance database
   • Username
   • Password

   

2. 以下のSQL文を使用して、テーブルID値のシーケンスを作成する。

   CREATE SEQUENCE IF NOT EXISTS public.diabetes_table_id_seq
       INCREMENT 1
       START 1
       MINVALUE 1
       MAXVALUE 2147483647
       CACHE 1
       OWNED BY diabetes_table.id;

3. 「diabetes_table」という名前で、データ型と制約がスプレッドシートのテーブルと一致する列を持つPostgreSQLテーブルを作成する。

   CREATE TABLE IF NOT EXISTS public.diabetes_table
   (
       "Pregnancies" smallint NOT NULL,
       "BloodPressure" smallint NOT NULL,
       "BMI" numeric(3,1) NOT NULL,
       "Glucose" smallint NOT NULL,
       "DiabetesPedigree" numeric(4,3) NOT NULL,
       "Age" smallint NOT NULL,
       "Outcome" boolean,
       id integer NOT NULL DEFAULT nextval('diabetes_table_id_seq'::regclass),
       CONSTRAINT diabetes_table_pkey PRIMARY KEY (id)
   )
   WITH (
       OIDS = FALSE
   )
   TABLESPACE pg_default;

4. Google Sheetsでdiabetes.csvを開く。
5. 「拡張機能」をクリックして、「Coefficient: Salesforce, Hubspot Data Connector」＞「Launch」を選択する。
6. 「Export to」をクリックして、スプレッドシートのデータをPostgreSQLデータベースにエクスポートする。
7. 「PostgreSQL」の横にある「Connect」をクリック。
8. PostgreSQL接続情報を入力して、「Connect」をクリックする。

   

9. 「Tab」から「diabetes」を選択し、「Header row」から「Row 1」を選択して、データのエクスポート方法を定義する。

   

10. 「Destination」パネルの「Table」から「public.diabetes_table」を選択。
11. 「Action」から「Insert」を選択。

    

12. シートの列をPostgreSQLテーブルにマップする。
13. 2行目を選択して、「Done selecting rows」をクリックする。
14. スプレッドシートに「Record ID」「Result」（OKを示す）および「Timestamp」（エクスポートの日時を示す）の列ができる。
15. さらに行をエクスポートして、投稿のテストを行う。
16. クエリを実行して、最近インポートしたデータを表示する。

    SELECT * FROM diabetes_table;

    このクエリが、diabetesテーブルの全データを取得します。

PostgresSQLのデータをExcelシートに連携してエクスポートする

まずは、PostgreSQLの接続情報が必要になります。

1. 空のExcelシートを開き、「Devart」をクリック。
2. 「Get Data」をクリックして、「Import Data Wizard」を開く。
3. データソースの一覧から「PostgreSQL database」を選択して、「Import Data Wizard」でデータベースへの接続情報を入力する。

   

4. 「Test Connection」をクリックして、接続が成功したかどうかを確認する。
5. オブジェクトを選択し、ビジュアルクエリを使用してデータベースにクエリを実行。Visual Query Builderを使用するか、独自のカスタムSQLクエリを作成してデータベースにクエリを実行。

   

6. 「Finish」をクリック。これで、データが取り込まれたExcelシートが作れました。「Refresh」をクリックして、ワークシートを最新の状態にします。

   

7. 「Yes」をクリックする。
8. このシートとデータベースを編集・更新するには、「Edit Mode」をクリックする。
9. スプレッドシートに新しいレコードを追加し、「Commit」をクリックして変更を反映。

   

10. クエリを実行して更新されたデータベースを確認。データベースに新たなレコードが追加されているはずです。

まとめ

クラウドデータベースを利用することで、データの保存、アクセス、確立、および動的な関係の効率的な管理が可能になり、共同作業も捗ります。

Kinstaでは、PostgreSQLおよびMySQLデータベースを簡単に構築・稼働し、外部接続を使用してすぐにスプレッドシートを統合することができます。これにより、データベーステーブルを作成し、スプレッドシートのフィールドをクラウドデータベースにマッピングして、データのエクスポートを行うことが可能です。

Kinstaのマネージドデータベースホスティングで、クラウドデータベースの優れた管理機能を活用してみてください。

スプレッドシートで膨大な量のデータを管理されていませんか？また、データを効率的に管理するヒントをご存知でしたら、以下のコメント欄でぜひお知らせください。

Jeremy Holcombe Kinsta

Kinstaのコンテンツ＆マーケティングエディター、WordPress開発者、コンテンツライター。WordPress以外の趣味は、ビーチでのんびりすること、ゴルフ、映画。高身長が特徴。

• LinkedIn