Copyright © Acrovision All Rights Reserved. >>>データベースのサンプルを見てみる, 通常のエクセル関数とは違い、SQLのようなQuery関数も簡単に使うことができます。Query関数は以下のような式を記述すると利用することができます。, 以下のように山田家と佐藤家のデータベースがスプレッドシート内に格納されているとします。, まずselectでG〜Jまでの列をしていします。さらに「山田」さんで検索するためには[where 列 like キーワード%]という書き方が必要です。H列のlikeで「山田」を指定します。queryの書き方は以下になります。, さらに「男性」という条件をつけたいとします。I列の条件を「男性」とします。また先ほどの「山田」を姓に含むは継続して条件検索したいので、間に”and”を加えてください。, 最後に「20歳以下」という条件を加えます。エクセルでもIF関数などででてくる「<=（以下）」という演算子を利用して下さい。queryの記載方法は下記のようになります。, クライアントの業務を徹底的にITで効率化する。プログラムをガシガシ書いてます。集客、マーケティング、営業を支援するWEBアプリも作ってます。Python/GoogleAppsScript/Javascript, =QUERY($G$2:$J$7,”select G,H,I,J where H like ‘山田%'”), =QUERY($G$2:$J$7,”select G,H,I,J where H like ‘山田%’ and I=’男性'”), =QUERY($G$2:$J$7,”select G,H,I,J where H like ‘山田%’ and I=’男性’ and J<=20″), >>>参照：Googleスプレッドシートと連携！Googleフォームを使ってみた。, セブンイレブンの住所をスクレイピング、GoogleMapAPIで緯度経度に変換して地図プロット. Google Apps Scriptを使うようになってから、クライアントサイドでAccessを使う機会は大分減りましたが、それでもAccess需要がゼロになるという事はありません。まだまだクラウドのデータベースサービスは高価であり、おいそれとクエリを投げようものなら翌月の請求書金額は怖いものになります。また、複雑な計算やクエリを組んでとなると、Accessのほうがまだまだ作りやすく、何よりもレポート機能が強力で価格が安いので、手放せません。, そんなAccessですが、Googleスプレッドシートと連携出来たらより一層利便性が向上し、既存のGoogle Apps Scriptのプログラムと連携出来たらいいなと思い、色々調査してみました。Google Apps Script APIが使えそうなので、これを使ってみて実装をしてみました。. msmaflink({"n":"Microsoft Access 2019(最新 永続版)|カード版|Windows10|PC2台","b":"マイクロソフト","t":"","d":"https://m.media-amazon.com","c_p":"/images/I","p":["/31IgHD803XL.jpg","/518upkmJNNL.jpg"],"u":{"u":"https://www.amazon.co.jp/dp/B07K2XFBZH","t":"amazon","r_v":""},"aid":{"amazon":"1879761","rakuten":"678083","yahoo":"1879762"},"eid":"6B7xl","s":"s"}); ※今回は、スプレッドシートのデータの読み書きですが、Google Apps Script APIを使ってるのでGAS側でコードを書き足せばメールの送信やカレンダーの登録、ドライブの操作、その他GASで可能な事が全てAccess側から実行可能になります。, ※今回使用するAccessデータベースは、32bit版と64bit版の2つが入っています。, 今回のスクリプトは以下の２つを実装する必要性があります。また、今回はGoogle Apps Script APIを用いてデータの入出力を行いますので、事前にGoogle Cloud ConsoleにてクライアントIDを作っておく必要があります。, 今回の発表直前の2019年4月8日より、Google Apps ScriptからCloud Platform Projectへ直接アクセスが出来なくなりました。これまでにデプロイしてるものについては、これまで通り「リソース」⇒「Google Cloud Platform API ダッシュボード」からアクセスが可能です。, 今回の変更はスプレッドシート上で動かすスクリプトやGoogleの拡張サービスを利用しないタイプのスクリプトであれば特に問題はありませんが、「Apps Script API」や「Google Picker API」、「Cloud SQL接続」などGCP上のAPIを利用する場合には以下の手順を踏んで、Google Apps Scriptにプロジェクトを連結する必要があります。これまでは、自動的にGCP上にGoogle Apps Script用のプロジェクトが生成されていたのですが、今後は自分の組織（もしくはGCPプロジェクト）上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。, 今回のこの変更だと１つ作ったプロジェクトに集約する必要があるので、クォータについてプロジェクト毎のカウントだったので問題なかったものが、集約されることで、クォータに引っ掛かる可能性があります。, ここでは、クライアントIDとクライアントシークレットを取得します。また、Google Apps Script APIを有効にします。以下の手順で取得しましょう。今回使用するスプレッドシートを開いて作業を行います。, これで必要な情報の半分が手に入りました。この２つは大切なものなので、漏れたりしないように保存しておく必要があります。また、事前に一度、どのfunctionでも良いので実行して、承認をしておく必要があります。, ここでは、スクリプトIDとスコープを取得します。同じくスクリプトエディタの画面で行います。以下の手順で手に入れます。, Google Apps Script側のデータの出力用関数は非常にシンプルです。今回は特にフィルタをせずに全データをAccess側へとreturnするので、以下のようなコードになります。但し、取得データはJSON.stringifyで変換して渡しています。, データ受け入れ側は少しだけ複雑です。今回はAccess側からJSON化したレコードデータとレコード件数を引数としてparamに入れていますので、これらを加工して、data2というシートにデータを書き込みします。, データの取得やテーブルデータをPOST通信で送り込む仕組みが必要です。また、今回はデータにフィルタを掛けずに取得して、特定のIDを元に差分だけをマスターテーブルにインサートするようにしていますので、その為のクエリも必要です。今回は、Windows8.1 / Access2013で動作確認をしています。, ※アーリーバインディングする場合には、参照設定よりMicrosoft Script ControlとMicrosoft WinHTTP Serviceをチェックしておく必要性があります。, 今回のテーブル構造はシンプルです。Googleスプレッドシート側に合わせています。また、自分のテーブルデータとGoogleスプレッドシート側のデータの差分は、レコードIDを持って不一致クエリを実施し、インサートします。Googleスプレッドシート側のレコードIDは手動ではなく、なんらかのUIを持って自動的に割り当てするような仕組みにしておくと良いですね（今回は、GAS側のレコードIDは文字列を含めた文字列型のIDにしてあります。例：A001）。, よって、同じフィールドを持ったテーブル２個（１つはマスター、１つはGAS側のデータを受け入れるテンポラリ用）、不一致クエリ１個で作成します。また、Access_TokenとRefresh_Tokenを格納する隠しテーブルも用意しておきましょう。, ※不一致クエリはそのままGAS側のデータ受け入れるテンポラリ用テーブルへの追加クエリに変更しておいて下さい。, 今回はこちらのサイトのコードを改造・修正して使っています。修正ポイントや改造ポイント他注意点等をポイントにまとめてあります。また、今回はJSONコードの取得の為にVBA-JSONを利用していますので、以下の作業が必要です。, Google Apps Script側のdataget関数を叩いてスプレッドシートのデータを以下のような形で取り込みます。, 差分のみを追加するようにしています。今回parameterを使っていませんが、ここに例えば日付でフィルタをGAS側へ渡して、GAS側でそれに基いてフィルタして返して上げるのがもっとも良いやり取りの仕方だと思います。, データの送信は、GAS側へJSON化したレコードデータと、レコード件数の２つをparametersに入れて送ります。JSONデータは手動で組み上げ、データの取得時にも使用したExecuteGASFunction関数に送っています。masterテーブルのデータをそのまま送っていますが、実際にはクエリなどでデータ数を絞ってから送るのがベストです。, 今回のソースコードは、Access 32bit版でなければ動作しません。コードの中で使われている「Microsoft ScriptControl 1.0」が64bit対応していない為です。Microsoft365以降は64bit版が標準でインストールされるようになってきている為、このままでは連携ができません。また、VBS-JSONも64bit環境では不具合が確認されています。, この問題をクリアする為に、64bit対応では以下のようにコードを変更する必要があります。参照設定からScript Controlは外しておきましょう。64bit版ではVBA-JSONも利用しない書き方に変更します。, このエントリーは前項のVBA 64bit対応と同じく、64bit Windows10の場合に於ける「IEを使ったOAuth2認証」をする場合に生じる問題とその解決方法です。問題の箇所はInternet Explorerのobject生成時にあり、以下のようなエラーが出ます。また、この問題はIEの保護モードの有無でも生じる場合がある。以下の作業を行い、32bitのIEを起動するようにしましょう。, このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。.

||c.scripts[c.scripts.length-2];(b[a].q=b[a].q||[]).push(arguments)}; (adsbygoogle = window.adsbygoogle || []).push({}); '--------------------------------------------------, "profile email https://www.googleapis.com/auth/spreadsheets", "https://accounts.google.com/o/oauth2/auth? Jdbc.getConnection(‘jdbc:mysql://yoursqlserver.example.com:3306/database_name‘, {user: ‘username‘, password: ‘password‘}); getConnectionメソッドを利用し、サーバーアドレス、DB名、ユーザID、パスワードと繋げて接続します。メソッドと接続用文字列が異なるので、注意が必要です。, 今回はCloud SQL側に１枚だけテーブルを作ってありますので、これに対して１行新規データを追加、またテーブルの中身を取得してAlertで表示するといったものを作ってみたいと思います。まずは現在テーブルに入ってる情報を抜き出して見たいと思います。, 次にHTMLサービスで作成したフォームに情報を入れて、このDBへデータを１行追加するというのをやってみたいと思います。スプレッドシート上でHTMLサービスで作成したダイアログに入力後、送信を押すとCloud SQLのMySQLへデータがインサートされるという単純な仕組みです。, 次にレコードを更新をする事例です。。今回はIDが1の人間の役職名を変更するというシーンを想定してコードを記述してあります。よって、UPDATE文だけでなくWHERE句も出てきます。, データベースのレコードを同じくWHRER句で条件をつけて削除します。今回はIDが7の専務を消し去りたいと思います。POSITIONで専務としても良いのですが、それだと無関係の専務まで消えてしまうので注意してください。あくまでIDが7の魔王専務です。WHRER句はANDをつけて２つ条件をつけても良いでしょう。, 手元にあるAccessアプリケーションからも接続出来るようにし、Google Apps ScriptではなくAccessをクライアントにして運用ができたら、手持ちのアプリを簡単にクラウド化する事が出来るのではないか？と思い、チャレンジしてみました。, MySQL Migration Toolkitはすでに開発終了済みで古いツールなのですが、AccessデータベースをMySQLのテーブルに変換し送り込むツールです。使用する為には、旧Java SE 5.0が必要なので、そちらも別途インストールが必要。また、インポート時に型や文字コードを自動判定するのですが、失敗するので、適当に進めず文字コードなどはとくにしっかり指定しておきましょう。。, MySQL WorkbenchでGoogle Cloud SQLの中身を見てみたところ、データは空っぽでしたが、無事にテーブルは出来ていて、きちんとテーブル定義も変換して取り込めていました。テーブル定義が非常に面倒なので、これだけでも非常に助かるツールですね。Windows版しかありませんが。, 手持ちのAccess2013よりODBC接続でテーブルをリンクできれば、データはGoogle Cloud SQL、開発はコレまで通りAccessで可能になるのではと、MySQL Connector ODBCドライバをインストールし、設定をしてリンクテーブルでつなげてみました。なお、5.3 DriverはVisual Studio 2013 の Visual C++ 再頒布可能パッケージに依存しているので、それもインストール済みでなければインストールできません。, ※「作成後の設定を施する」にて、接続元IP許可に自分のアクセス元のIPアドレスを登録しておく必要があります。, AccessでODBC接続させてデータの取得には成功しました。これを実運用する場合、企業などのように「プロキシーを利用した固定IP」が接続元ならば、それらのIPを登録すれば問題ありません。必ずユーザはプロキシーを経由するからです。, しかし、そうではない環境の場合、接続元のIPアドレスは固定ではなく、場合によっては特定のアドレスレンジまるごと登録といった雑な許可をする必要があります。そこで用意されているのが、Google Cloud SQL Proxyを利用した接続です。この方式の場合、DBへはグローバルIPアドレスではなく、ポートフォワーディングされたlocalhostで接続が可能になります。クライアント側のIPアドレスが変動しても問題なく、継続して接続が可能になります。接続手順は以下の通り（今回はWindows版で説明します）, ※Linux, OSX, Windowsのそれぞれ32bitと64bit用にプロキシープログラムが用意されています。, ※Cloud SDKを利用した方法もあるのですが、PCにPythonがインストールされている必要があるため、手間が多いので今回はやめておきました。専用のサーバでも用意する場合には良い選択肢かもしれません。, サービスアカウントの作成とキーを手に入れました。続けて、cloud_proxyのプログラムをダウンロードし設定・実行します。キーはこのプログラムの中で利用します。, cloud_sql_proxy.exeおよび秘密鍵のJSONファイルを元にMySQL ODBC 5.3 Unicode Driverにて、DSNレスで接続させてみました。Autoexecマクロで、Accessファイル起動時に実行させれば、自動的にプロキシー起動⇒ODBC接続でリンクテーブル作成が可能になります。ただしこのコードには以下の課題があります。, とりあえず、簡単なコードは以下の通り。MySQL ODBC 5.3 Unicode Driverを利用していますが、DSN設定を利用せずにリンクテーブルを作成しています。, 今回はGoogle Cloud SQLをテーマに外部のデータベース接続についてこれまで記述しました。しかし、Google Apps ScriptのJDBCサービスでは他にもMicrosoft SQL Server、Oracleに対応しています。これらは前項で紹介したgetConnectionにて接続用URLを渡せば、操作する事が可能です。, JDBCサービスからのアクセスは以下のIP範囲からのものを、相手方DBサーバで許可（ホワイトリスト）してあげる必要があります。, また、MySQLからフォークして作成されているMariaDBやCloud SQLにあるPostgreSQLについては、JDBCサービスではサポートされていません。stackoverflowでは、Google Apps ScriptからPostgreSQLに関しては、REST APIアクセス化する為のPostgRESTというものがGithubにて公開されています。GASからはUrlfetchAppでアクセスする方法を使うようです。しかし、UrlfetchAppは結構使用制限の厳しいものなので、素直にMySQLのレンタルを選んだほうが幸せになれると思います。, 其の場合、GASからのアクセスはねこの足跡Rさんのページにて、IP範囲についてのエントリーが紹介されています。, MariaDBについては、MySQLからフォークされているものの、お互い最新版では互換性が薄れています。其のため、Google Apps ScriptからMySQLとして「MariaDB」を使う場合には、MariaDBのバージョンは5.5である必要があります。これは、MySQL WorkbenchでMariaDBに接続する場合も同じです。, […] ツールを作る事に特化しています。GCPのサービスなので有償です。GASでもGoogle Cloud SQLをバックエンドにしたアプリは作ることが可能です。個人的にはスプレッドシートをバックエンドに […], このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。. Googleスプレッドシートを用いてデータベース化するということにはどのようなメリットがあるのでしょうか？ 以下にメリットを記載しています。 すべてに共通していることは、いかに効率よくデータを扱えるようにするのかという部分です。 GoogleスプレッドシートをGAS（GooglAppsScript)を使って Webアプリを作成しデータベース代わりに使えるよう考えてみた。 インターフェースはHTMLベースだが、これもGASにて提供されているので、 自分でサーバを用意する必要はない。 .SetRequestHeader "Content-Type", "application/json; Private Function checkAccessToken() As Boolean, Public Function EncodeURL(ByVal str As String) As String, Dim s As String, com As String, ret As String, Dim ScriptFilePath As String, ExeFilePath As String, With .CreateTextFile(ScriptFilePath, True), ExeFilePath = .GetSpecialFolder(0).Path & ", ret = ExecuteGASFunction(access_token, script_id, function_name, param), arrlength = UBound(tempArray) - LBound(tempArray) + 1 - 1, tempday = Mid(tempArray(Eval(i + 1)), 1, 4) & ", " & Mid(tempArray(Eval(i + 1)), 6, 2) & ", 'function':'" & function_name & "','parameters':'" & parameters & "','devMode':" & dev_mode & "}", "https://script.googleapis.com/v1/scripts/", VBA 64ビット環境でCreateObject(“ScriptControl”)がエラーになる, Max size for POST request sent to webapps, Google Apps ScriptでBox Webhookを受取りkintoneへ流してみた, ダイアログが出てくるので、新規プロジェクトを作るか？既存のプロジェクトを選択する。この時、G Suiteであれば選択元は「, この時、元の自動作成されたプロジェクトはシャットダウンされて消えます。これで設定完了です。, データを取得して返すケースは非常に単純にJSON.stringifyしてデータを返すのみです。, 書き込み用配列は２次元配列に組み上げて、JSON文字列から一つずつ値を取得し、pushしています。, Authgoogle()がメインのaccess_tokenを取得する為のコードです, testTokenInfo()が取得済みAccess Tokenがexpireしていないかどうかのチェックと、expireしてる場合のrefresh_tokenを使って、新しいaccess_tokenを取得するコードです。, データベース起動時にAutoexecマクロを使って、expireチェックをし、refreshしておくとスムーズにデータを取得したり、データを送信するルーチンに繋げられると思います。, 2019年1月1日より、ブラウザ上で表示されるAuthrization Codeの取得の為の仕様が変更になっているため、旧来の, temp_gasとmasterテーブルの不一致分をmasterテーブルへと追加するクエリを実行, refresh_tokenで新しいtokenを取得した場合、再認証は必要ありません。, また、expireしてるかどうかのチェックの為にtokeninfoに投げて結果を受け取るコードも必要です。, オリジナルのコードの中ではAPI IDを指定していますが、現在はスクリプトIDでなければ利用できません。, access_tokenおよびrefresh_tokenは、settingテーブルの1行目に値を格納させています。, expireしてなくても、残り時間が60秒以下の場合には、refreshを実行するようにしてあります。, 取得したデータはカンマ区切りになってるので、splitで配列化、その後日付だけは型を整えてDAOでレコードを追加しています。, temp_gasに入ったレコードを差分追加クエリの実行にて、masterテーブルへと追加しています。, 今回はAccess側にフォームを設けていません。実際にはフォームとボタン類を配置して、各Subルーチンを割り当てて利用しましょう。, 出来れば、Access_Tokenをrevokeし、別のアカウントで再認証出来るように仕組みを追加すると尚良いでしょう。その際のrevoke用のエンドポイントは、, json文字列を組み立てる部分でやたらとダブルコーテーションが記述されていますが、これは文字としてダブルコーテーションでプロパティ名やその値を括らなければならないので、このような書き方をしています。エスケープしないと、文字としてのダブルコーテーションが認識されないので、ちょっと面倒ですね。, Cloud SQLを使わずスプレッドシートで十分なレベルのデータ量ならば、VPN無し・DBなしで様々な業務アプリがAccessでも構築出来ます（バックエンド処理を全部、GASに任せられますし、DB接続する場合も, Google Apps Script APIを使えば、レガシー資産をクラウド対応させたり、CordovaのようなHTML5のスマートフォンアプリに強力な機能を持たせる事が出来るので、VBAでも使えるのはありがたい。, 複数のGoogleスプレッドシートを扱う場合は、１個だけ今回の処理を記述したGoogleスプレッドシートを用意し、複数のスプレッドシートはリスト化しておけば、この１個からそれらを読み書き可能です。よって、複数のシートに今回の処理を記述する必要はありません。.

.

動物 体操 123 6, 知的障害 発達障害 違い 5, ジムニー 室内 収納 5, Steam Gwセール 2020 4, キバナ ポケモン 声優 15, 卓球場 作る 費用 5, 開封厳禁 封筒 書き方 18, 草刈機 レンタル カーマ 11, 日本ドラマ 無料動画 まとめ 17, パソコン 有線 Wi Fi 9, Mrワゴン ガソリン 点滅 15, Kv Studio ライセンス 6, 村上 祐子 立教大学 4, 北嶺 青雲寮コース 偏差値 33, 外壁塗装 アトモス 費用 4, Fox リアサス エア漏れ 17, Webex 仮想背景 Windows 27, Tiger View In 3d Google Ar 6, 栄冠ナイン 世界 大会 20, Dynabook セーフモード F8 4, スタジオアリス 袴レンタル 口コミ 6, Pso2 武器 比較 32,