c.getElementById(a)||(d=c.createElement(f),d.src=g, (function(b,c,f,g,a,d,e){b.MoshimoAffiliateObject=a; Google Apps Scriptを使うようになってから、クライアントサイドでAccessを使う機会は大分減りましたが、それでもAccess需要がゼロになるという事はありません。まだまだクラウドのデータベースサービスは高価であり、おいそれとクエリを投げようものなら翌月の請求書金額は怖いものになります。また、複雑な計算やクエリを組んでとなると、Accessのほうがまだまだ作りやすく、何よりもレポート機能が強力で価格が安いので、手放せません。, そんなAccessですが、Googleスプレッドシートと連携出来たらより一層利便性が向上し、既存のGoogle Apps Scriptのプログラムと連携出来たらいいなと思い、色々調査してみました。Google Apps Script APIが使えそうなので、これを使ってみて実装をしてみました。. (window,document,"script","//dn.msmstatic.com/site/cardlink/bundle.js","msmaflink"); d.id=a,e=c.getElementsByTagName("body")[0],e.appendChild(d))}) >>>データベースのサンプルを見てみる, 通常のエクセル関数とは違い、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で緯度経度に変換して地図プロット. msmaflink({"n":"詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド","b":"","t":"","d":"https:\/\/m.media-amazon.com","c_p":"","p":["\/images\/I\/51GD7yZsLVL.jpg"],"u":{"u":"https:\/\/www.amazon.co.jp\/dp\/B01LCJRCYE","t":"amazon","r_v":""},"aid":{"amazon":"1879761","rakuten":"678083","yahoo":"1879762"},"eid":"qrNSV","s":"s"}); データベースはスプレッドシートと違って、課金されるサービスなのですが近年は単価も下がってきており、また何よりも大きなメリットがあるため、ビジネスでは普遍的に利用されています。主な利点は, 気になるGoogle Cloud SQLの利用料金ですが、わかりにくいです。今回の設定は, この場合の料金になります。３つの課金がなされるのですが、起動におよそ15分は消費するので注意。, ネットワーク部分が要するにクエリをぶん投げてデータを拾ってくる場合の料金ですが、10GBも拾っても200円くらいなので、よほどオカシナ使い方しない限りは気にする必要無し。一番ボリュームが大きいのはインスタンスの部分。ここはおいそれと止められないので、トータルで最低でも「3,914円/月以上」は掛かることになります。MySQL第一世代のほうがパッケージプランがあって安そうだ。, インスタンスが本番使用での標準の「db-n1-standard-1」ならば、$0.0878なので換算値で7,054円は標準的に掛かるという事。安くなったとはいえ、まだまだ高いですねぇ。SakuraでVPSでMySQL借りて運用したほうが安いなぁ。まぁ、セキュリティやバックアップ、MySQL構築の手間とか考えたらってのはあるんですけれどね。Nifty Cloudもいいかもしれない。, ※とは言え、G Suite上で作ってユーザに入力だけしてもらうならば、こちらは固定費で済むので、利用者数が多いなら、G Suiteアカウントではなく、専用アプリ用意して使わせたほうが、トータルコストは安く済む計算（G Suite Basic 12名分の料金で済むので、ソレ以上の利用者がいるならば）, ※Azureに以前あったClearDBというMySQLサービスは削除されてしまい、現在はcleardb.netというサイトに分離独立してしまっています。今まで使っていた人は、支払い情報やアカウントについては、こちらのサイトのように自分で手続しなければならないのです。また、Azureには2018年10月にMariaDBがプレビューながら追加されているので、これに置き換えたようですね。, 今回の発表直前の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プロジェクト）上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。, 今回のこの変更だと１つ作ったプロジェクトに集約する必要があるので、クォータについてプロジェクト毎のカウントだったので問題なかったものが、集約されることで、クォータに引っ掛かる可能性があります。, Google Apps Scriptから使うためには、Google Cloud Platformでいろいろ作業を行わなければなりません。以下の手順でAPIを有効にしましょう。, APIを有効化したら、しばらく待つとデータベースのインスタンスというものが作れるようになります。「≡」というアイコンが左上にあるので、これをクリックして、左サイドバーを開きます。, さて、これでインスタンスも立ち上がり、MySQLは稼働しているのですが、まだやることがあります。データベースの作成と、ローカルからMySQL WorkBenchで接続出来るようにIPの許可を追加します。, Google Cloud Consoleには、Cloud Shellという標準でターミナルが用意されています。画面右上にある左端にあるアイコンがそれ。クリックすると、画面下部にターミナルが起動します。, 今回は、rurikosanというインスタンスに接続して、rootアカウントで入っています。Enterで実行するとしばらく接続まで待ちます。Passwordを聞いてきたら、入力。すると、MySQLへログインが完了し、以降は、コマンドラインでMySQLを操作可能になります。quitコマンドで終了です。, ※あらかじめ、新しいネットワークで自分のIPアドレスを登録しておかないと接続できませんので注意。, 取得した情報を元にMySQL Workbenchにて作業をします。コンソールが使える人はそちらで作業をしても良いでしょう。MySQL WorkbenchはGUIでテーブルの作成や設計、データの入力等が可能な非常に良いツールで、Windows/Mac/Linuxで使える優れものです。ポートフォワーディングなどを利用すれば、レンタルサーバのMySQLも操作が可能です。, ※MySQL Workbenchは無償のツールですが、OracleのIDが必要です。, JDBCサービスの制限はダッシュボードから確認できますが、以下のような感じになっています。以下の制限に引っかからないように注意しながら、運用する必要性があります。それほど大規模な人数でなければ、制限に引っかかることはないとは思いますが。, Google Apps Scriptでは、JDBCでコネクションを張るときに接続文字列、ユーザID、パスワード、接続先DB名が必要になります。自分が立てたMySQLサーバや他のクラウドデータベースの場合と違い、Google Cloud SQLのMySQL接続時には、概要タブで取得した「インスタンス接続名」を利用します。. さて、次はいよいよ、フォームに入力した内容が複数のスプレッドシートに登録されるようなスクリプトをかいていきます！ 次回の記事「Google Apps Script」で社内システムをつくってみた～GASとの出会 … 社内で様々なアプリケーションのデータ記録場所として、Googleスプレッドシートを使っているわけなのですが、スプレッドシートには最大500万セルという制限があり(最近、200万セルから拡張されました)、またLockServiceで排他制御があるとは言え簡易的なものでしかありません。また、Google SpreadsheetとCloud Platformではサポートが異なるので、より安定してるCloud SQLは魅力的。Google Apps Scriptでのアプリ作成の幅がグンっと広がります。Google App Engineというものもあるのですが、GASではなく主にJavaで作成するものなので、あくまでGASでこれまで通り出来るという点が重要です。, 今回は、用意したDBサーバや最近流行りのDBaaSサービスで数分で用意できるクラウドデータベースなどを保存先として、クライアントはこれまでのGoogle Apps Scriptを使うというやり方です。主にAmazon AWSやGoogle Cloud SQLなどがそれらに該当します。GASからなので、素直にGoogle Cloud SQLのMySQL Serverを選択しました。. )", "https://officeforest.org/wp/library/ProgressSpinner.gif", "ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;PORT=3306;", '127.0.0.1:3306に対して、MySQL ODBCでリンクテーブル接続（DSN無しで実現）, Visual Studio 2013 の Visual C++ 再頒布可能パッケージ, GAS+GoogleCloudSQL+GoogleChartToolsで簡易BIツールを作ってみる, Google Spreadsheet JDBCを活用したDBとの連携 [Google Apps Script], GoogleAppsScriptでSQLっぽくDBを扱えるライブラリを作りました。, クラウド データベース比較解説 (Amazon RDS・Azure SQL Database・Google Cloud SQL), Google Apps Script JDBC Storing Specific Date Value from Google Sheets into Google Cloud SQL, 【GCP】CloudSQLで作ったインスタンスに、ローカルからアクセスしたい【412日目】, MySQL の ODBC ドライバー 5.3.7 をインストールするとエラーになる, electronでAzure AD認証を行い、Graph APIを叩く – 実装編, electronでAzure AD認証を行い、Graph APIを叩く – 準備編, リレーショナルデータベースなので、リレーションシップを利用したデータの整合性を常に取る事が可能です。, 接続元制限や接続メンバーの制限など細かな設定が可能です（Azure ClearDBでは出来ません）, スプレッドシートのようなセル数での制限はなく、データベース容量での制限があります（後者のほうが遥かに広いです）。, ベンダーロックインじゃないですが、Googleサービスに完全依存しない上でも利点があります。, もちろん、Accessから直接ODBC接続でつなげて運用が出来るので、AccessをクライアントにするのもGoodですね。, 概要には紐付けられている課金プロジェクトが出てきます。初期ボーナスの30,000円分の残りクレジットなどはここで確認できます。, ダイアログが出てくるので、新規プロジェクトを作るか？既存のプロジェクトを選択する。この時、G Suiteであれば選択元は「, この時、元の自動作成されたプロジェクトはシャットダウンされて消えます。これで設定完了です。, ここでは、マシンタイプを選ぶのですが、選ぶマシンタイプで料金が全然変わってきます。ハイスペックほど高い料金が掛かります。通常は、「, 新しい接続をつくり、取得したIPアドレス、ユーザID、パスワードでログインします。, AccessのようにGUIでテーブル設計が可能ですがフル英語ですので気合をいれましょう。今回は5個のカラムでIDのみ, サイドバーから初期設定をするようになっているので、利用する場合にはメニューの「作業用」⇒「DBの設定」を開くと実行されるので、セットしてから利用しましょう。, 接続する部分で変数connにデータベース接続用URLを構築し、stmt.executeQueryにてクエリ発行をしています。これでデータの塊が取得できたので、配列に１つずつ収めて、スプレッドシートに追記するといった形を取っています。, Insert Intoにてレコードを一個追加しています。スプレッドシートに記述しても追加はされたりしません。, HTMLサービスのダイアログの値を取得して、query文のInsert intoに続けて値をはめ込むコードです。ハマりどころは, setStringで１個目〜４個目のパラメータにはめ込んでゆきます。そして、executeでインサート実行です。insert intoは, stmt.setString(1,”職員”);、stmt.setInt(2,1)が肝です。SQL文の1個目の「?」に「職員」をString型でセットするという意味。これで、ID=7がSQL文にセットされるわけです。つづけて、SQL文の2個目の「?」に1をInt型でセットし、これで、IDが1のものに対して、JOBを職員にするという意味になります。, stmt.setInt(1,7);が肝です。SQL文の1個目の「?」に7をInt型でセットするという意味。これで、ID=7がSQL文にセットされるわけです。複数ある場合には、複数セットする。7を変数に置き換えれば動的に変更が出来ますね。, 他にもテーブルの作成や削除などといったものもGoogle Apps ScriptのJDBC Serviceには用意されているので、使いやすいようにラッピングした関数などを用意しておくと良いでしょう。, 接続先にはGoogle Cloud SQLのIPアドレスとID、パスワードを指定。, 途中のObject Mappingに於いて、Migration of Type schemeは「, インポートが完了したら、データの転送が開始されるのだが、ここはなぜか失敗していた。, ただし、一部の列の型がおかしな型になっていて、JANコードなどの大きな数値が入らなかったので、手動で型変換をしてあげました。こればかりは、避けられないので、データ転送が失敗するのはこれが原因です。, テーブルのVarchar型のフィールドも「スウェーデン語」の文字コードになっているので、こちらもUTF8に変換してあげましょう。。。。, 接続画面が出るので、Google Cloud SQLのIPアドレスとIDおよびパスワードを入力。, 接続に成功すると、テーブル一覧が出てくるので、選べばリンクテーブルとしてAccessに登録されます。, 秘密鍵がダウンロードされます。これを適当なフォルダに保存します。大切なものなので、流出しないように！！, 127.0.0.7:3306にODBCで接続が可能になります。もちろん、MySQL Workbenchもこのlocalhostへ接続が可能になります。, Accessなどからこの処理を自動化する場合には、accdbのカレントディレクトリにcloud_sql_proxy.exeおよび秘密鍵のJSONを配置し、, データベース接続用のIDおよびPASSワードを入力させるフォームが必要（現在はVBAに直書きであるため）, 2.を自動でやるならば、VBAからPowershell経由で資格情報マネージャにPasswordを読み書きさせるコードを記述する必要があります（VBAから直接、資格情報マネージャは触れない為）, DBaaSサービスは時間や転送量などに応じての従量制課金サービスなので、SQLなどを発行する場合には、その時必要なレコードだけに絞って取得するようにしないと、どんどん課金されるので注意。, その為、Googleスプレッドシートなどを土台にしたアプリケーションを作ってる感覚で作ってはいけません。先にDBに投げるに当って必要な情報をすべて用意し、必要な情報に絞った状態でデータの取得をするように心がけましょう。, GROUP BY句やHAVING句、JOIN句, ORDER BY句、DISTINCT句、サブクエリ等覚えることはたくさんありますが、AccessやスプレッドシートのQUERY関数などで予行演習をしておくと良いでしょう。. 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 を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。.